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.
Dimension.All.Member much slower than Dimension.Member
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
2 additional answers
Sort by: Most helpful
-
Lukas Yu -MSFT 5,821 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.
-
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