PowerQuery - Query SSAS Cube using List of Parameters

ManriqueM

New member
Joined
Sep 16, 2016
Messages
2
Reaction score
0
Points
0
Hi, I´m looking for help to query an SSAS cube (built in SSAS 2005) using a list of parameters, via PowerQuery. Any help on this is greatly appreciated!
·I have a Customers List (1K customer ids). This list comes from another query and will change over time.

·Also a SSAS cube (built in SSAS 2005), which has the sales records for all customers (100K+) over multiple time periods.

·I´d like to create a table that shows the monthly sales for 12 months for the 1K customers included in the list (12K records).


So far, I´ve been able to do this via 2 inefficient methods listed below. I´m looking for guidance on how to provide the 1K customers list as a parameter for the SSAS Cube and have it run the query once, only for these customers, which I suppose will be more efficient.


·
Inefficient attempt #1: Running both queries separately, the CustomersList Query (1K records) and the SSAS Sales Cube query for the 12 time periods for the 100K customers (1.2MM records). Then, merging the queries to obtain the table with 12K records for the monthly sales for the CustomersList. Code below.


·
Inefficient attempt #2: running the SSAS Sales Cube query as a function and providing each Customer from the CustomersList as a parameter. This looks to be running the query 1K times, 1 parameter at a time. Code below.


Code for Inefficient attempt #1.


let
Source = OleDb.Query("Provider=MSOLAP;Data Source=XXXX;Initial Catalog=XXXX;dbpropmsmdflattened2=true",
"SELECT
NON EMPTY { [Measures].[ Sales]} ON COLUMNS,
NON EMPTY { ([CustomerID]. ALLMEMBERS) * ( [Month].[2015/09]: [Month].[2016/08]) } ON ROWS
FROM [SALES]
in
Source
--------------------------------------------
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Merged Queries" = Table.NestedJoin(Table1,{"CustomerID"}, QuerySalesCube,{"[CustomerID]"},"NewColumn",JoinKind.LeftOuter),
#"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"[Month] ", "[Measures].[Sales]"}})
in
#"Expanded NewColumn"

Code for Inefficient attempt #2.
fnSalesCube = (CustomerList as text) =>

let

Source = OleDb.Query("Provider=MSOLAP;Data Source=XXXX;Initial Catalog=XXXX;dbpropmsmdflattened2=true",
"SELECT
NON EMPTY { [Measures].[ Sales]} ON COLUMNS,
NON EMPTY { ([“ & CustomerList & “]. ALLMEMBERS) * ( [Month].[2015/09]: [Month].[2016/08]) } ON ROWS
FROM [SALES]
in
Source
--------------------------------------
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"CustomerID", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each fnSalesCube([CustomerID]))
in
#"Added Custom"
 
Back
Top