locked
M:M Performance issue in SSAS RRS feed

  • Question

  • Hi All,

    I have a requirement where the two tables are M:M

    So in order to implement that I have created bridge table at SSDT level and the relationship becomes M:1 and 1:M 

    The problem is when I run a report with a column which has many distinct values, the report keeps spinning for a long time and end up with low memory error.

    However when I take column which has very less distinct values such as Type, Status etc. the report works fine.

    And I have to create a report with the column which has many distinct names. 

    What best can be done to fix this performance issue. I have various M:M use cases. Have gone through various blogs where they have suggested to split the table to make it 1:m and m:1 but does not help.

    Any suggestions would be really appreciated.

    Regards,

    Akash

    Friday, March 16, 2018 9:13 AM

All replies

  • Hi Akash,

    Thanks for your question.

    In this scenario, please try to use related distinct count pattern in below blog:
    https://www.daxpatterns.com/distinct-count/

    The Related Distinct Count pattern allows you to apply the distinct count calculation to any column in any table in the data model. Instead of just counting the number of distinct count values in the entire table using only the DISTINCTCOUNT function, the pattern filters only those values related to events filtered in another table.


    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

    Monday, March 19, 2018 2:04 AM
  • Hi Willson,

    My question is not related to a metric with distinct count. My concern is when I query a varchar columns which has too many distinct values, it takes a lot of time to show result. 

    Metric is not my report requirement.

    I have to query dimensional data point.

    Monday, March 19, 2018 8:46 AM
  • Hi Akash,

    Thanks for your question.

    >>>My question is not related to a metric with distinct count. My concern is when I query a varchar columns which has too many distinct values, it takes a lot of time to show result.
    Sorry about my missunderstanding. In this scenario, you might consider to integrate the high cardinality column into your fact table. Please refer to column [Sales Order Number] in fact table Reseller Sales in SSAS tabular sample database.

    And It's known perfomance issue for high cardinality column, thus I would not suggest you to create report with the high cardinality column in SSAS. For report with high cardinality data, it's much better use T-SQL to achieve the same thing.


    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 Wednesday, March 21, 2018 1:37 PM
    Monday, March 19, 2018 9:13 AM
  • Thank you. Would you be able to forward me the link where it is mentioned about performance issue with high cardinality column. 

    Would you mind suggesting some examples wherein I can see T-SQL method of doing the same ?

    Did not quite understand about that.

    Regards,

    Akash

    Monday, March 19, 2018 10:10 AM
  • Hi Akash,

    Thanks for your question.

    >>>Would you be able to forward me the link where it is mentioned about performance issue with high cardinality column.
    Please check "chapter 14 Optimizing data model"---"Columns cardinality" in book "The Definitive Guide to DAX" written by Marco Russo and Alberto Ferrari.

    >>>Would you mind suggesting some examples wherein I can see T-SQL method of doing the same ?
    For T-SQL, it should be much easier, just select [cardinality column], data from TableA a join TableB B on a.id=b.id where ....


    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, March 21, 2018 5:51 AM