Thursday, August 4, 2011

SSAS: Which change makes the cube/dimension unprocessed?

In many cases, some of OLAP cubes are huge and Full Process takes a lot of time.
Therefore, it’s important to know which change, when the updated solution is deployed, causes the cube to move to “Unprocessed” status and needs new full process. Dimension process status is also critical since if dimension moves to unprocessed, all cubes that have connection to this dimension will be unprocessed too.

Here is the detailed tested list of different actions, and how they apply on cube (tested on SSAS 2008. I don’t remember any difference with  SSAS 2005, but not certified)
We can summarize the results in one sentence: Every action that affects data will move cube or dimension status to unprocessed. Such changes are also called structural changes, meaning any change that apply on cube/dimension structure (new element, edition on aggregation method, etc.) and therefore will require data loading or reloading.
One exception: Adding or deleting translation to dimension unprocesses it when deployed. I don’t know why..
And now to the detailed list:
Type
Action
Unprocess cube/dimension?
Cube
New measure group
Yes
Cube
New measure
Yes
Cube
Edit measure aggregation method
Yes
Cube
Measure format
No
Cube
Measure name
No
Cube
Measure display folder
No
Cube
ErrorConfiguration edition
No
Cube
Edit dimension usage
Yes
Cube
Calculations
No
Cube
Add, edit or delete kpi
No
Cube
Add, edit or delete action
No
Cube
Edit partition query
No, not applied till next process.
Cube
Add new partition
No, new partition unprocessed.
Cube
Edit partition storage mode
No, but data is empty.
Cube
Create,edit and assign aggregations
No, not applied till next process.
Cube
Add, edit or delete perspective
No
Cube
Add, edit or delete translation
No
Dim
Add attribute to dim
Yes
Dim
Edit attribute name
No
Dim
Order by property of attribute
Yes
Dim
AttributeHierarchyVisible property
No
Dim
Edit attribute relationship
Yes
Dim
Add or delete translation to dim
Yes
Dim
Edit translation
No
Other
Add, Edit, Delete Role
No
Other
Edit data source
No, not applied till next process.
Other
Edit DSV
No


Finally, 3 remarks:
·         BIDS Helper add-in gives you possibility to deploy MDX script or aggregations only, so you can be sure nothing else is affected.
·         Use Deployment Wizard tool when you want to deploy without overwriting Partitions or roles, in cases they were edited in the OLAP DB.
·         If your cube is down and you need to have one measure group/partition available without processing the entire cube (because of process long time), you can first process the cube with “Process structure” Process mode. It will move cube status to Processed but without any data. Then you can process the specific part you want and it will be available.

4 comments:

  1. Hello,
    I have a Data Warehouse in SSAS 2008 configured with Dimensions (such as customers and products) and Measure Groups (such as inventory and orders) which are partitioned at the monthly level (i.e. Jan 2011, Feb 2011, etc.). Data is loaded on a daily basis via SSIS packages. When the data has completely loaded, a script runs to determine the partitions that need to be processed (i.e. if only orders for October 2011 were added then only the Order October 2011 partition is included in the xmla script).
    In order to save on time (since it takes about 2 hours to process the cube due to the volume of data), is there a way to only process the dimensions and affected partitions? Currently an SSIS package is executing two xmla scripts: 1) to process all of the dimensions [which takes roughly 15 minutes to run] and 2) to process the affected partitions and cube [which takes roughly 2 hours to run]. However if I don't inclue the directive to also process the cube (I am using ProcessUpdate) the cube becomes in an Unprocessed state.
    Can you provide any insight as to whether it is possible to add data to a cube and only process the dimensions and affected partitions without having to process the cube itself and keep the cube in a Processed state so it can be queried?

    Thanks for your help!
    Shannon

    ReplyDelete
  2. Hi Shannon,
    I don't understand why your cube moves to unprocessed. If the only thing you do is to process update dimensions and then process relevant partitions, it shouldn't happen.
    Anyway, you can solve it by adding a step between 2 above: process structure of entire cube. It moves status to processed but without any data inside, and then you can process relevant partitions.

    ReplyDelete
  3. Thanks for the follow up!

    Upon further review of the xmla script for processing the dimensions, I am setting the Type to ProcessFull (versus ProcessUpdate or ProcessAdd). Could that be the problem?
    Otherwise I will try your suggestion of:
    1. processing the dimensions
    2. process the cube as ProcessStructure
    3. processing the partitions that have updated data

    ReplyDelete
  4. Hi,
    I need to comment out that is need to delete translations on one dimension, Do I need to do Full process of the dimension followed by cube again? or just process update is enough?

    Thank you!!!
    Kiran

    ReplyDelete