Table of Contents
Overview
Every night we refresh the data into a complete copy of the live database. So reporting via powerbi has the latest information as yesterdays information.
Once you have connected PowerBI to the reporting tables - these are the tables to consider.
There are diagrams in the database to help understand the relationships - use the Microsoft free tool SSMS. https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms To view the relationships install the standard SQL server management studio and connect to the reporting server. |
Tables that begin with vbi_ have been created specifically for reporting. This means that the information in them has been converted to readable form. So if a supplier on a SKU is number 123 in the system - the vbi_ table will show the supplier name (rather than what is stored in the table - the number 123)
vbi_ tables do have relationships - use SQL server management studio (described above) to view the relationships.
Genie Enumerations > central table for all lookups in the system
Stock Transaction > Order Headers (for lots of different types of orders)
Stock Trans Item > Order lines (for lots of different types of orders)
Journal Detail > All Journal Lines
Here are some relationship diagrams that may help
Journalsvbi_article - this is all stock items and is the Product table
vbi_itemforsale = Abstraction layer for goods (articles) vs servicesvbi_salesordervbi_journalis the table that determines the nature of the transaction from the JournalTypeText field with journal_id being the related key field
vbi_journal_line
vbi_journal_lines
vbi_purchaseorder
vbi_purchaseorderlines
|
Clinic bookings and payments |
Related articles appear here based on the labels you select. Click to edit the macro and add or change labels.
|