locked
SSAS "Building indexes for fact parition" issue RRS feed

  • Question

  • Hello,

    Our SSAS 2014 cube is steadily taking longer and longer to process. Doing a manual refresh, I noticed there is a "Building indexes for the XXX partition" that is taking a very, very long time. Our biggest fact table has about 260,000,000 rows in it.

    Are there any techniques / best practices for investigating / improving this? I am coming back to SSAS after a multi year "break"...

    Thanks in advance...


    • Edited by amir tohidi Thursday, April 6, 2017 3:05 PM
    Thursday, April 6, 2017 3:05 PM

Answers

  • Hi,

    Edit: Do u have the Problems while Processing the Dimension or for ProcessIndex of the Partitions?

    u could try to "play" with some Instance properties for improving ProcessIndex: OLAP/Process/AggregationMemoryLimitMax and OLAP/Process/AggregationMemoryLimitMin.

    Second: What kind of Aggregation do u have? Rigid or Flexible? For Rigids dont change over time so wont invalidate after a ProcessUpdate o a dimension.

    Third: How is the Partition strategy, how big are your partitions, think of splitting it, but dont get too small partitions.

    Fourth: Rethink of the Aggregationdesign -  u can have different aggregations for different partitions. So for older Partitions which are queried not so often use less Aggregations.

    Fifth: Running a ProcessDefault instead of a ProcessIndex: ProcessDefault would only make an ProcessIndex where the Agregations are invalid.

    Edit: Do u have the Problems while Processing the Dimension or for ProcessIndex of the Partitions?

    Kr J


    • Edited by yger Monday, April 10, 2017 7:23 AM
    • Proposed as answer by alexander fun Tuesday, April 11, 2017 9:42 AM
    • Marked as answer by amir tohidi Wednesday, April 12, 2017 10:45 AM
    Monday, April 10, 2017 7:22 AM

All replies

  • Hi Amir,

    Thanks for your question.

    According to your description, it seems that index fragmentation exist in your XXX partition.

    In this scenario, I would suggest you to run a script to selectively reorganize/rebuild indexes depending on the level of index fragmentation in your partition, and then update statistics for the handful of tables that are directly related to the cube query, immediately prior to processing the cube.

    For more information, you can also refer to a similar thread SQL Server 2016 - Why does one partition process much slower than all others? - ReadData shows 10,000 rows processed in 30 seconds .

    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, April 7, 2017 5:13 AM
  • A couple of other options that might help you.

    1. If you have Enterprise version of SQL Server, partitions might allow you to only fully process the latest partition. (I know your message above indicates a partition name, but that doesn't necessarily mean that you have partitioned your measure group.)

    2. If your fact records don't change, just get new ones inserted, use incremental process rather than a full process.

    Hope that helps,


    Richard

    Monday, April 10, 2017 1:43 AM
  • Hi,

    Edit: Do u have the Problems while Processing the Dimension or for ProcessIndex of the Partitions?

    u could try to "play" with some Instance properties for improving ProcessIndex: OLAP/Process/AggregationMemoryLimitMax and OLAP/Process/AggregationMemoryLimitMin.

    Second: What kind of Aggregation do u have? Rigid or Flexible? For Rigids dont change over time so wont invalidate after a ProcessUpdate o a dimension.

    Third: How is the Partition strategy, how big are your partitions, think of splitting it, but dont get too small partitions.

    Fourth: Rethink of the Aggregationdesign -  u can have different aggregations for different partitions. So for older Partitions which are queried not so often use less Aggregations.

    Fifth: Running a ProcessDefault instead of a ProcessIndex: ProcessDefault would only make an ProcessIndex where the Agregations are invalid.

    Edit: Do u have the Problems while Processing the Dimension or for ProcessIndex of the Partitions?

    Kr J


    • Edited by yger Monday, April 10, 2017 7:23 AM
    • Proposed as answer by alexander fun Tuesday, April 11, 2017 9:42 AM
    • Marked as answer by amir tohidi Wednesday, April 12, 2017 10:45 AM
    Monday, April 10, 2017 7:22 AM
  • Thanks for the replies so far. I haven't been able to try any of them up until now as I have been building a new DEV environmentso I can investigate.

    A manual Cube process, it clearly showed what is slowing down our batch: there is a step Processing Partition 'Daily Balances' which says it needs to process to 4056 of something (see image)

    The step is executing a SQL query against a Fact table with 250M+ rows in it. The Execution Plan for the query shows a 64% cost on a clustered index key (see image).

    Based on this info, are the above suggestions still applicable. I have kicked off a Reorganise and Rebuild on the index but it hasn't finished processing yet.


    Tuesday, April 11, 2017 11:56 AM
  • Hi Amir,

    As stated, start with building partitions, for example yearly partitions and only process the most recent partition.

    There is not much need to do a full process on data from years ago that doesnt change. Once u fixed this issue its nice to look at the other suggestions such as aggregations. Aggregations improve Query performance but take much longer for your measure Groups to process.

    Tuesday, April 11, 2017 12:53 PM
  • Following the above advice and help (thanks everyone) and our DBA's recommendation, using Query Binding, I changed the Cubes to restrict data to the latest data only.

    But, I am still seeing a slow progress as highlighted below. What is this? 4058 of what is being processed that is so slow.

    I have processed each dimension individually and the worst one took 7 minutes. So, it is definitely the Fact table that is causing the performance issues.

    Tuesday, April 11, 2017 4:54 PM
  • BTE reorg & rebuild of indexes wasn't necessary and made no difference. I didn't know that our DBAs rebuild all indexes every night before batch starts.
    Tuesday, April 11, 2017 4:59 PM
  • Hi Amir,

    I’m writing to follow up with you on this post. Was the problem resolved after performing the solution provided by Yger ? 


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, April 12, 2017 6:13 AM
  • Hi Wilson,

    I have implemented Yger's recommendation (partition) and it has made a dramatic difference.

    I have kept that thread unanswered as I am hoping someone can tell what the 4058 in the above image is. Then I will mark Yger's response as answer.

    Wednesday, April 12, 2017 8:38 AM
  • Hi Amir,

    Thanks for your response.

    As I test in my cube Adventure Works, 4058 should be the total aggregations number in your partition 'Daily Balances'.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, April 12, 2017 9:09 AM
  • Thanks Willson.

    I don't remember us setting up aggregations back in 2011.

    Checking our Cube structure in BIDS, I see the attached. These must have been created by the Cube Wizard.

    Is there anything that can be done (other than partitions) to speed up their performance?

    Wednesday, April 12, 2017 11:07 AM
    • Proposed as answer by alexander fun Wednesday, April 12, 2017 11:37 AM
    • Unproposed as answer by alexander fun Wednesday, April 12, 2017 11:37 AM
    Wednesday, April 12, 2017 11:09 AM