Hi,
I am trying to improve the effiency of the query below.
The database I am reaching is huge but the data I need is not large. I thought that adding the filters in the end would have improved performance but it still thanks a lot of time to to update the query or refresh the data.
I am probably missing some basic good practice to get it working properly.
I cannot do a live link to the dataset as some of the data will have to be transformed afterwards.
Thanks in advance.
let
Source = AnalysisServices.Databases("link://xxx.azurewebsites.net", [TypedMeasureColumns=true, Implementation="2.0"]),
#"MS - Orders1" = Source{[Name="MS - Orders"]}[Data],
#"MS - Orders2" = #"MS - Orders1"{[Id="MS - Orders"]}[Data],
#"MS - Orders3" = #"MS - Orders2"{[Id="MS - Orders"]}[Data],
#"Added Items1" = Cube.Transform(#"MS - Orders3",
{
{Cube.AddAndExpandDimensionColumn, "[Dim_Customers]", {"[Dim_Customers].[Hrchy_Customers].[Billing Account]", "[Dim_Customers].[Hrchy_Customers].[Corporate Account]", "[Dim_Customers].[Hrchy_Customers].[Sales Account]"}, {"Dim_Customers.Billing Account.3", "Dim_Customers.Corporate Account.3", "Dim_Customers.Sales Account.2"}},
{Cube.AddAndExpandDimensionColumn, "[Dim_Date]", {"[Dim_Date].[Date].[Date]", "[Dim_Date].[Month].[Month]", "[Dim_Date].[Month (Date)].[Month (Date)]", "[Dim_Date].[Month#].[Month#]", "[Dim_Date].[Quarter].[Quarter]", "[Dim_Date].[Quarter#].[Quarter#]", "[Dim_Date].[Year].[Year]", "[Dim_Date].[Year (Date)].[Year (Date)]", "[Dim_Date].[YearMonth].[YearMonth]", "[Dim_Date].[YearMonth (PI Display)].[YearMonth (PI Display)]", "[Dim_Date].[YearQuarter].[YearQuarter]", "[Dim_Date].[Hrchy_Date].[Year (Date)]", "[Dim_Date].[Hrchy_Date].[Month (Date)]", "[Dim_Date].[Hrchy_Year_Date].[Year]", "[Dim_Date].[Hrchy_Year_Date].[YearQuarter]", "[Dim_Date].[Hrchy_Year_Date].[YearMonth]"}, {"Dim_Date.Date.1", "Dim_Date.Month.1", "Dim_Date.Month (Date).3", "Dim_Date.Month#.1", "Dim_Date.Quarter.1", "Dim_Date.Quarter#.1", "Dim_Date.Year.3", "Dim_Date.Year (Date).3", "Dim_Date.YearMonth.3", "Dim_Date.YearMonth (PI Display).1", "Dim_Date.YearQuarter.3", "Dim_Date.Year (Date).2", "Dim_Date.Month (Date).2", "Dim_Date.Year.2", "Dim_Date.YearQuarter.2", "Dim_Date.YearMonth.2"}},
{Cube.AddAndExpandDimensionColumn, "[Dim_Product_Line]", {"[Dim_Product_Line].[Product Line (Code)].[Product Line (Code)]", "[Dim_Product_Line].[Product Line (Desc)].[Product Line (Desc)]"}, {"Dim_Product_Line.Product Line (Code).1", "Dim_Product_Line.Product Line (Desc).1"}},
{Cube.AddAndExpandDimensionColumn, "[Dim_xxx_Organization]", {"[Dim_xxx_Organization].[Country (Code)].[Country (Code)]", "[Dim_xxx_Organization].[Country (Desc)].[Country (Desc)]", "[Dim_xxx_Organization].[Geomarket (Code)].[Geomarket (Code)]", "[Dim_xxx_Organization].[Geomarket (Desc)].[Geomarket (Desc)]"}, {"Dim_xxx_Organization.Country (Code).1", "Dim_xxx_Organization.Country (Desc).1", "Dim_xxx_Organization.Geomarket (Code).1", "Dim_xxx_Organization.Geomarket (Desc).1"}},
{Cube.AddAndExpandDimensionColumn, "[Fact_MandS_Orders]", {"[Fact_MandS_Orders].[Field Ticket Ref #].[Field Ticket Ref #]", "[Fact_MandS_Orders].[FTL Approval Date].[FTL Approval Date]", "[Fact_MandS_Orders].[FTL Approver ID].[FTL Approver ID]", "[Fact_MandS_Orders].[FTL Submission Date].[FTL Submission Date]", "[Fact_MandS_Orders].[iDistrict/eTrace#].[iDistrict/eTrace#]", "[Fact_MandS_Orders].[Invoice Create Date].[Invoice Create Date]", "[Fact_MandS_Orders].[Invoice Post Date].[Invoice Post Date]", "[Fact_MandS_Orders].[Order Approval Date].[Order Approval Date]", "[Fact_MandS_Orders].[Order Creation Date].[Order Creation Date]", "[Fact_MandS_Orders].[Order Creator Job Category].[Order Creator Job Category]", "[Fact_MandS_Orders].[Order First Submission Date].[Order First Submission Date]", "[Fact_MandS_Orders].[Order Job End Date].[Order Job End Date]", "[Fact_MandS_Orders].[Order Type].[Order Type]", "[Fact_MandS_Orders].[Revenue Status].[Revenue Status]", "[Fact_MandS_Orders].[Siebel Order ID].[Siebel Order ID]", "[Fact_MandS_Orders].[Stage 1].[Stage 1]", "[Fact_MandS_Orders].[Stage 5].[Stage 5]", "[Fact_MandS_Orders].[Stage_2].[Stage_2]", "[Fact_MandS_Orders].[Stage_3].[Stage_3]", "[Fact_MandS_Orders].[Stage_4].[Stage_4]"}, {"Fact_MandS_Orders.Field Ticket Ref #.1", "Fact_MandS_Orders.FTL Approval Date.1", "Fact_MandS_Orders.FTL Approver ID.1", "Fact_MandS_Orders.FTL Submission Date.1", "Fact_MandS_Orders.iDistrict/eTrace#", "Fact_MandS_Orders.Invoice Create Date.1", "Fact_MandS_Orders.Invoice Post Date", "Fact_MandS_Orders.Order Approval Date.1", "Fact_MandS_Orders.Order Creation Date.1", "Fact_MandS_Orders.Order Creator Job Category", "Fact_MandS_Orders.Order First Submission Date", "Fact_MandS_Orders.Order Job End Date.1", "Fact_MandS_Orders.Order Type.1", "Fact_MandS_Orders.Revenue Status.1", "Fact_MandS_Orders.Siebel Order ID.1", "Fact_MandS_Orders.Stage 1.1", "Fact_MandS_Orders.Stage 5.1", "Fact_MandS_Orders.Stage_2.1", "Fact_MandS_Orders.Stage_3.1", "Fact_MandS_Orders.Stage_4.1"}},
{Cube.AddMeasureColumn, "Revenue (kUSD).1", "[Measures].[Revenue (kUSD)]"},
{Cube.AddMeasureColumn, "Stage 2.1", "[Measures].[Stage 2]"},
{Cube.AddMeasureColumn, "Stage 3.1", "[Measures].[Stage 3]"},
{Cube.AddMeasureColumn, "Stage 4.1", "[Measures].[Stage 4]"},
{Cube.AddMeasureColumn, "Total DIR Stage.1", "[Measures].[Total DIR Stage]"}
}),
#"Filtered Rows" = Table.SelectRows(#"Added Items1", each (Cube.AttributeMemberId([Dim_Date.Year.2]) = "[Dim_Date].[Hrchy_Year_Date].[Year].&[2019]" meta [DisplayName = "2019"]) and (Cube.AttributeMemberId([Fact_MandS_Orders.Revenue Status.1]) = "[Fact_MandS_Orders].[Revenue Status].&[Invoiced]" meta [DisplayName = "Invoiced"]) and (Cube.AttributeMemberId([Fact_MandS_Orders.Order Type.1]) = "[Fact_MandS_Orders].[Order Type].&[Sales Order]" meta [DisplayName = "Sales Order"])),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [#"Revenue (kUSD).1"] <> null and [#"Revenue (kUSD).1"] <> "")
in
#"Filtered Rows1"
