Analytics Data Warehouse

dbo. schema

The dbo scheme contains two types of tables.

Control tables

The tables prefixed with LSInsight$ are used by the pipelines in the Analytics Azure Data Factory to control what data is loaded into the Analytics data warehouse. It was decided not to change the name of the control tales even if the name of the product has changed so theyare still prefixed with LSInsight$

LSInsight$Companies

During the Analytics setup process the companies from the companies file are stored in this table. This table is then used as the source for the DW.dCompany dimension table.

LSInsight$LSCentralColumnMetadata

This table is populated with LS Central table and column meta data by the PopulateQueryBase-GetMetadata pipeline.

LSInsight$PublisherAffixReg

This table should contain GUID and affix information for all Business Central and LS Central extensions that should be loaded to Analytics.

By default, the table contains information for Business Central applications, LS Central, and LS Central for Hotels applications.

This tables is also used to trim away the prefixes for columns when the staging tables are created.

LSInsight$QUERY BASE

This table is populated by the PopulateQueryBase pipeline using the meta data from LSInsight$LSCentralColumnMetadata to create the queries used to create the staging tables and query the LS Central source database.

LSInsight$SourceTablesMap

This table is used to map between the new shorter and prefixed table names to the older LS Central version table names, and to create the staging table name that is the same as the table name from older versions or the new table name without extension prefix. This is done so that we do not have to have multiple versions of each stored procedure used to load the dimensions and fact tables in the data warehouse. This table also includes information about whether each table should be included in this Analytics instance or not.

LSInsight$Audit

This table registers all pipeline runs in the ADF, which timestamps were moved, and whether the load was full or incremental.

LSInsight$Version

In this table you can find the version of Analytics that the database was created on.

Staging tables

The staging tables are also created in the dbo. schema. They do not exist in the data warehouse when the database is restored, but are created on the fly using the information from the LSInsight$ control tables on the initial load from LS Central to Analytics.

The staging tables are prefixed with stg$ and a staging table is created for each table name in LSInsight$SourceTables that is marked with Include table = TRUE.

Each staging table combines the Business Central (BC) base table with all extension tables with the same name, or, if the table only exists as an extension table, then that is the only data loaded to the staging table. For data from an extension table to be included in the staging table, the extension must exist in the LSInsight$PublisherAffixReg table.

To add new staging tables from LS Central to the data warehouse, you can use the Add or Delete Source Tables and Add or Delete App Affix pipelines in the Azure Data Factory. This is explained in more detail in the extension guidelines.

DW. schema

The tables in the DW. schema are organized in a star schema. This means that each fact table connects to several dimension tables through surrogate keys. Each fact and dimension table has a corresponding store procedure that is used to load the table from the staging tables. Each fact and dimensions table can be loaded with data from one or more staging tables.

Busmatrix

The busmatrix shows which dimensions connect to which fact tables:

	Dimensions
Facts	Time	Date	Item	Item Variant	Location	POS / Device	Member	Customer	Vendor	Staff	Offer
FactSalesPosted	Sec	Day
fDiscount	Sec	Day
fInventory		Day
Hotels	Hour	Day

Dimension tables

There are currently 23 dimension tables in the data warehouse. They are all prefixed with a 'd' and the stored procedures that load them are prefixed with dim, dimext, predim, or vXXdim if the loading is different depending on which LS Central version is being used.

Note: Since we regularly add dimension and fact tables to the data warehouse, we always recommend setting up the latest version of Analytics to check for dimension tables and which columns are supported.

Fact tables

There are currently 19 fact tables in the data warehouse, but not all of them are in active use.

The fact tables are prefixed with an f or Fact.

Note: Since we regularly add dimension and fact tables to the data warehouse, we always recommend setting up the latest version of Analytics to check for fact tables and which columns are supported.