How to read data from a Dataset of a SSAS or Power BI Model using DAX Query in the Power BI

How to connect and read data from a Dataset of a SSAS or Power BI Model using DAX Query in the Power BI

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 :

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]
)

# 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.

The Script in the  Power Query > Advanced Editor :

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

In this workspace we can find port number and other details of the instance like below:

The port number(52873, in this Scenario) can be found from a text file called "msmdsrv.port". The port number and the Workspace instance names will be randomly changing from session to session.

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

--------------------------------------------------------------------------------------------------------