Problem:

You are building a Power BI report using data from your data warehouse. However, you need to combine this data with data from another data domain that is stored inside an Analysis Services 2022 Tabular model. Is it possible to show this combined data on the report?

Solution:

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

• Import Mode: Import the data into the model of Power BI Desktop. However, this may not be feasible if the dataset is too big or if you don’t have clearance to make an actual copy of the data.
• Live Connection: The data stays at the source, and queries are sent from Power BI Desktop to the Tabular model. However, you can only query the Tabular model with a live connection and cannot combine the data with other sources.

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

Here are the steps to create a composite model with Analysis Services 2022 and Power BI Desktop:

1. Install Analysis Services 2022 and the SSAS extension in your Visual Studio instance.
2. Create a new project in Visual Studio called “Analysis Services Tabular project.”
3. Connect to your data source, such as the Adventure Works DW sample database.
4. Import the necessary tables into your workspace and create relationships between them.
5. Deploy the model to the server and process the data if necessary.
6. In Power BI Desktop, create a new model and connect to the SQL Server source.
7. Import the necessary tables into the Power Query Editor and remove unnecessary columns.
8. Add tables from the SSAS Tabular 2022 model as DirectQuery tables in the Power BI Desktop model.
9. Create visualizations that combine data from the Power BI Desktop model with data from the SSAS 2022 model.

By following these steps, you can leverage the capabilities of Analysis Services 2022 Tabular models as DirectQuery sources in Power BI Desktop. This allows you to easily combine data from different sources and create more flexible and powerful Power BI reports.

Conclusion:

With the release of SQL Server 2022, it is now possible to add Analysis Services 2022 Tabular models as a DirectQuery source to Power BI Desktop models. This provides much more flexibility when designing Power BI reports, especially in scenarios where you need to combine data from different sources without making actual copies of the data. By using composite models, you can easily incorporate data from Analysis Services Tabular models into your Power BI reports and gain valuable insights.

Article Last Updated: 2023-01-18