Calling all Essbase/MDX Power Query Experts!

[pawlowski6132]

I need to write a native MDX query for pulling data from our Oracle/Essbase cube. I understand the syntax but, can't get a simple query to run. I know it's user error but, I can't figure out what's wrong with my statement. I'm certain it has to do with out of place (, [ {, etc.

[BDIAZCUSI]

Hello guys. I´m experiencing the same issue using MDX to query ESSBASE in power bi. 

Did anyone find a solution?. 

Regads. 

[v-juanli-msft]

Hi @pawlowski6132 

Please share the error message for further analysis.

Best Regards

Maggie

[Greg_Deckler]

Can you post the query?

[pawlowski6132]

Thanx for replies. I think this is prolly a case of a misplaced { } or () or []. I just don't have experience with MDX.

Here's teh query and Dialog and Error:

Here's the Data Structure:

----------------------------------

Here's the M that works.

let
Source = Essbase.Cubes("http://xxxxxxxxx.prg-dc.dhl.com:19000/aps/XMLA", [CommandTimeout=#duration(0, 1, 0, 0)]),
#"xxxxxxxxx.prg-dc.dhl.com" = Source{[Name="xxxxxxxxx.prg-dc.dhl.com"]}[Data],
AMER_P = #"xxxxxxxxx.prg-dc.dhl.com"{[Name=""]}[Data],
AMER_P.Oracle = AMER_P{[Name="AMER_P.Oracle"]}[Data],
#"Added Items" = Cube.Transform(AMER_P.Oracle,
{
{Cube.AddAndExpandDimensionColumn, "[RESP]", {"[RESP].Levels(10)", "[RESP].Levels(11)", "[RESP].Levels(4)", "[RESP].Levels(5)", "[RESP].Levels(6)", "[RESP].Levels(7)", "[RESP].Levels(8)", "[RESP].Levels(9)"}, {"RESP.[RESP].Level 10", "RESP.[RESP].Level 11", "RESP.[RESP].Level 4", "RESP.[RESP].Level 5", "RESP.[RESP].Level 6", "RESP.[RESP].Level 7", "RESP.[RESP].Level 8", "RESP.[RESP].Level 9"}}
})
in
#"Added Items"

-----------------------------------------

[venkatasuresh_g]

Hi

  Did you manage to get it working ? I am having difficulties working with Essbase source , when I use direct query it worked but I loose ability to add additional sources , whcih I need. When I try to use import method it never worked as it fails in import process with memeory issues , so I was thinking I should try MDX queries but I don't have experience of writing them. 

I want to know if you got this working , if yes how ?

Thanks 

Suresh 

[pawlowski6132]

So, yes, I was able to make figure out basic syntax, make connection and execute very simple query.

If you're interested, I can try to share the queries here?

[venkatasuresh_g]

Hi 

 That would be great if you can share the Queries , so I can try MDX method. 

Thanks 

Suresh Guddanti

[venkatasuresh_g]

Hi 

 Will you be able to share the MDX queries 

Thanks

Suresh Guddanti

[pawlowski6132]

Sorry Suresh, Here's some snippets of code that have returned results without error. I haven't expanded to create any production ready queries yet though. If you are going to put time into this, I'd like to find a way to collaborate?

SELECT

Members([TOTAL COST CENTERS].levels(3))

ON COLUMNS,

Members([Accounts].levels(12))

ON ROWS

From PL.PL

/*
SELECT

{[GROSS PROFIT]}

ON COLUMNS,
Filter(
Members([COST CENTER].levels(3)), [Gross Profit] > 0
)
ON ROWS

From PL.PL

*/

/*
SELECT
CrossJoin ({[GROSS PROFIT],[GROSS REVEN]}, {[FORD MAP]})
ON COLUMNS,
CrossJoin ({[SCENARIO].Children}, {Members([PERIOD].levels(3))})
ON ROWS
FROM PL.PL
*/

/*
SELECT
CrossJoin ({[2020 ACTUAL]}, {[FORD MAP]})
ON COLUMNS,
CrossJoin (
{[ACCOUNTS].[GROSS PROFIT]}, {Members([PERIOD].levels(3))}
)
ON ROWS
FROM PL.PL
*/

/*
SELECT
CrossJoin ({[2020 ACTUAL]}, {[FORD MAP]})
ON COLUMNS,
CrossJoin (
{[GROSS PROFIT],[GROSS REVEN],[COST OF SALES]}, {Members([PERIOD].levels(3))}
)
ON ROWS
FROM PL.PL
*/

/*
SELECT
CrossJoin ({[GROSS PROFIT],[GROSS REVEN]}, {[FORD MAP]})
ON COLUMNS,
CrossJoin ({[2020 ACTUAL]}, {Members([PERIOD].levels(3))})
ON ROWS
FROM PL.PL
*/