DirectQuery for Power BI Datasets and Azure Analysis Services

When the Power BI composite model was first introduced in 2018, it allowed for an expanded range of capabilities when connecting reports to data sources. The feature enabled several ways to add both DirectQuery connections and imported data to a model. Developers could combine DirectQuery connections with imported data or even utilize multiple DirectQuery connections. These options provided many ways to establish and customize relationships between tables from different sources. With these new relationships, visuals could be created to reflect data spanning multiple sources.

While the combination of only DirectQuery and imported data sources were beneficial for extending data models, business users still faced limitations such as the inability to incorporate and combine live connection data sources with existing models.

New DirectQuery for Power BI Datasets and Azure Analysis Services

The long-awaited update to the composite model recently became available in public preview in December 2020. This update offers greater capabilities when connecting to different data source groups. Developers are now able to use DirectQuery to establish composite models against live connection data sources such as Power BI Datasets and Azure Analysis Services. This allows for data models inside of Power BI to include connections to remote models in addition to other DirectQuery and imported data sources.

Let’s say you wanted to extend and add personalization to live connected data. In this case, a local model is required. When adding a local model, the live connection would need to be converted to DirectQuery. Once the local model is created, you now have access to add different data source groups and establish relationships within them.

Another benefit of the new upgrade is that users now have the ability to add their own data to enterprise reporting models. In most cases, a business user does not have access to modify or combine their organization’s enterprise models with other local data sources. However, establishing a DirectQuery connection and adding a local model now make this possible. This is extremely beneficial for members of organizations because reporting can now be enhanced and scaled through the incorporation of one’s own data into a single source of truth enterprise model.

Establishing a Live Connection

To outline the full capabilities of the Power BI composite model upgrade, start by establishing a live connection to Azure Analysis Services which will serve as a semantic layer for an Azure Synapse Analytics data warehouse solution. In this example, the data warehouse is an enterprise solution that acts as a single source of truth for different business areas throughout an organization.

1. In Power BI Desktop, select Get Data > Azure > Azure Analysis Services database.
2. Click Connect.
3. Enter the server and database name.
4. Select the Connect Live connection option.
5. Click Ok.
6. In the Navigator, select the desired components to import.
7. Click Load.

Importing Excel Data Source

To expand upon this enterprise model, we can import an additional data source in the form of an Excel file. This file contains additional product information that the user wants to add to enhance the current enterprise model.

1. Select Excel from the Data tab.
2. A notification will appear prompting the user to add a local model to the Power BI report.

Note: In order to add data sources or make additional changes to the existing remote model, the file must convert from using a live connection to DirectQuery. In order to establish this DirectQuery connection, a local model must be added.

1. Click Add a local model.
2. In the File Explorer, select an Excel file to import.
3. In the Navigator, select the desired components to import.
4. Click Load.

In the Model View, you are now able to see the Enterprise model with a DirectQuery connection in blue headers as well as see the imported table that we have established a relationship with.

Functionality Between Sources

Once you have an extended model consisting of multiple data source groups, relationships can be established. These relationships are what allow you to seamlessly create measures, columns, and visuals to represent your data between different data sources. The example currently consists of a DirectQuery connection to Azure Analysis Services and an imported Excel file dataset. With the composite model, you would follow the same steps to create a variety of relationships between your sources and start building your Power BI report.

The new upgrade to the composite model is a major step forward in the process of connecting to data sources in Power BI. It offers an enhancement to self-service analytics by enabling users to extend and add customization to their model through different ways of connecting to data. As functionality updates to the feature continue to be made, it will be fascinating to experience the future of composite models and their impact on enterprise reporting and data analytics.

Ryan Napper