In this article
The Analytics for LS Central data warehouse is an SQL database. During the setup process a backup template of the database is restored and then the Azure Data Factory pipelines load the data into the warehouse.
When the database is restored it contains some tables, but others are created dynamically by the pipelines later in the process.
The tables are organized into schemes and prefixed in certain manner.
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$
Analytics$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.
Analytics$LSCentralColumnMetadata
This table is populated with LS Central table and column meta data by the PopulateQueryBase-GetMetadata pipeline.
Analytics$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.
Analytics$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.
Analytics$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.
AnalyticsAudit
This table registers all pipeline runs in the ADF, which timestamps were moved, and whether the load was full or incremental.
AnalyticsVersion
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 Analytics$SourceTablesMap 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 Analytics$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.
Dimension tables
There are currently 33 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.
All dimension tables have a combined primary key of Company and surrogate key that is unique to each line in the table. This surrogate key is then used in the fact tables to connect the fact to specific dimension values.
Fact tables
There are currently 27 fact tables in the data warehouse. The fact tables are prefixed with an f or Fact.
Each fact table is usually connected to several dimensions but not all. You can see which dimensions a facttable is connected to in the Analytics data catalog.
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.