Indexes in a Data Warehouse

Question

I'm creating a data mart in SQL Server 2008 using SSIS for load, and SSAS for an OLAP cube. So far, everything is working great. However, I haven't created any indexes on the source database other than the default clustering on primary key.

I'm pretty comfortable with designing indexes on the application databases, but since this database is intended primary to be the source for a cube, I'm not sure what sort of indexing, if any, will be beneficial.

Is there any sort of indexing I should be doing to improve the processing of the dimensions and cube? I'm using your regular Molap storage.

Answer 1

Generally, the best practice is to keep indexes and constraints off of marts, unless they'll be used directly for reporting. Indexes and constraints can seriously hose your ETL time (especially with the amounts of data that usually go into warehouses).

What I've found works best is to have a single, solitary PK on all of your tables (including fact, because I have composite keys, and I'll just hash the composite to get myself a PK if I have to). Having PK's (that are identity columns) provides you with an autogenerated index, quick joining when the cubes are built, and very quick inserts.

If you're going to be doing reporting, then build out the indexes as you would, but make sure to disable and then rebuild the indexes as part of your ETL process. Otherwise, bulk inserts take some time to do (hours upon hours to commit, in some cases).