The LS Insight data warehouse is an SQL database. During the LS Insight 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 LS Insight Azure Data Factory to control what data is loaded into the LS Insight data warehouse.
LSInsight$Companies
During the LS Insight 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 LS Insight.
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$ShortTableNameMap
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. 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.
LSInsight$SourceTables
This table contains the short table name for all tables that should be loaded from LS Central and whether they should be included in this LS Insight 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.
LSInsightVersion
In this table you can find the version of LS Insight 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 LS Insight.
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 LS Insight 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 LS Insight 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 LS Insight data warehouse, we always recommend setting up the latest version of LS Insight to check for dimension tables and which columns are supported.
Fact tables
There are currently 8 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 LS Insight data warehouse, we always recommend setting up the latest version of LS Insight to check for fact tables and which columns are supported.