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.

Direct Access to Diagrams

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.

vbi_xxxxx

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.

Non vbi tables

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

PowerBI Diagrams

Here are some relationship diagrams that may help

Click here to expand...Stock Diagram

Click here to expand... entities and relationships

Click here to expand... core tables

Click here to expand... Orders - sales, purchase, repair, etc

Click here to expand...Journals

Journals

vbi_article - this is all stock items and is the Product table

COA_ID - chart of accounts GL account - will only have a value if not using the default values
MANUFACTURER_ID - link to business_entity to get supplier details
ITEMFORSALE_ID - link to item_for_sale (1:1 and service also links to item_for_sale)
SEARCH-CODE = SKU
ARTICLE_CATEGORY_ID link to table article_category
STATUS - what is 10 / 20 / 99 ? internal numbers; there should be a StatusText column that is decoded
brandid - link to table item_brand
GSTModeText - are values always stored ex GST and this is just whether to add GST or not?
Price_list (filtered by host company) is Average Cost table

vbi_itemforsale = Abstraction layer for goods (articles) vs services

vbi_salesorder

vbi_journal

is the table that determines the nature of the transaction from the JournalTypeText field with journal_id being the related key field

JOURNAL_TYPE = Debtor invoice, CN, COGS etc
JOURNALBATCH_ID = link to journal_batch table
ORDER_ID = optional related order (Sales, Purchase, RA etc)

vbi_journal_line

Please explain this table relative to vbi_jourmal and vbi_journal_lines
COA_ID
debit / credit - only 1 of the 2 be populated per line
quantity = decorative, used for printing, not part of any calculation
jd_id = unique id for journal lines
status - 1,2 or 5 - what do these mean = Status of the line

vbi_journal_lines

Order_ID = optional related order (Sales, Purchase, RA etc)
control_type
quantity = decorative, used for printing, not part of any calculation, often the number of items if it was a sale/purchase

vbi_purchaseorder

why a purchase table but no sales equivalent?
Status - what do the codes mean ?
supplier_id = different to manufacturer_id - link to business_entity to get supplier details
ORDER_ID =
SEARCH_CODE = purchase order search code
CONTACT = contact name on PO
log_surrogate_id ?

vbi_purchaseorderlines

LineStatus ? meaning of each number
XVALUE ?
PLAN_QUANTITY = quantity on the line sent to the supplier

Clinics and Healthpoint Transactions

Clinic bookings and payments

Related articles

Page:

Reporting basics using PowerBI or Excel
Page:

Price Lists - Printing
Page:

Go Live Checklist
Page:

Creating and Managing School Runs

Browser not supported