Indexing data warehouse tables

Too many or too few indexes hurt performance. Not active indexes may disturb SQL server in generating the optimal execution plan. Managing indexes can become art that only experienced professionals can make right.

Not accidentally, models in BI4Dynamics data warehouse are know to the same engine that generates the indexes, so indexes are created according to the build in logic that understands the whole data warehouse model. This is Datawarehouse Automations.

Timestamp

Almost all data warehouse tables have Clustered index on column Timestamp. This index is used for incremental processing. Timestamp column is created by BI4Dynamics based on different ERP logic of BC, DFO, AX and NAV data. Timestamp column type is bigint and not timestamp, as timestamp type cannot be used in Columnstore.

Indexes on staging tables

Staging tables are most indexed tables as they are used in modeling when creating fact tables. BI4Dynamics creates indexes on staging tables automatically based on request from data warehouse model.

For example: all requests for indexing one table are gathered from different fact models and analyzed first so that only really needed indexes are created. Concept such as covering indexes, included columns, equality and inequality request are embedded in algorithm that create indexes. This is Data Warehouse Automations at its best.

 

It is very less likely that indexes are set wrong, missing or that there are too many.

Indexes on fact tables

Indexing fact tables

It is less common that users access data warehouse directly. In such scenarios, user’s queries can be unpredictable and would require attention in indexing fact tables. Setting a Cluster Columnstore index would probably be the best solution. This is a standard BI4Dynamics feature, that is not enabled by default.

 

In most projects, users access Analysis services. Fact tables are usually less indexed, as they are consumed in Analysis services with predictable load requests and not queried directly from front-end tools.

Indexing temporary view

Temporary view is first part of loading fact where staging tables are joined together. The second part of loading fact is where dimensions are connected to the view and inserted to fact table.

According to our experience view can be slow when joining tables that have many empty values. By putting a Clustered Columnstore Index on a view we have speed up inserting data into the fact by factor 50 or 100. This setup is a BI4Dynamics feature in fact model and is not used by default.