How to create a robust, automated Sage 50 Accounts database in SQLite for Power BI

Why and how to create a Sage 50 Accounts database in SQLite for Power BI?

You can create a Sage 50 Accounts database in SQLite 3 for Power BI, using the Accounting Insights Sage 50 Power BI connector.  Power BI can then consume your Sage 50 Accounts data from this database, using an ODBC connection.  This all sounds very technical, but this database is key to enabling comprehensive and automated Power BI reports from your Sage 50 Accounts data.  The advantages of using this database are: 

• You can set up the Power BI service (via the Power BI Gateway from Microsoft) to use a scheduled refresh to keep your Power BI reports up to date.   
• It is comprehensive, with sufficient data to create a full P&L statement, balance sheet, sales or stock report for any particular day, drill-able down to each individual transaction line.  
• It is light and efficient, enabling fast refresh times for your Power BI report.  The Sage 50 Accounts database maintained by the Accounting Insights connector always contains the last two years’ data (future transactions, current month, last 24 months plus all outstanding transactions).  In other words, the connector strips out all of your old data that is no longer relevant to current year vs last year reporting.   

• It is live (or almost).  A special listener within the Accounting Insights connector detects new or amended transactions as they are entered into Sage 50 Accounts.  This means that within a couple of minutes, your SQLite Sage 50 Accounts database in sync with your data in Sage. This means that when you refresh your Power BI report, you have the confidence that you are looking at the very latest data from your Sage 50 Accounts system.
• The database is multi-company ready, with each table containing a unique reference key for the Sage 50 company.

List of tables in your SQLite Sage 50 Accounts database

The Accounting Insights Sage 50 Accounts connector creates the following table structure in your SQLite Sage 50 Accounts database that you can read into Power BI via an ODBC connection. 

Table Name	Description	How to use
AuditHeader	Header information for the Sage 50 transactions	Combine with AuditSplit to create financial transactions by TranNumber (the [No] field in the Sage 50 Transactions module). Join with AuditSplit based on HeaderNumber.
AuditSplit	Line items of Sage 50 financial transactions	Combine with AuditHeader to create financial transactions by TranNumber (the [No] field in the Sage 50 Transactions module). Join with AuditHeader based on HeaderNumber.
AuditUsage	Allocations of payments and receipts	Combine with AuditSplit based on SplitNumber
Invoice	Sales invoice header information	Combine with InvoiceItem based on InvoiceNumber to reproduce full line item and header record for each sales invoice.
InvoiceItem	Sales invoice line item details	Combine with Invoice based on InvoiceNumber to reproduce full line item and header record for each sales invoice.
SalesOrder	List of Sales Orders	Combine with SopItem to create a full list of Sales Orders with header information and line item detail
SopItem	Sales Order line items	Combine with SalesOrder to create a full list of Sales Orders with header information and line item detail
PurchaseOrder	List of Purchase Orders	Combine with PopItem to create a full list of Purchase Orders with header information and line item detail
PopItem	Purchase Order line items	Combine with PurchaseOrder to create a full list of Purchase Orders with header information and line item detail
StockTran	List of stock transactions	Use to calculate historic cost prices for stock (for historic margin reports and trends) and historic stock levels.
GoodsDespatchedNote	List of Goods Despatched Notes from Sage	Reporting goods despatched by StockCode, SalesOrderNumber, Customer and Date
GoodsNotesRecord	List of Goods Received Notes	Reporting goods received by StockCode, PurchaseOrderNumber, Supplier and Date
Nominal Record	List of nominal records, their balances, budgets and movements by period	Combine with Company, ControlData, ChartList, CategoryTitle, Category to create a nominal hierarchy based on the default chart of accounts in Sage.
Category	List of Nominal Record Categories	Combine with Company, ControlData, ChartList, CategoryTitle and NominalRecord to create a nominal hierarchy based on the default chart of accounts in Sage
Company	A single row table containing company information	Combine with Category, ControlData, ChartList, CategoryTitle and NominalRecord to create a nominal hierarchy based on the default chart of accounts in Sage. Also, use to calculate fiscal year information in a Date table
CategoryTitle	List of main account category groupings in a chart of accounts, such as “Sales”, ”Purchases”, “Current Assets” etc.	Combine with Company, ControlData, Category, ChartList, and NominalRecord to create a nominal hierarchy based on the default chart of accounts in Sage.
ChartList	List of Charts of Accounts defined in Sage	Combine with Company, ControlData, Category, CategoryTitle, and NominalRecord to create a nominal hierarchy based on the default chart of accounts in Sage.
ControlData	List of control accounts	Combine with Company, Category, CategoryTitle, ChartList, and NominalRecord to create a nominal hierarchy based on the default chart of accounts in Sage.
Stock	List of stock items from Sage including quantities	Combine with StockCategory and StockComp for full stock and assembly hierarchy. Links to InvoiceItem, SopItem, PopItem, StockTran, GoodsDespatchedNote, GoodsNoteRecord.
StockCategory	List of Stock Categories	Combine with Stock and StockComp to create stock and assembly hierarchy
StockComp	List of Stock components	Combine with Stock and StockCategory to create stock and assembly hierarchy
SalesLedger	List of Customers	Combine with transactional data, Country, Currency to report by customer
PurchaseLedger	List of Suppliers	Combine with transactional data, Country, Currency to report by supplier
Country	List of countries from Sage	Combine with customer/supplier address data for mapping or reporting by country. For filled maps add ISO-3 country code.
Currency	List of currencies from Sage	Combine with transactional data to report by currency. For live rates use a 3rd party web service.
Department	Departments list from Sage	Filtering/slicing by DepartmentName.