다음을 통해 공유

Indexes in the BAM star-schema database

  • 아티클

Some customers have asked why some tables in the star-schema database have indexes and others don't.

As suggested by SQL Books Online “Creating and Using Data Warehouses”, all the dimension tables have the index automatically created on the primary key column “ <dimension name>_ID”. (BTW these implicit indexes don’t show up in the Query Analyzer, but you can see them in the Enterprise Manager.) In addition, for the hierarchical dimensions (data dimensions and time dimensions) composite indexes are created on all dimension levels.

One place that doesn’t have index is the fact table. Theoretically, the fact table should be indexed on the composite primary key made up of the foreign keys of the dimension tables. A few special considerations were taken into account before it was decided not to create index on the BAM fact table:

1. Index occurs overhead on data insert (as well as delete & update)

2. Unlike the BAM primary import table which needs to support heavy instance query and real-time aggregation queries, fact table is only queried during the execution of the star-schema transformation stored proc.  

3. Unlike dimension table, the entire fact table is truncated before next DTS run. The table is expected to stay relatively small (of course, table size depends on the incoming data volume, the scheduling of cubing DTS and archiving DTS). Indexing small tables may not be optimal because it can take SQL Server longer to traverse the index searching for data than to perform a simple table scan.

4. The star-schema is dynamically created. Customers may know best what additional index they may need on the fact table based on their business data characteristics and DTS scheduling etc.

Comments