Hi
We are exploring the feasibility of using powerBI for all the reports from the Hyperion Essbase.
We noticed that the latest PowerBI release had fixed some of the issues with Essbase adapter. I am now able to connect to Essbase using Import Data or Direct query. Both works and I am able to see data refreshed from the essbase cube. But as soon as I try to add filter criteria it breaks down.
Because of the size of the cube, we like to add filter criteria to limit the dataset refreshed from Essbase.
Below is extracted from "Advanced Editor" generated by power BI when I try to add Filter to the datasource
#"Added Items" = Cube.Transform(Cost.Cost,
{
{Cube.AddAndExpandDimensionColumn, "[Years]", {"[Years].Levels(1)"}, {"Years.[Years].Level 1"}},
{Cube.AddAndExpandDimensionColumn, "[Currency]", {"[Currency].Levels(1)"}, {"Currency.[Currency].Level 1"}},
{Cube.AddAndExpandDimensionColumn, "[Period]", {"[Period].Levels(1)"}, {"Period.[Period].Level 1"}},
{Cube.AddAndExpandDimensionColumn, "[Scenario]", {"[Scenario].Levels(1)"}, {"Scenario.[Scenario].Level 1"}},
{Cube.AddAndExpandDimensionColumn, "[Version]", {"[Version].Levels(1)"}, {"Version.[Version].Level 1"}},
{Cube.AddMeasureColumn, "Total Costs", "[Total Costs]"}
}),
#"Filtered Rows" = Table.SelectRows(#"Added Items", each (Cube.AttributeMemberId([#"Years.[Years].Level 1"]) = "[FY19]" meta [DisplayName = "Y2019"]))
in
#"Filtered Rows"
Error Message:
Expression.Error: Essbase Error (1260052): Syntax error in input MDX query on line 9 at token ','
