Extend your Power BI Reports with Analysis Services 2022 Data

By:   |   Updated: 2023-01-18   |   Comments   |   Related: > Power BI


Problem

I'm building a Power BI report using data from our data warehouse. We need to combine our data with data from another data domain for one particular analysis. This data is stored inside an Analysis Services 2022 Tabular model. Is it possible to show it on the report?

Solution

When connecting to earlier Analysis Services Tabular models (SQL Server 2019 and lower) from Power BI Desktop (PBI), you had two choices to retrieve the data:

  • Import Mode: Import the data into the model of PBI itself. However, let's assume we can't use this because the dataset is too big, or maybe you don't have clearance to make an actual copy of the data.
  • Live Connection: The data stays at the source. Queries are sent from PBI to the Tabular model, and only the results are transferred back. However, you can only query the Tabular model with a live connection. You cannot combine the data with other sources.

The solution is to use a composite model. This is a model in PBI where we combine data from import sources with DirectQuery sources. With the release of Analysis Services 2022 (SSAS), a Tabular model can now be used as a DirectQuery source in a composite model.

This tip will walk you through the steps of combining SSAS 2022 with other data in PBI Desktop.

How to Create a Composite Model with SSAS 2022

Create an SSAS Tabular 2022 Model

The first step is to install SSAS 2022. Next, you must install the SSAS extension in your Visual Studio (VS) instance. Then you can create a new project in VS, "Analysis Services Tabular project," as seen in the following image.

create new ssas tabular project

Give the new project a name and choose a location to save it. Click Create:

configure new project

A pop-up will appear, asking you to set the compatibility level and choose where you want to host the workspace. For this example, we need the 1600 compatibility level. Click OK.

set compatibility level

Let's connect to some data. In this example, we will use the Adventure Works DW sample database. Right-click on Data Sources and select Import From Data Source….

import from data source

Choose SQL Server database as the data source.

sql server data source

Enter the name of your SQL Server instance. Click OK.

select server

Configure how you want SSAS to connect to the database server. You can choose between Windows credentials, SQL Server users (the database option), or a Microsoft account (where you need to log in to Active Directory). Click Connect.

configure authentication

In the navigator, choose the AdventureWorksDW2019 database.

choose database

From the list, choose the following tables:

  • DimCustomer
  • DimDate
  • DimProduct
  • DimProductCategory
  • DimProductSubcategory
  • FactInternetSales
choose tables to import

Visual Studio will create the connection and import the data from the tables into your workspace.

processing tables in import VS

Once the tables are imported, you can create relationships between them if the designer hasn't done this already for you.

tabular model with relationships

Right-click the model name in the object browser, and click on Deploy. Depending on your settings, this will deploy the model to the server and optionally process the data.

Deploy

If the data hasn't been processed during deployment, log into the server using SQL Server Management Studio (SSMS) and process the database:

process database

Create a Composite Model in Power BI Desktop

Now that we have a model in SSAS Tabular, we need to create another in Power BI Desktop. Select SQL Server as the source, specify the server name, and choose Import as the connectivity mode. Click OK.

import from SQL Server in PBI Desktop

In the Navigator, choose the following tables:

  • DimDate
  • DimProduct
  • DimProductSubcategory
  • DimProductCategory
  • DimReseller
  • FactResellerSales
select tables to import

In Power Query Editor, remove unnecessary columns (typically table relationships that were added due to the foreign keys present in the fact table).

remove unnecessary columns

When the tables are imported into the model, make sure the following relationships are created (most of them should've been added automatically):

STAR SCHEMA ALL THE THINGS

Now we can add tables from our SSAS Tabular 2022 model into the Power BI Desktop model. But, instead of simply importing the data, we're adding them as DirectQuery tables. This means the data stays in the SSAS 2022 database and is not transferred to the PBI model. Click on Get Data in the ribbon and choose Analysis Services as the data source.

get data from SSAS

Specify the server, the database we created in the previous section, and DirectQuery as the connectivity mode. Click OK.

connect to ssas 2022 with directquery

Select the DimCustomer and the FactInternetSales tables from the tree view and click Submit.

select the table for which you want directquery in the model

You might get a security warning like the one below. This is because when you define a model with a DirectQuery source, it's possible that data from the import source gets sent to the SSAS 2022 model. For example, the model has the dimension DimProduct in common (here, it's imported into the PBI Desktop model). Suppose we create a filter on a particular product and ask for data from the FactInternSales table (which is in DirectQuery mode). This means the specific product code can be sent along with the query to SSAS 2022, so the results can be filtered.

security warning

Here is the final model:

composite model in PBI Desktop

You can see two different connectivity modes because the icons left of the table name are different between some tables. In the bottom right corner of PBI Desktop, you can also see the storage mode is mixed:

storage mode mixed

Now it's possible to create visualizations where we combine data from the PBI Desktop model with data from the SSAS 2022 model:

create one visualization with data from two different models

Conclusion

With the release of SQL Server 2022, we can add Analysis Services 2022 Tabular models as a DirectQuery source to our Power BI Desktop models giving us much more flexibility when designing Power BI reports. For example, in Data Mesh architectures, where other teams manage certain data products, it becomes easier to use their data in your reports without taking an actual copy of their data.

Next Steps





get scripts

next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips


Article Last Updated: 2023-01-18

Comments For This Article

















get free sql tips
agree to terms