Sql-server – Problem with Processing Dimensions with Multiple Facts

dimensionfacttablesql serversql-server-2012ssas

I have a cube built in SSAS that uses 3 facts and 8 dimensions. Some of the dimensions are shared across 3 facts.

When I process one of the dimension, it tries to process the underlying measure groups, however, as the other dimensions are not yet updated with the new data, the measure group processing fails saying the key doesn't exist.

How should we trigger a dimension process in such a case?

Illustration:
3 Fact Tables C1, C2, C3;
8 Dimensions D1, D2, D3, D4, D5, D6, D7, D8

C1 is built on D1, D2, D3, D4
C2 is built on D1, D5, D6
C3 is built on D1, D2, D5, D7, D8

Assume that I have made updates in the Dimensions D1 and D5 and trigger a Dimension process one after the other using AMO.

When I process D1, it processes Dimension D1 and measure groups C1, C2 and C3. When it tries to process the measure group C2, it complains that the updated data in D5 is still not populated.
When I Process D5 first, it processes Dimension D5 and measure groups C2 and C3.
When it tries to process the measure group C3, it complains that the updated data in D1 is still not populated

Note:

  • It's SSAS 2012
  • We are processing dimensions using ProcessUpdate and
    process using ProcessFull

Please do let me know if you need any additional information.

Best Answer

You need to process D1 and D5 in the same transaction. Then both will complete processing before measure groups are processed. Make sure you mark Transaction=true (the default unless you explicitly set it to false) and Parallel. 

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Parallel>
    <Process>
      <Object>
        <DatabaseID>YourDB</DatabaseID>
        <DimensionID>D1</DimensionID>
      </Object>
      <Type>ProcessUpdate</Type>
    </Process>
    <Process>
      <Object>
        <DatabaseID>YourDB</DatabaseID>
        <DimensionID>D5</DimensionID>
      </Object>
      <Type>ProcessUpdate</Type>
    </Process>
    <Process>
      <Object>
        <DatabaseID>YourDB</DatabaseID>
        <CubeID>C1</CubeID>
      </Object>
      <Type>ProcessFull</Type>
    </Process>
  </Parallel>
</Batch>

I would highly recommend you digest this article.

Also are you processing all cubes as ProcessFull or only some cubes or some measure groups or some partitions. If you are processing all cubes and measure groups and partitions then don't bother with all this. Just do a ProcessFull on the database object. ProcessUpdate on dimensions is only for incremental processing which assumes you aren't processing all partitions.

Related Question