Tuesday, August 18, 2009

How to process SSAS Cubes from SSIS

In case you do not know I LOVE SSAS. It makes sooo many things sooo easy (with great performance J) that it should be a sin for anyone to charge implementing it. But shhhhh don’t tell anyone.


Anyways you can refresh Cubes from SSIS using analysis services processing task. Its in the main SSIS panel.


One error that you may encounter is that it will fail with some weird error like “x cube failed because primary key y not found” or something. That is because the fact table will contain data not found in the dimension.


Here’s a tip about how SSAS works. Dimensions are processed separately (dimension processing) and fact tables are processed separately (cube -> fact table -> partition processing) with only liking to the dimensions.  The error occurs when data present in the fact tables against a dimension is not in the last processed dimension. To avoid this process the cubes AND dimensions. Dimensions are pretty cheap to process since they are only select distincts. Sure you might have data for say day x in Cell level, BSC level, Whatever other level but its still one day x. Get the drift?  


So to process the partition you need to process the Dimensions first. But since the “Analysis Services Processing Task” does not order your processing sequence as you would like (no feature to move steps up and down) you will need two Analsysis Services tasks. In sequence refresh dimensions in first and refresh cubes (fact tables / partitions) in second.