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 NameDescriptionHow to use
AuditHeaderHeader information for the Sage 50 transactionsCombine with AuditSplit to create financial transactions by TranNumber (the [No] field in the Sage 50 Transactions module). Join with AuditSplit based on HeaderNumber.
AuditSplitLine items of Sage 50 financial transactionsCombine with AuditHeader to create financial transactions by TranNumber (the [No] field in the Sage 50 Transactions module). Join with AuditHeader based on HeaderNumber.
AuditUsageAllocations of payments and receiptsCombine with AuditSplit based on SplitNumber
InvoiceSales invoice header informationCombine with InvoiceItem based on InvoiceNumber to reproduce full line item and header record for each sales invoice.
InvoiceItemSales invoice line item detailsCombine with Invoice based on InvoiceNumber to reproduce full line item and header record for each sales invoice.
SalesOrderList of Sales OrdersCombine with SopItem to create a full list of Sales Orders with header information and line item detail
SopItemSales Order line itemsCombine with SalesOrder to create a full list of Sales Orders with header information and line item detail
PurchaseOrderList of Purchase OrdersCombine with PopItem to create a full list of Purchase Orders with header information and line item detail
PopItemPurchase Order line itemsCombine with PurchaseOrder to create a full list of Purchase Orders with header information and line item detail
StockTranList of stock transactionsUse to calculate historic cost prices for stock (for historic margin reports and trends) and historic stock levels.
GoodsDespatchedNoteList of Goods Despatched Notes from SageReporting goods despatched by StockCode, SalesOrderNumber, Customer and Date
GoodsNotesRecordList of Goods Received NotesReporting goods received by StockCode, PurchaseOrderNumber, Supplier and Date
Nominal RecordList of nominal records, their balances, budgets and movements by periodCombine with Company, ControlData, ChartList, CategoryTitle, Category to create a nominal hierarchy based on the default chart of accounts in Sage.
CategoryList of Nominal Record CategoriesCombine with Company, ControlData, ChartList, CategoryTitle and NominalRecord to create a nominal hierarchy based on the default chart of accounts in Sage
CompanyA single row table containing company informationCombine 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
CategoryTitleList 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.
ChartListList of Charts of Accounts defined in SageCombine with Company, ControlData, Category, CategoryTitle, and NominalRecord to create a nominal hierarchy based on the default chart of accounts in Sage.
ControlDataList of control accountsCombine with Company, Category, CategoryTitle, ChartList, and NominalRecord to create a nominal hierarchy based on the default chart of accounts in Sage.
StockList of stock items from Sage including quantitiesCombine with StockCategory and StockComp for full stock and assembly hierarchy. Links to InvoiceItem, SopItem, PopItem, StockTran, GoodsDespatchedNote, GoodsNoteRecord.
StockCategoryList of Stock CategoriesCombine with Stock and StockComp to create stock and assembly hierarchy
StockCompList of Stock componentsCombine with Stock and StockCategory to create stock and assembly hierarchy
SalesLedgerList of CustomersCombine with transactional data, Country, Currency to report by customer
PurchaseLedgerList of SuppliersCombine with transactional data, Country, Currency to report by supplier
CountryList of countries from SageCombine with customer/supplier address data for mapping or reporting by country. For filled maps add ISO-3 country code.
CurrencyList of currencies from SageCombine with transactional data to report by currency. For live rates use a 3rd party web service.
DepartmentDepartments list from SageFiltering/slicing by DepartmentName.

Newsletter Updates

Enter your email address below to subscribe to our newsletter