Data Warehouse Indexes
A datawarehouse environment is used differently than a transactional environment, so architecture and indexing strategies should also be different. In this presentation, we review what the ultimate goal of an index really is, what the point of a data warehouse is, and how they relate to each other. You will also learn common practices for indexing the different types of warehouse tables, specifically dimension tables, fact tables, and staging tables. We will review cases when it is useful to have summary level fact tables, in addition to detail level fact tables. Then, lastly, we will look at the benefits, and gotchas, of using columnstore indexes.
You can get to the presentation slide-deck by clicking here. There are helpful notes on most of the slides.
The script referenced in the slide deck can be downloaded by clicking here.
Also, there have been a number of requests to be able to get the cute frog pics, so here they are!
This session is also available via a recording, which can be found by clicking here. It was presented to the Data Architecture Virtual PASS chapter (although it has been updated since then, the majority of the presentation is the same).
ETL - Index Maintenance - specific table
First off, this script is still a work in progress. It is something that I created while working with data warehouse indexes, and added to it when I was getting ready for my presentation. It helps me specifically in the ETL process - I do NOT use this for overall index maintenance.
What is nice about this script is that I am able to either disable or rebuild by using the same stored procedure. That way, when I want to update the process, I only have 1 place to change it and all SSIS packages that use it will be affected.
The process I am using it in is as follows:
- Disable Clustered Index (this also disables all non clustered indexes and saves me from having to specifically do them manually)
- Rebuild the Clustered Index
- Data Manipulation (insert, edit, delete, etc)
- Rebuild all of the NonClustered Indexes
I am working on a full indexing strategy for the entire ETL process, but for now, I have specific needs and this takes care of all the problem children (tables) allowing the ETL process to continue largely unaffected, but these specific uses run much faster than without it.
By accessing or using this code, you agree to be bound by the following: license