Scenario:
In the following scenario, I am going to explain about how to connect and read data from the dataset of local instance of Analysis Services of a Power BI Model.
We wants to generate a Sales Summary dataset using facts from “tbl_Sales” and dimensions (group by columns) from related tables, tbl_Calendar, tbl_Products from a model like below.
We would like to connect load the output of Sales Summary dataset in to our new Power BI Report.
In this Scenario, we will use Native Query approach to Import Data from Get Data > “SQL Server Analysis Services Database” Method.
Power BI Model:
We can generate the Sales Summary Table using the below DAX Query :
VAR vAddCols1 =
ADDCOLUMNS (
tbl_Sales,
“FY_Year”, RELATED ( tbl_Calendar[Year] ),
“QTR_Year”, RELATED ( tbl_Calendar[QTR_Year] ),
“Prod_Name”, RELATED ( tbl_Products[Prod_Name] )
)
VAR vFilterCol1 =
FILTER ( vAddCols1, [QTR_Year] IN { “Q1-2014”, “Q1-2015”, “Q1-2016” } )
VAR vSummary1 =
SUMMARIZE (
vFilterCol1,
[FY_Year],
[QTR_Year],
[Prod_Name],
“Total_Sales”, SUM ( tbl_Sales[Gross_Sales] ),
“Discount”, SUM ( tbl_Sales[Gross_Sales] ) * 0.05
)
EVALUATE
SELECTCOLUMNS (
vSummary1,
“FY_QTR”, [QTR_Year],
“Prod_Name”, [Prod_Name],
“NetSales”, [Total_Sales] – [Discount]
)
# Output:
Now we want to use the above DAX Query in our new Power BI Report to generate the same Sales Summary.
For this, we will use the Get Data > “SQL Server Analysis Services Database” Method. In this Method, we are passing the DAX Query like a native SQL Query.
We need to allow once to Run the Native Query.
let
Source = AnalysisServices.Database( “localhost:52873”, “1aa4426a-7c20-4ebe-895d-453491282c68“,
[Query=“DEFINE
VAR vAddCols1 =
ADDCOLUMNS(
tbl_Sales,
“”FY_Year””, RELATED ( tbl_Calendar[Year] ),
“”QTR_Year””, RELATED ( tbl_Calendar[QTR_Year] ),
“”Prod_Name””, RELATED ( tbl_Products[Prod_Name] )
)
VAR vFilterCol1 =
FILTER(vAddCols1, [QTR_Year] IN { “”Q1-2014″”, “”Q1-2015″”, “”Q1-2016″” } )
VAR vSummary1 =
SUMMARIZE (
vFilterCol1,
[FY_Year],
[QTR_Year],
[Prod_Name],
“”Total_Sales””, SUM ( tbl_Sales[Gross_Sales] ),
“”Discount””, SUM ( tbl_Sales[Gross_Sales] ) * 0.05
)
EVALUATE
SELECTCOLUMNS (
vSummary1,
“”FY_QTR””, [QTR_Year],
“”Prod_Name””, [Prod_Name],
“”NetSales””, [Total_Sales] – [Discount])”,
Implementation=”2.0″] )
in
Source
# ———————————————————————————————————————— #
Notes:
Please make sure to pass the extra Double Quotes around the Column Names, in case if you are writing the DAX Query directly in a Blank Query of the Power BI Advanced Editor.
In the above We have passed the Server name and Database name of the local instance of Analysis Services (Power BI Model)
Server Name = “localhost:52873”
Database Name : “1aa4426a-7c20-4ebe-895d-453491282c68”
We can find the Analysis Services local instance details from the below Folder:
C:\Users\[UserName]\Microsoft\Power BI Desktop Store App\AnalysisServicesWorkspaces\ AnalysisServicesWorkspace_eb7f2ea0-6000-4533-9f2f-339b902ba165\Data
The name of the Server will be look like:
TPREDDY-PC\AnalysisServicesWorkspace_eb7f2ea0-6000-4533-9f2f-339b902ba165
or
We can use the “localhost:[PortNumber]”
The name of the Database can be found from the XML file name, which looks like :
1aa4426a-7c20-4ebe-895d-453491282c68.0.db
The complete Analysis Service Instance name will be looks like as follows in Tabular Editor:
TPREDDY-PC\AnalysisServicesWorkspace_eb7f2ea0-6000-4533-9f2f-339b902ba165. 1aa4426a-7c20-4ebe-895d-453491282c68
Additional Notes :
Make sure, the SQL Browser Service is running in the Local System, otherwise, you may get the below error:
Enabling the SQL Browser Service:
——————————————————————————————————–
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
——————————————————————————————————–
Be the first to comment