Indexing Data Warehouse Tables

Too many or too few indexes can hurt performance. Non-active indexes may disturb SQL server in generating the optimal execution plan. Managing indexes can become an 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, in this case indexes are created according to the built-in logic that understands the whole data warehouse model. This is what is called data warehouse automations.

Timestamp

Almost all data warehouse tables have Clustered Index on column timestamp which is used for incremental processing. The timestamp column is created by BI4Dynamics based on different ERP logic of Business Central (BC), D365 FO, Dynamics AX and Dynamics Navision (NAV) data. The timestamp column type is a BigInt and not a timestamp. The type of the timestamp can be used in Columnstore.

Indexes on staging tables

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

For example: All requests for indexing one table are gathered from different fact models. Those requests are analyzed first so that the only needed indexes are created. Concepts such as covering indexes, included columns, equality and inequality requests are embedded in an algorithm that creates indexes (this is Data Warehouse Automations at its best).

It is less likely that indexes are set wrong, missing, or redundant.

Indexes on fact tables

Fact tables indexing

It is less common that users access data warehouse directly. In such scenario, users’ queries can be unpredictable and would require attention while indexing fact tables. Setting a Cluster Columnstore index would probably be the best solution; 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

The temporary view is the first part of fact loading where staging tables are joined together. The second part of fact loading is where dimensions are connected to the view, and inserted to the fact table. According to our experience the view can be slow when joining tables that have many empty values. By putting a Clustered Columnstore Index on a view we speeded up inserting data into the fact by a factor of 50 to a 100. This setup is a BI4Dynamics feature in the fact model and is not used by default.