locked
SSAS 2008 R2 reading all partitions RRS feed

  • Question

  • Hi<o:p></o:p>

    The report is very slow and we see in sql profiler SSAS reading all year partitions even we filter with one month and report is very slow. The cube partitions are monthly. The cube size is around 150GB. A measure column is Last Non empty. The
    cube performance is very slow. We read prefetching article and we tried the options no luck. How can we restrict not reading all partitions? Any help/suggestions

    Thanks



    Sreeni

    Tuesday, April 29, 2014 2:32 PM

Answers

  • Hi Sreeni,

    I hope I am wrong but I think sorting out this type of issue remotely is very difficult.

    The book I can recommend is; -

    Microsoft SQL Server 2008 Analysis Services Unleashed

    The only other generic advice I can give is to narrow down the problem domain. In this case this means; -

    1) Creating a separate copy of your BIDS SSAS solution with a minimum number of dimensions and a single measure group with two partitions.

    2) Think of any other way you can make your separate copy of your BIDS SSAS solution as simple as possible whilst still being able to demonstrate the issue of unexpected partition scans, e.g. remove calculated members from your single measure group.

    The likely outcome of this is that you are likely to be closer to a resolution. The worst case scenario is that you would need to contract a third party on site consultant where your joint efforts would be much more focused.

    In short by creating a stripped down version of your cube you  are much more likely to identify the specific area of your cube design which is causing the unexpected scans.

    I hope this helps,

    Kind Regards,

    Kieran.


    Kieran Patrick Wood http://www.innovativebusinessintelligence.com http://uk.linkedin.com/in/kieranpatrickwood http://kieranwood.wordpress.com/


    Wednesday, April 30, 2014 3:26 PM

All replies

  • Hi Sreeni,

    Have you set the slicer property on your partitions, please find the following link; -

    Set the Slice on your SSAS Cube Partitions

    Kind Regards,

    Kieran.


    Kieran Patrick Wood http://www.innovativebusinessintelligence.com http://uk.linkedin.com/in/kieranpatrickwood http://kieranwood.wordpress.com/

    Tuesday, April 29, 2014 4:50 PM
  • Thanks for your replay, i have tried that option also,  same thing it is happening, after reading all partitions if we apply same date filter i.e different month the query result is fast because it is in cache, but if we add any other filter including date filter, again it start reading all partitions and query response time is very slow. I am not sure why it is not keeping cache if we apply any filters, already read all partitions once.

    Any help/suggestions please

    Thanks,


    Sreeni

    Tuesday, April 29, 2014 8:00 PM
  • Hi Sreeni,

    Do you have Many-To-Many relationship defined between this particular measure group and the dimension you are using in the report ?


    Saurabh Kamath

    Wednesday, April 30, 2014 1:40 AM
  • Hi Kamath,

    No, I am using Flexible.

    Thanks,

      

    Sreeni

    Wednesday, April 30, 2014 2:26 AM
  • I think Kamath is referring to the dimension usage tab, Sreeni in your response I think you are referring to the Attribute Relationship-> Attribute Relationship Type within the Dimension designer.

    I am struggling to give you further advice Sreeni. Which books have you read on SSAS I could maybe recommend some.


    Kieran Patrick Wood http://www.innovativebusinessintelligence.com http://uk.linkedin.com/in/kieranpatrickwood http://kieranwood.wordpress.com/


    Wednesday, April 30, 2014 2:47 AM
  • Hi Kieran,

    You are always free to recommend books.

    Please let me know if I mentioned anything wrong in my last reply ?

    In my last reply I was referring to "Unexpected Partition Scans" while using Many-To-Many relationship.

    http://cwebbbi.wordpress.com/2012/01/24/many-to-many-relationships-and-partition-slices/


    Saurabh Kamath

    Wednesday, April 30, 2014 3:31 AM
  • Relationship type is Regular and attribute relationship is Flexible. I am not using Many-to-Many relationship.

    Thanks


    Sreeni

    Wednesday, April 30, 2014 1:32 PM
  • Hi Anumala ,

    Could it be you're having a calculation within the cube ? so there is a range or any other calculation that uses not only one partition .


    Regards, David .

    Wednesday, April 30, 2014 2:15 PM
  • Hi David,<o:p></o:p>

    Thanks for your reply. Yes i have calculations in the cube, but i am not using in the report and using only base measure and also i have tried removing all calculations in the cube, still same issue.

    Thanks,<o:p></o:p>



    Sreeni

    Wednesday, April 30, 2014 2:30 PM
  • Thanks for the helpful link Saurabh

    Kieran Patrick Wood http://www.innovativebusinessintelligence.com http://uk.linkedin.com/in/kieranpatrickwood http://kieranwood.wordpress.com/

    Wednesday, April 30, 2014 3:16 PM
  • Hi Sreeni,

    I hope I am wrong but I think sorting out this type of issue remotely is very difficult.

    The book I can recommend is; -

    Microsoft SQL Server 2008 Analysis Services Unleashed

    The only other generic advice I can give is to narrow down the problem domain. In this case this means; -

    1) Creating a separate copy of your BIDS SSAS solution with a minimum number of dimensions and a single measure group with two partitions.

    2) Think of any other way you can make your separate copy of your BIDS SSAS solution as simple as possible whilst still being able to demonstrate the issue of unexpected partition scans, e.g. remove calculated members from your single measure group.

    The likely outcome of this is that you are likely to be closer to a resolution. The worst case scenario is that you would need to contract a third party on site consultant where your joint efforts would be much more focused.

    In short by creating a stripped down version of your cube you  are much more likely to identify the specific area of your cube design which is causing the unexpected scans.

    I hope this helps,

    Kind Regards,

    Kieran.


    Kieran Patrick Wood http://www.innovativebusinessintelligence.com http://uk.linkedin.com/in/kieranpatrickwood http://kieranwood.wordpress.com/


    Wednesday, April 30, 2014 3:26 PM