Versions Compared

Version Old Version 12 New Version Current
Changes made by

Paul Sawkins

Paul Sawkins

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Table of Contents

...

Once you have connected PowerBI to the reporting tables - these are the tables to consider.To view the relationships install the standard SQL server management studio and connect to the reporting server. 

Note
titleDirect 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)

...

Expand
titleClick here to expand...Stock Diagram

Image RemovedImage Added


Expand
titleClick here to expand... entities and relationships

Image RemovedImage Added


Expand
titleClick here to expand... core tables

Image RemovedImage Added


Expand
titleClick here to expand... Orders - sales, purchase, repair, etc

Image RemovedImage Added


Expand
titleClick here to expand...Journals

Gliffy
nameJournal lines linked to stock transactions
pagePin1

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

...