none
ssas multidimensional large many to many query issue

    Question

  • Hi,

    I have an issue with query performance with a large ssas md model, that has a many to many relationship. The cube shows data on website searches. So the fact table has a granularity of 1 line per seach with all its details, date, time of day, response time ec, and the bridge table has the granularity of how many products are returned per seach, it can be anywhere from 1 to 500.

    The volume of data on a daily basis is 30million searches (fact table) and bridge table is 100million rows

    Both fact table and bridge table are partitioned by the same criteria (day)

    I have used matrix compression as many product searches return the same list of products

    https://bideveloperextensions.github.io/features/Many-to-ManyMatrixCompression/

    I have added redundant dimensions

    https://blog.crossjoin.co.uk/2015/02/21/optimising-ssas-many-to-many-relationships-by-adding-redundant-dimensions/

    Cube processing is not a problem, I only process the latest day data. The issue is querying when the bridge table is involved. On a day level, it's slow but ok....but if a user was to see products returned in the search over the last month you can be waiting forever (it would be scanning 30 partitions of 100 million rows each)

    Any ideas? Changing the dimensional model? design techniques? Is SSAS md the right tool?

    thanks


    • Edited by kiwiNspain Friday, July 13, 2018 3:12 PM
    Friday, July 13, 2018 2:59 PM

All replies

  • Hi,

    Whenever it comes to query performance I will be considering 2 things.

    1. Reviewing the aggregations - You need to check if proper aggregations are designed for your partitions. 

    2. Attribute relationships -  You need to see if proper attribute relationships exists between the dimension attributes especially when you perform drill down and roll up actions.

    HTH,

    Ram


    Please vote as helpful or mark as answer, if it helps

    Friday, July 13, 2018 3:33 PM
  • Hi Ram,

    Yes I have attribute relationships and set to ridgid where possible.

    Aggregations I havent looked at yet, so good point. I'll have to investigate if they are useful as many reports are done at the day (partition level) and i believe aggregations wouldnt help too much here. However for week or month level reports I'm sure would be useful.

    thanks

    Friday, July 13, 2018 3:46 PM
  • >>Yes I have attribute relationships and set to ridgid where possible.

    It's not only setting attribute relationship to rigid. It's more important to ensure you have established a proper attribute relationship between the child and the parent in a hierarchy and other related attributes.

    Please follow this link for more details.

    HTH,

    Ram


    Please vote as helpful or mark as answer, if it helps

    • Proposed as answer by alexander fun Tuesday, July 17, 2018 10:52 AM
    Friday, July 13, 2018 6:54 PM
  • Hi kiwiNspain,

    Thanks for your question.

    >>>Any ideas? Changing the dimensional model? design techniques?
    If you have tried a lot to improve the query performance, then you might want to change the design for your model. The first thing you need to concern is your client requirement, then you might need to determine the granularity for your model. Please refer to below article talking about how to modeling your data:
    https://dwbi.org/data-modelling/dimensional-model/1-dimensional-modeling-guide

    >>> Is SSAS md the right tool?
    Since this is a large cube, SSAS MD is the right tool. The largest databases in production are multidimensional, with processing and query workloads running independently on dedicated hardware, each one optimized for its respective use. SSAS MD is better for larger database than SSAS Tabular.


    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

    • Proposed as answer by alexander fun Tuesday, July 17, 2018 10:52 AM
    Monday, July 16, 2018 3:07 AM
    Moderator