Business Central ERP System

Business Central is an ERP System - the main objective of enterprise resource planning, or ERP, is to integrate all departments and functions across a company into a single system by using a common database, so to have only one correct set of data.   

Microsoft Dynamics Business Central is a fully integrated relational database system that lets you enter and maintain financial and other business activities in one place. This includes the following:

• General ledger
• Inventory
• Sales and receivables
• Purchases and payables
• Resources
• Manufacturing
• Jobs
• Service management
• Human resources

Because Microsoft Dynamics Business Central is a database system, be aware that entered data is written (committed) directly to the database. Therefore, no Save action is required. Any entered and unposted data can be edited or deleted from the database. Posted data cannot be deleted as they are considered binding financial transactions.  

Relational Databases Systems and Normalisation

Database normalization is a process used to organize a database into tables and columns.  The main idea with this is that a table should be about a specific topic and only supporting topics included.

Therefore, each Company in Business Central has its own set of NAV SQL tables e.g. CronusUK$Customer and CronusEU$Customer, and a lot of the customer attributes such as Payment Terms, Currency, Posting Groups, Country/Region come from different SQL tables and some attributes such as Customer Default Dimensions are stored in another table.

Data Modelling  

Relational data source for ‘analysis’ is generally in the form of a ‘star schema’, containing Fact & Dimension tables (suitable for populating OLAP cubes).

In Power BI, typically performed in Power Query (M Language) and may involve merging and appending multiple tables into individual dimension tables and single fact table.

Key Business Central Processes & Tables

This is not an exhaustive list of processes and tables, just the most commonly used in general.

Posting Routines

Posting represents the accounting action of recording business transactions in the various company ledgers.  Business Central has two posting procedures:

• Journal: the data is added in a journal line and the relevant ledger entries are created via the journal posting
• Document: the data is used in a document and the relevant ledger entries are created via the document posting routine

At a document level, information is recorded in document Header and Line tables, as well as register and entry tables.

At a journal level, information is recorded in register and entry tables only.

Sales & Purchase Process Flows

Key Ledger (FACT) Tables

• G/L Entry
• G/L Budget Entry
• VAT Entry
• Value Entry
• Cust. Ledger Entry
• Vendor Ledger Entry
• Item Ledger Entry
• Detailed Cust. Ledg. Entry
• Detailed Vendor Ledg. Entry

Key Line (FACT) Tables

• Sales Line
• Sales Invoice Line
• Sales Credit Memo Line
• Sales Shipment Line
• Purchase Line
• Purchase Invoice Line
• Purchase Credit Memo
• Purchase Receipt Line

Note: each line table has a header table – often in Power Query these tables will be merged to one FACT table (and the Header table may be used to create dimension tables as well).

Key Master Data (DIMENSION) tables

• Customer
• Dimension Value (used for all NAV dimensions)
• Dimension Set Entry (used for NAV Shortcut dimensions 3-8 – only global dims on entry and line tables)
• Item
• Item Category
• G/L Account
• Vendor

Things to look out for  

• Bill-to Customer vs. Sell-to Customer
• Buy-from Vendor vs. Pay-to Vendor
• Posting Date, Document Date, Closed at Date, Order Date.
• Create a "Datasource" column for company if appending tables from multiple companies.
• Create a "Document Type" column if appending invoice and credit memo tables into a single FACT table
• Remember to reverse the sign on Credit Memo lines (i.e. qty and amount)
• Option fields (0,1,2,3,4 etc. you can't find these in SQL - create a mapping table using Enter Data in Power BI)
• Use Dimension Set Entry for multiple dimension reporting in Business Central i.e. more than Global Dimensions