locked
SSAS 2012 Multidimensional Visual Studio 2012 wizard does not suggest any aggregations RRS feed

  • Question

  • The fact table has over 400 columns, there are 350+ foreign keys and about 105+ dimension tables. The fact table close to 200 million rows. With a smaller sample on fact rows (but full rows on dimension tables) I ran the wizard. First I ran it everything being default and it did not come up with any aggregations. Then I unchecked most fact table columns except 3 (Property Type (commercial and it's details, residential and it's details), Zip Code (and other items in the hierarchy) and County ) selected as full only, but in that case also the wizard did not suggested any aggregations.

    When I browse the cube (the cube compressed backup is about 20GB, the cube has equally divided 8 partitions and on an SSD drive, the test server has 32 gb of memory and analysis is not using more than few GB of memory when I am browsing it) queries (drilled down by zip code or county etc) take about a minute first time (faster the second time)

    Should I be creating aggregates somehow, if yes how?

    Thank you


    Gokhan Varol

    Saturday, August 3, 2013 7:31 PM

Answers

  • you can with BIDSHelper...but that shouldn't be the first step in the aggregation design process.

    it is odd that the aggregation design wizard didn't come up with any suggestions...which is why I think you should walk us through the wizard and the options you have chosen at each step.


    BI Developer and lover of data (Blog | Twitter)

    • Marked as answer by Elvis Long Wednesday, August 28, 2013 2:23 AM
    Monday, August 5, 2013 2:54 PM

All replies

  • Hi Gokhan,

    Aggregations are precalculated summaries of data from leaf cells, it improve query response time by preparing the answers before the questions are asked. So, we define aggregations to reduce the number of records that the storage engine needs to scan from disk to satisfy a query.

    In your case, you can consider creating some aggregations to improve query respond time. Here is a good article about Aggregation Design Best Practices in SSAS, it also applies to SSAS 2012. Please see:
    http://technet.microsoft.com/en-us/library/cc966399.aspx#EBAA

    If you have any feedback on our support, please click here.

    Best Regards,


    Elvis Long
    TechNet Community Support

    Monday, August 5, 2013 2:10 PM
  • did you try using the aggregation design wizard or usage-based optimization?

    assuming you used the aggregation design wizard, can you walk us through the wizard with screenshots so that we can see which settings you chose along the way?


    BI Developer and lover of data (Blog | Twitter)


    • Edited by BillAnton Monday, August 5, 2013 2:45 PM edit
    Monday, August 5, 2013 2:45 PM
  • I don't have a usage on this yet, it's a new cube. The wizard (not usage) does not give any suggestions. I don't see that I can create a aggregate bypassing the wizards or can I?

    Gokhan Varol

    Monday, August 5, 2013 2:49 PM
  • you can with BIDSHelper...but that shouldn't be the first step in the aggregation design process.

    it is odd that the aggregation design wizard didn't come up with any suggestions...which is why I think you should walk us through the wizard and the options you have chosen at each step.


    BI Developer and lover of data (Blog | Twitter)

    • Marked as answer by Elvis Long Wednesday, August 28, 2013 2:23 AM
    Monday, August 5, 2013 2:54 PM