question

AmyJandreau-8392 avatar image
0 Votes"
AmyJandreau-8392 asked CarrinWu-MSFT commented

CUBE Processing best practices

Hi All,
I have a FACT table with over 60 million rows and over 20 dimensions, all but one dimension (customer-14 million rows) are small. The CUBE processing is taking 45 minutes. I tried processing all the related dimensions first and then processed the cube with not much of a difference. I then created Query based partitions based of the Year by joining the FACT to the Time Dimension. In total i created 10 partitions and there is a slight improvement , 42 mins. I have the following questions.
1) Do i need to create Aggregations?
2) Do i need to create 10 aggregations, one per each partition.
3) I do not want to include certain dimensions in the aggregation (so select None for these dimensions).

Thanks for your time.

sql-server-analysis-services
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @AmyJandreau-8392, we have not get a reply from you. Could below answers help you? If yes, please do "Accept Answer". By doing so, it will benefit for community members who have this similar issue. Your contribution is highly appreciated. Thank you!

0 Votes 0 ·

1 Answer

CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered CarrinWu-MSFT edited

Hi @AmyJandreau-8392,

Thanks for your posting.

You are using Multidimensional modes, right? I suggest that you should make a test in staging environment, and then you can use SQL Server Profiler to view how and when aggregations are used to satisfy queries. Logically, you could create aggregations to enhance query performance. For more information, please refer to below links:
Designing Aggregations (Analysis Services - Multidimensional)
Aggregations and Aggregation Designs
Improve Query Performance And Calculation Times Using SSAS-Aggregations



Best regards,
Carrin


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@CarrinWu-MSFT thanks,
I went through the links but no where i find an answer to my question, if it is ok to create aggregation for each partition.
Thanks

0 Votes 0 ·

Hi @AmyJandreau-8392, thanks for your replying. The above link (Designing Aggregations (Analysis Services - Multidimensional)) mentions that to set storage options and design aggregations for a partition, use the Aggregation Design Wizard. The wizard operates on a single partition of a measure group at a time so that you can select different options and designs for each partition. The wizard takes you through steps to configure storage and design aggregation for a partition. And each partition in a group can have 0 or 1 aggregation design objects. For more information, you could refer to this blog.

0 Votes 0 ·