Sage, Power BI

Your Sage 50 Accounts database in SQLite for Power BI

Your Accounting Insights connector for Sage 50 Accounts creates a SQLite 3 database of your Sage data, ready for consumption by Power BI via 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 connector for Sage 50 Accounts creates the following table structure that you can read into Power BI via an ODBC connection. They are grouped (the black border around each group) to show which tables need to be combined for particular purposes.

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
NominalRecord 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.

Leave a Reply

Your email address will not be published. Required fields are marked *

Privacy Settings
We use cookies to enhance your experience while using our website. If you are using our Services via a browser you can restrict, block or remove cookies through your web browser settings. We also use content and scripts from third parties that may use tracking technologies. You can selectively provide your consent below to allow such third party embeds. For complete information about the cookies we use, data we collect and how we process them, please check our Privacy Policy
Youtube
Consent to display content from Youtube
Vimeo
Consent to display content from Vimeo
Google Maps
Consent to display content from Google
Spotify
Consent to display content from Spotify
Sound Cloud
Consent to display content from Sound
Cart Overview