Data Warehouse indexing approach - to verify

Question

Could you take a look at my approach to the Data Warehouse indexing (SQL Server 2014 SP1 Ent) below and tell me is it correct or should I change it ? (our Data Warehouse most of the time is used for SSAS Cube and sometimes for direct reports)

My indexing approach:

• All primary key's (in a Dimension tables) have a clustered Index
• For bigger Dimension tables (50k+ of rows) I have a dedicated non-clustered index per frequently use column
• All Fact tables have a PK clustered index implemented (on single non FK column like FactTableKey) – but I red this shouldn’t be placed there
• All foreign key’s (in a Fact tables) have a dedicated non-clustered index (per one FK column in it)
• In addition I’m considering the implementation of a CLUSTERED COLUMNSTORE INDEX on a Fact tables instead of many non-clustered indexes for every FK’s - but in this case I’ve red that this is a not recommended solution

In addition – on my test lab with Visual Studio SSDT – I tried to put a CCI index on a Fact table which belongs to a FILEGROUP but I get an error with information that I cannot use CCI on a table which belongs to a FILEGROUP. What’s strange I can create such CCI from SSMS directly. Visual Stusio issue or SSMS bug ?

Answer 1

You mention that you added single-column indexes for each foreign key in your fact table. Often at least some of the foreign keys have low cardinality so they are likely not useful in an index on their own. https://www.ibm.com/developerworks/data/library/techarticle/dm-1309cardinal/

They may be more useful as part of a multi-column index which you can design based on the way that you expect users to query the table.

If your workload suits it, then non-clustered columnstore index should be considered on large dimension tables and fact tables. They are ideally suited for data warehouse workloads. http://www.nikoport.com/2016/03/07/columnstore-indexes-part-79-loading-data-into-non-updatable-nonclustered-columnstore/

Since you are using 2014 then non-clustered is the only option if you want to keep constraints and other indexes.