April 1, 2015 at 7:50 am
Hi All,
I am having performance issues while using excel to browse SSAS cube.
I ran the profiler and captured the MDX queries and query which excel has auto generated has lot of cross joins
All i am doing is selecting 3 attributes in a dimension and i can't understand why excel is choosing to use cross joins
Can any one please give me some options on how to make excel not use cross joins ?
Query From BIDS :
SELECT NON EMPTY { } ON COLUMNS, NON EMPTY { ([Orders].[Order No].[Order No].ALLMEMBERS * [Orders].[Order Status].[Order Status].ALLMEMBERS * [Orders].[Branch].[Branch].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Sales_Detail] CELL PROPERTIES VALUE
Query From Excel :
SELECT
NON EMPTY CrossJoin(CrossJoin(Hierarchize({DrilldownLevel({[Orders].[Order No].[All]},,,INCLUDE_CALC_MEMBERS)}), Hierarchize({DrilldownLevel({[Orders].[Order Status].[All]},,,INCLUDE_CALC_MEMBERS)})), Hierarchize({DrilldownLevel({[Orders].[Branch].[All]},,,INCLUDE_CALC_MEMBERS)})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME,[Orders].[Order No].[Order No].[Branch],[Orders].[Order No].[Order No].[Legal Entity],[Orders].[Order No].[Order No].[Likelihood Of Deal],[Orders].[Order No].[Order No].[Order Status],[Orders].[Order No].[Order No].[Order Type],[Orders].[Order No].[Order No].[Products ID] ON COLUMNS FROM [Sales_Detail] CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS
April 1, 2015 at 9:09 am
The cross joins are not the issue here. Your query from BIDS also uses cross joins, denoted by the "*".
You do not have control over the query generated by Excel, so I would rather focus on ensuring that the cube structure is sound and that you have aggregations defined.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy