I'm trying to pull some data from a cube I use in business which has millions of data in it. I am using MDX, which I'm very new to, and I'm trying to replicate the output of one of my pivot tables. I created the query and it seems like everything is in the right place, however the output seems to be inverted (rows on columns and columns on rows, please see pictures for details) And for some reason I can't figure out how to fix it.
This image shows both the desired output and the actual output: This is the query I am using:
SELECT NON EMPTY CrossJoin(CrossJoin(CrossJoin(CrossJoin(CrossJoin(Hierarchize({DrilldownLevel({[Dem
Product].[Dem Product Hierarchy].[All]},,,INCLUDE_CALC_MEMBERS)}),
Hierarchize({DrilldownLevel({[TheDate].[Fiscal Year].[All]},,,INCLUDE_CALC_MEMBERS)})),
Hierarchize({DrilldownLevel({[TheDate].[Quarter].[All]},,,INCLUDE_CALC_MEMBERS)})),
Hierarchize({DrilldownLevel({[TheDate].[Month].[All]},,,INCLUDE_CALC_MEMBERS)})),
Hierarchize({DrilldownLevel({[Running].[Business].[All]},,,INCLUDE_CALC_MEMBERS)})),
Hierarchize({DrilldownLevel({[Reporting].[segment].[All]},,,INCLUDE_CALC_MEMBERS)}))
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME
ON COLUMNS
FROM
(SELECT
({[Dem Product].[Dem Product Hierarchy].[Dem allProducts].&[ProductX],
[Dem Product].[Dem Product Hierarchy].[Dem allProducts].&[ProductY],
[Dem Product].[Dem Product Hierarchy].[Dem allProducts].&[ProductZ],
[Dem Product].[Dem Product Hierarchy].[Dem allProducts].&[ProductA],
[Dem Product].[Dem Product Hierarchy].[Dem allProducts].&[ProductB],
[Dem Product].[Dem Product Hierarchy].[Dem allProducts].&[ProductC],
[Dem Product].[Dem Product Hierarchy].[Dem allProducts].&[ProductD],
{[Reporting].[segment].&[Online telecom],
[Reporting].[segment].&[Online shop],
[Reporting].[segment].&[ground sales],
[Reporting].[segment].&[Retail],
[Reporting].[segment].&[third party]})
ON COLUMNS
FROM [SalesCube])
WHERE
([Reporting.[UPSegment].&[1stDivision],
[Measures].[BusinessRevenue]) CELL PROPERTIES VALUE,
FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS
the reason I want the output is so I can import the data into power bi and manipulate to add adhoc measures needed to run my business. ANY HELP I CAN GET WILL BE MUCH APPRECIATED =) THANK YOU!
try the query below
SELECT NON EMPTY CrossJoin(CrossJoin(CrossJoin(CrossJoin(CrossJoin(Hierarchize({DrilldownLevel({[Dem
Product].[Dem Product Hierarchy].[All]},,,INCLUDE_CALC_MEMBERS)}),
Hierarchize({DrilldownLevel({[TheDate].[Fiscal Year].[All]},,,INCLUDE_CALC_MEMBERS)})),
Hierarchize({DrilldownLevel({[TheDate].[Quarter].[All]},,,INCLUDE_CALC_MEMBERS)})),
Hierarchize({DrilldownLevel({[TheDate].[Month].[All]},,,INCLUDE_CALC_MEMBERS)})),
Hierarchize({DrilldownLevel({[Running].[Business].[All]},,,INCLUDE_CALC_MEMBERS)})),
Hierarchize({DrilldownLevel({[Reporting].[segment].[All]},,,INCLUDE_CALC_MEMBERS)}))
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME
ON rows,
[Measures].[BusinessRevenue] on columns
FROM
(SELECT
({[Dem Product].[Dem Product Hierarchy].[Dem allProducts].&[ProductX],
[Dem Product].[Dem Product Hierarchy].[Dem allProducts].&[ProductY],
[Dem Product].[Dem Product Hierarchy].[Dem allProducts].&[ProductZ],
[Dem Product].[Dem Product Hierarchy].[Dem allProducts].&[ProductA],
[Dem Product].[Dem Product Hierarchy].[Dem allProducts].&[ProductB],
[Dem Product].[Dem Product Hierarchy].[Dem allProducts].&[ProductC],
[Dem Product].[Dem Product Hierarchy].[Dem allProducts].&[ProductD],
{[Reporting].[segment].&[Online telecom],
[Reporting].[segment].&[Online shop],
[Reporting].[segment].&[ground sales],
[Reporting].[segment].&[Retail],
[Reporting].[segment].&[third party]})
ON COLUMNS
FROM [SalesCube])
WHERE
([Reporting.[UPSegment].&[1stDivision]) CELL PROPERTIES VALUE,
FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS