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.
Enjoy!
Excellent
ReplyDeleteImpecable
ReplyDeletenice
ReplyDeletenice
ReplyDeleteYes, but that means that dimensions and cubes are processed in two separate batches, and as soon as the dimensions commit they un-process the cube, so it cannot be read until its processing is completed. Currently the only solution I have found for this is to create one SSIS DDL script task and script all the process dimension tasks, and then the process cube inside a single batch in the script. That way you can read the cube while it is processing.
ReplyDeleteGlenn