Share via

Dimension.All.Member much slower than Dimension.Member

Werner 21 Reputation points
2020-09-14T17:29:21.98+00:00

Hi - i have a huge dimension (~10 mio entries) where a 3rd party tool is accessing with
WITH
MEMBER [Measures].[x] AS
'1',
SELECT
{[Measures].[x]} ON COLUMNS,
Hierarchize({[Y].[All].[A],
[Y].[All].[B],
...
[Y].[All].[F]}) ON ROWS
FROM [MYCUBE]

  • performance for about 9 Elements - 2min (default measure is null value, tried with empty mdx script as well)
    compared with the same Statement but with [Y].[A], [Y].[B]... [Y].[F] - less than a second.
    Tried to trace it via SQL Profiler - nothing (only the Query Start / End / Serialize, no Subcubes, no File Loads - nothing... just running for 2min)
    Tried with warm and cold cache.
    The Name is unique, Hierarchy is fully optimized - and the [Y] is a plain attribute hierarchy without levels or other fancy stuff.

My assumption is that it takes that long to find the entry via some kind of full text search in the Dimension (it scales - one element 15 sec, 9 elements 2min).

If i access with [Y].[All].&[id] - it is also less than a second. Anyone ever encountered that?

thanks,
Werner

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,253 questions
0 comments
Accepted answer
  1. Darren Gosbell 2,376 Reputation points
    2020-09-16T02:43:25.533+00:00

    I have not seen anything using this old parent member path syntax for ages. Normally in MDX I would use <dimension>.<level>.<member> to unambiguously address a member. If you are using a 2 part name it could be causing unwanted scans of other attributes, so I'd suggest trying with a 3 part <dimension>.<level>.<member> name to see if that helps.

    1 person found this answer helpful.
    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Lukas Yu -MSFT 5,816 Reputation points
    2020-09-15T03:15:21.233+00:00

    Hi,

    Normally we would recommend to avoid ALL members , only if it is really necessary in the query.
    See this related case in the blog : Unnecessary All Members and Performance Problems

    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.

    1 person found this answer helpful.
    0 comments
  2. Werner 21 Reputation points
    2020-09-17T11:02:12.747+00:00

    Thanks for your inputs - probably we will highlight that topic to the 3rd party application.
    I assume multiple different root causes lead to that situation - e.g. in our design guide we enforce NamePath, then the Member is unique, combined with Attribute Hierarchy without a real hierarchy - and finally the 3rd party application that will get confused about those non-standard settings...

    Reason for the NamePath: we have a Web Application in place (self developed) that shows the current selection in a tree - and to make life easy for the application the name path was chosen. Application is already 10 years old and the successor of a 20 year old one - both relied on the name path...

    anyhow - thanks,
    Werner

    0 comments