none
Cube generation taking 7+ hours

    Question

  • We have a pretty large cube, with thousands of partitions. The way pour process works is that if there is a cube schema change, like adding new dimension or removing one, etc..., then we drop the SSAS DB and regenerate the cube from scratch in an automated process through AMO calling XMLA scripts.

    The problem is it is now taking 7+ just to generate an empty cube shell, that is, a fully structured cube with all measure group partitions, but no data. Data comes later.

    We do partitions by day, so every new day there is a new cube partition per MG.

    Our data goes back to 2009. So 4 years x 365 partitions x 13 measure groups, that 18,980 measure group partitions that have to be generated, and it's growing with each passing day. Our data is based on daily usage patterns, this is why we have partitioned by day.

    Aside from beefing up hardware, are there any directions we could go in or suggestions for making the regeneration of an empty cube shell speed up? I'm almost certain that the slow down is in generating the large number of partitions.

    Wondering if perhaps partition creation can be palatalized in xmla, haven't found anything like that, other than for processing.

    Thanks!


    Wednesday, August 21, 2013 9:56 PM

Answers

  • Hello ,

      Below are some thoughts which may help you

      1. Instead of recreating the complete cube , you pretty much generate the scripts pertaining to the modified dimension , measure groups & partitions alone to apply.

      2. Creating partitions in parallel should be quite possible in my point in view by running multiple executable in parallel. If you try that please let us know if you are able succesfully do that.


    Best Regards Sorna

    • Marked as answer by Bjorn Turoque Friday, August 23, 2013 4:13 PM
    Friday, August 23, 2013 3:12 PM

All replies

  • Bueller?
    Friday, August 23, 2013 2:57 PM
  • Hello ,

      Below are some thoughts which may help you

      1. Instead of recreating the complete cube , you pretty much generate the scripts pertaining to the modified dimension , measure groups & partitions alone to apply.

      2. Creating partitions in parallel should be quite possible in my point in view by running multiple executable in parallel. If you try that please let us know if you are able succesfully do that.


    Best Regards Sorna

    • Marked as answer by Bjorn Turoque Friday, August 23, 2013 4:13 PM
    Friday, August 23, 2013 3:12 PM
  • Hi there,

    I have found the SQL Server Analysis Services Performance White Paper to be exceptionally detailed and well put together for answer questions of this nature.  Have you had a chance to peruse this white paper to see if it can be of any value to you in answering your question?

    Here is the white paper:

    http://www.microsoft.com/en-us/download/details.aspx?id=17303

    David


    David Duncan

    Friday, August 23, 2013 3:33 PM
  • Hhhmmm, this might be worth doing! I will definitely look into trying this route perhaps with the TPL.

    Thanks for that idea.

    Friday, August 23, 2013 4:12 PM