Forum Discussion
Power Query connection to SSAS Database much slower than Power Pivot Connection
It's possible that the query we're generating is inefficient. If you are willing to send me the the M query you are using in a private message, I can take a look and see if there is something in it which isn't being folded correctly.
Here's the code
let
Source = AnalysisServices.Databases("kepionffm1.XYZ.com.au", [TypedMeasureColumns = true]),#"XYZ FFM" = Source{[Name="XYZ FFM"]}[Data],
Cashflow1 = #"XYZFFM"{[Id="Cashflow"]}[Data],
Cashflow2 = Cashflow1{[Id="Cashflow"]}[Data],#"Added Items" = Cube.Transform(Cashflow2, {{Cube.AddAndExpandDimensionColumn, "[Account]", {"[Account].[MemberId].[MemberId]"}, {"Account.MemberId"}}, {Cube.AddAndExpandDimensionColumn, "[Activities]", {"[Activities].[MemberId].[MemberId]"}, {"Activities.MemberId"}}, {Cube.AddAndExpandDimensionColumn, "[MeasureType]", {"[MeasureType].[MemberId].[MemberId]"}, {"MeasureType.MemberId"}}, {Cube.AddAndExpandDimensionColumn, "[Project]", {"[Project].[MemberId].[MemberId]"}, {"Project.MemberId"}}, {Cube.AddAndExpandDimensionColumn, "[Time]", {"[Time].[Day].[Day]"}, {"Time.Day"}}, {Cube.AddMeasureColumn, "Value", "[Measures].[Value]"}, {Cube.AddAndExpandDimensionColumn, "[Scenario]", {"[Scenario].[MemberId].[MemberId]"}, {"Scenario.MemberId"}}}),
#"Filtered Rows" = Table.SelectRows(#"Added Items", each (Cube.AttributeMemberId([Scenario.MemberId]) = "[Scenario].[MemberId].[Actual]" meta [DisplayName = "Actual"] or Cube.AttributeMemberId([Scenario.MemberId]) = "[Scenario].[MemberId].[Approved WOL]" meta [DisplayName = "Approved WOL"] or Cube.AttributeMemberId([Scenario.MemberId]) = "[Scenario].[MemberId].[Forecast]" meta [DisplayName = "Forecast"]) and (Cube.AttributeMemberId([MeasureType.MemberId]) = "[MeasureType].[MemberId].[Escalated Amount]" meta [DisplayName = "Escalated Amount"]))
in
#"Filtered Rows"
Thanks for replying Alex, I'll be back at the client next week and will send you the M code
Cheers
Wyn
