Automating the Tasks
After the cube is processed, you can examine the results of your work (finally!) in the Analysis Manager. Now the manual labor is over, and it's time to automate the tasks.
You'll need a DTS package that consists of several tasks:
- Drop indexes on the fact table and dimension tables, if appropriate.
- Refresh dimensions with the new dimension members (or new member values).
- Populate the fact table with new data (data that isn't already in the fact table).
- Rebuild indexes on the fact table (and dimension tables, if appropriate).
- Process cube dimensions.
- Process the cube itself.
Not too hard, is it? Well, not at the first glance, anyway. But it does take some patience and diligence to get all of these steps right. In addition, you'll want to put your entire cube population and processing routine in a transaction because you don't want one step to fail and the rest of them keep going (you'll mess up your existing cube). I'll discuss transactions in another article.
In this article, I introduced you to the tasks of populating the fact and dimension tables, creating and maintaining appropriate indexes to optimize your cube processing performance, designing storage for your cubes, and building aggregations. As I mentioned in the beginning, there are many more complex topics to be discussed with data warehousing. I'll try to cover these in upcoming articles.