0

I have two cubes which updates in sequence. These takes very long to update, upwards of 5 hours due to their sizes.

I have a SQL agent job which updates the cubes. The job has type SQL Server Analysis Services Command with the following command:

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <ErrorConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400">
    <KeyErrorLimit>-1</KeyErrorLimit>
    <KeyNotFound>IgnoreError</KeyNotFound>
    <NullKeyNotAllowed>IgnoreError</NullKeyNotAllowed>
  </ErrorConfiguration>
  <Parallel>
    <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400">
      <Object>
        <DatabaseID>TheCube</DatabaseID>
      </Object>
      <Type>ProcessFull</Type>
      <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
    </Process>
  </Parallel>
</Batch>

I've tried to replace <Type>ProcessFull</Type> with <Type>ProcessDefault</Type> but this operation were completed in 0 seconds, and did not update any data at all (even though the underlying SQL tables were updated prior to when the cube job were executed).

What I want to achieve is to find an update option which updates the data, keeping the underlying cube structure intact, and which is faster than ProcessFull.

Conferring the docs.microsoft documentation here I gather that there are other options available, but they all seem to have a backside.

ProcessData seem plausible at a glance, but reading the description it claims that

Process Data Dimensions, cubes, measure groups, and partitions. Processes data only without building aggregations or indexes. If there is data is in the partitions, it will be dropped before re-populating the partition with source data.

(Where I have put italics on the part which says it won't update aggregations, even though I have aggregation which I use).

ProcessUpdate does also seem plausible at a glance, but

Process Update Dimensions Forces a re-read of data and an update of dimension attributes. Flexible aggregations and indexes on related partitions will be dropped.

(Where I once again have put my main concern in italics). What does that mean?

Can someone shed some light into this? Both cubes are used in production, and they take up so much time to process during the night, that I am very constrained in terms of testing different options myself, and I am of course concerned that a faulty option may cause a cascade of errors or the like given the complexity of the cube structure.

Has anyone found a way to update a cube, without having to process the cube using ProcessFull, and still maintain aggregations etc.?

  • Upwards of 5 hours seems excessive, how big is your datawarehouse and cube? This smells of a design issue in the underlying database – Tom V - Team Monica Mar 30 '18 at 7:15
  • If you change KeyNotFound=ReportAndStop, NullKeyNotAllowed=ReportAndStop, KeyErrorLimit=1 does it throw an error? Ignoring errors is expensive so I would recommending relational integrity issues be resolved in SQL. Let's eliminate that as a problem. – GregGalloway Mar 31 '18 at 0:41
  • What is the row count and column count of your largest few dimensions? What's the row count of your largest few measure groups? – GregGalloway Mar 31 '18 at 0:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.