Search code examples
ssisssasolap

Bind 2 different OLAP Cube results

is there a way to bind 2 OLAP Cubes with the same structure into one? As for now I have big Datasets for past 10 years (20-50 milion records each cube) that is every day processed. For optimalization purposes I want to separate it. For exaple the data for past 8 years will be processed once and the new data will process daily. For it it would need 2 Cubes but can I merge results from it?

Edit. Im reading on blogs and forums about partitioning the data but how does it really work? Would I be able to process only the new data so I dont need to process all 30+ mil rows?

Solution

  • Processing these partitions is done separately from the cube design. One way to deal with this is by scripting out the commands and running them in a sql agent job:

    1- Connect to the ssas instance, browse to the dimensions folder and hit F7 to open the object explorer details window.
    2- Highlight all the dimensions, right click and select process enter image description here

    3- In the dialogue, select the script button and choose script action to new query window or script action to clipboard and save it someplace. This will be the xmla command to update all the dimensions.

    enter image description here

    4- Next, navigate to the measure group in which you made the partitions and select the last partition in the group which you want to refresh daily. Right click, select process and use the script option to create an xmla command

    enter image description here

    5- Next, select the measure group folder and select all the measure groups in the object explorer detail window, except for the partitioned group. Right click, select process and script out the command as in step 3.

    6- Now that you have all the commands, add them to a SQL Agent job with 3 xmla command steps, one to process the dimensions and the next two to process the measure groups and most recent partition. Note that if this is running under the sql agent service account, that account will need to have permissions to process the cube.

    tip add another job to run on the weekends which does a process full over the whole cube. This is a little cleanup step to make sure everything stays in sync.