I am looking into automating my SSAS cube builds using SSIS and/or scripts and have a few questions.
-
I have a cube with yearly partitions and a Date Dim that has attribute indicators by day for "last 30 days", "last 60 days", etc. Obviously as the current date changes, the indicators at the day level will change as well. I realize I will have to "process full" the Date Dim daily, but will I have to process a previous year partition whose date range falls into the "last 60 days"? And then keep on processing that partition until "day 61" of the current year?
-
I have a Postal Codes Dim with over 2 million entries that I intend to update only once a year. After I load the new postal codes and fully process the cube, can I remove the Postal Code Dim from the dimension update step once I start to process daily again (i.e., processing only the last partition)?
-
I have a cube with 4 measure groups, but yearly partitions on just one measure group as the 3 others do not have nearly the number of records. If I process the last yearly measure group in a script, do I then need to "process full" on the 3 other measure groups or on the 3 other single partitions in those associated measure groups? I plan to use the Analysis Services Processing Task in SSIS for that step, but not sure whether to include partitions or measure groups.
Best Answer
I would recommend you start by reading this excellent introduction to SSAS processing.
To answer your questions:
You should ProcessUpdate your Date dimension then ProcessIndexes your cube. ProcessUpdate will not unprocess (unload) your measure group partitions like ProcessFull which is important if you only want to process the latest year partition. But it is a best practice to ProcessIndexes at the end of the transaction to rebuild the flexible aggregations and indexes that were dropped when dimension rollups changed during ProcessUpdate.
Yes if the Postal Code dimension changes only yearly you can omit it from your daily processing scripts.
For the measure groups with just one partition, processing the partition has the same effect as processing the measure group. I would recommend a ProcessData on measure groups or partitions that need reprocessing then a ProcessIndexes on the cube.
By the way beware of the Analysis Services processing task in SSIS. When you set it up the database ID is hardcoded in the command it builds and editing the connection string of the SSAS connection manager to point at a different SSAS database name won't change which database it processes. So that SSIS task only works well if you are sure you will be processing the same database ID on each server and won't ever need to process a second SSAS database on the same server.