Saturday, 16 March 2013

SSAS process Programmatically

One thing I love about my work is that you can dive right into how things work and if you dont like the out of the box way of doing things you can check out how it works and manipulate it to do things your way.

One of the things I used this for recently was SQL Server Analysis Services. I wanted to be able to process a Cube via SQL Server Agent so that it could be scheduled. I also had the issue that I had many Cubes in the one Database and due to "inconsistent" data rules from source systems some of these would fail meaning if I processed at the Database level everything would fail. So I want to be able to split out the processing if ever there was an issue so that individual Cubes and dimensions would still be processed even if there was an error in another part of the Database (until the issue is fixed of course).

 Basically what I did was have a step that would attempt to process the database, if that step failed it would then move to another step where it will attempt various parts of the Database one bit at a time. Luckily we can process multiple Dimensions in parallel and you can process Cubes, Dimensions, Databases, Partitions etc. Programmatically.

 The below example can be run via a SQL Agent step (SQL Server Analysis Services Command) Just alter to reference your Dimensions and Partitions(Cubes). So I have the example of the Database "SSASDatabase" and two dimensions "Companies" and "Products" along with one Cube which has just the one partition "Sales". You will notice that the Dimensions get processed first (as they should with Cubes" and that I have two Dimensions within the set. That means they will both be processed at the same time. The Cube will be processed as soon as the Dimensions process step has been completed.
</parallel><br /> <!--Dimensions--> <batch xmlns=""> <parallel> <process> <object> <databaseid>SSASDatabase</DatabaseID> <dimensionid>Companies</DimensionID> </Object> <type>ProcessUpdate</type> <writebacktablecreation>UseExisting</writebacktablecreation> </process> <process> <object> <databaseid>SSASDatabase</DatabaseID> <dimensionid>Products</DimensionID> </Object> <type>ProcessUpdate</type> <writebacktablecreation>UseExisting</writebacktablecreation> </process> </parallel> <!--Current Measure Partitions--> <parallel> <process> <object> <databaseid>SSASDatabase</DatabaseID> <cubeid>Suspense</CubeID> <measuregroupid>Sales</MeasureGroupID> <partitionid>Sales</PartitionID> </Object> <type>ProcessFull</type> <writebacktablecreation>UseExisting</writebacktablecreation> </process> </parallel> </batch>