Address
8-9 Marino Mart, Fairview, Dublin 3, D03 EK81, Ireland
Contact
[email protected]
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. |