Friday, March 27, 2015

SSAS Database/Cube Processing Best Practices.

Small Databases
If the SSAS database is relatively small & you have a big enough load window do a full process on the database.

Large Databases
If the SSAS database is big & needs to be updated regularly (daily) & load window is not enough to do full process.
  1. Do process update on all the dimensions. 
  2. Do Process Data on the small measure groups.
  3. For big measure groups - Do a process Data on latest partition. ( Current Year or Month).
  4. Finally do process index on the cube.  Process Index rebuilds the aggregations and bitmap indexes on partitions.  Note:   If dimension members are changed/deleted, Process Update (step 1) can drop aggregations & bitmap indexes of effected partitions , the Process Index will rebuild them.
  5. Once a week or whenever you have a big enough load window do a process full on the database. This will make cube & tables in synch.

All the above can be easily accomplished by SSIS Process Cube task or XMLA scripts executed by SSIS.




No comments: