I have a pivot table generated in Excel. I need to use the same MDX query which is used in Excel, I have extracted the same query from Excel which is like this -
SELECT {[Measures].[OQ],[Measures].[RQ],[Measures].[SQ],[Measures].[SRQ]}
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS ,
CrossJoin(
CrossJoin(
CrossJoin(
CrossJoin(
CrossJoin(
CrossJoin(
CrossJoin(
Hierarchize({DrilldownLevel({[PO].[Date].[All]},,,INCLUDE_CALC_MEMBERS)}),
Hierarchize({DrilldownLevel({[PO].[Priority].[All]},,,INCLUDE_CALC_MEMBERS)})),
Hierarchize({DrilldownLevel({[POL].[Container].[All]},,,INCLUDE_CALC_MEMBERS)})),
Hierarchize({DrilldownLevel({[PO].[Name].[All]},,,INCLUDE_CALC_MEMBERS)})),
Hierarchize({DrilldownLevel({[POL].[Num].[All]},,,INCLUDE_CALC_MEMBERS)})),
Hierarchize({DrilldownLevel({[PO].[Warehouse].[All]},,,INCLUDE_CALC_MEMBERS)})),
Hierarchize({DrilldownLevel({[POL].[Status].[All]},,,INCLUDE_CALC_MEMBERS)})),
Hierarchize({DrilldownLevel({[POL].[Factor].[All]},,,INCLUDE_CALC_MEMBERS)}))
ON ROWS
FROM [Purchases] CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS
This query returns about 3 lac records in excel, when I run this query in SSMS it gives error like "Server: The operation has been cancelled due to memory pressure.".
I am new to MDX, above query uses many CrossJoins, is there any way to optimize this query?
Thanks and Regards,
Amit Thakkar
