DirectQuery Connections to Power BI Datasets and Analysis Services in Composite Models

In the previous chapter, we learned when and how to use dataflows. This chapter focuses on an advanced data modeling topic, which is expanding composite models by enabling and using DirectQuery connections to Power BI datasets, on-premises SQL Server Analysis Services (SSAS) tabular models, and Azure Analysis Services.

In the previous chapters, we learned about various aspects of data modeling through many different scenarios. This chapter briefly introduces more options available in the Power BI platform that can be handy in real-world scenarios. Each topic discussed in this chapter has extensive technical details, making it virtually impossible to cover in a single chapter. But it is worthwhile having exposure to them. This chapter covers the following areas:

• Introduction to composite models
• Enabling DirectQuery for live connections
• New terminologies

Introduction to composite models

In Chapter 4, Getting Data from Various Sources, we discussed different storage modes for a dataset. The following is a quick refresher on the datasets’ storage modes:

• Import: For when we keep the data model in Power BI, and the whole data is cached in the memory. In this mode, all tables are in Import storage mode.
• DirectQuery: For when we create a data model in Power BI, but the data is NOT cached in the memory. In this mode, all tables are in DirectQuery storage mode.
• Connect Live: A specific type of DirectQuery, connecting to a semantic model, not a relational database (data store). When we use Connect Live, the data model is hosted elsewhere; therefore, we cannot make any changes to the data model. Instead, we can only get data ready from the data model.
• Composite (Mixed): When a portion of data is cached in the memory while the rest is not. In this mode, some tables are in Import storage mode, and some tables...

Enabling DirectQuery for live connections

To be able to use this feature, we have to have it enabled in the Power BI service and Power BI Desktop. The next subsections explain how to do so.

Allow DirectQuery connections to Power BI datasets in the Power BI service

If you are a Power BI administrator, you need to enable Allow DirectQuery connections to Power BI datasets in the admin portal from the Power BI service. If you are not an admin, then you may ask your Power BI administrators to enable it for you. Either way, the following steps show how to enable this feature in the Power BI service:

1. Click the gear button () located at the top right in the Power BI service.
2. Click Admin portal.
3. Expand the Allow DirectQuery connections to Power BI datasets setting under the Export and sharing settings section.
4. You may enable this feature for The entire organization or just Specific security groups depending on your organizational data governance policies...

New terminologies

The new generation of composite models comes with new terminologies. Understanding these terms helps us to resolve more complex scenarios more efficiently and with fewer issues. In the following few subsections, we will learn about these new terms.

Chaining

Chaining is a new terminology introduced with the new composite model. When a Power BI report or dataset is based on some other semantic model hosted in SSAS tabular, AAS, or Power BI datasets, we create a chain; in other words, chaining is about the dependencies between semantic layers used in composite models. So, when we create a dataset on top of other datasets, the new dataset depends on a series of other datasets.

Chain length

When we create a chain, its length refers to the number of semantic layers the current dataset depends on. Let’s implement a scenario to understand these terminologies better.

The business has a semantic model hosted in an AAS dataset that is still under...

RLS in composite models with DirectQuery to Power BI datasets

Now that we have a better understanding of how composite models work with a DirectQuery connection to Power BI datasets, SSAS tabular, or AAS models, it is time to take this another step further and discuss more technical scenarios that include RLS. We’ll use the AdventureWorksDW2019 database for this scenario.

The business has an Internet Sales dataset published to the Power BI service. The following image shows the Internet Sales data model:

Figure 14.13: Internet Sales data model

Note the one-to-many relationship between the DimSalesTerritory and DimEmployee tables. The developer created a dynamic RLS so each salesperson can see only their relevant territory’s sales values. To ensure the DimEmployee table always shows the data that is relevant to the report users, we have to use the following DAX expression to create a rule on the DimEmployee table:

[EmailAddress] = USERPRINCIPALNAME...

Setting dataset permissions for contributors (report writers)

As we all know, the implemented RLS on a dataset kicks in only for users who access the report via a shared link, have read-only direct access to the report, or have a Viewer role on the workspace. The report writers should have Build permission on the referenced dataset(s) to connect to the datasets in Power BI Desktop to create thin reports. A thin report is a report we build upon an existing dataset. In other words, thin reports do not have data models but a data visualization only. Follow these steps to grant Build permission to contributors after navigating to a desired workspace:

1. Hover over the first chain dataset and click the more options ellipsis button.
2. Click the Manage permissions option.
3. Hover over a user account or security group and click the More options ellipsis button.
4. Click Add build.

The following image shows the preceding steps:

Figure 14.21: Grant build...

Summary

In this chapter, we learned about new terminologies for composite models in Power BI and how to enable DirectQuery connections to Power BI datasets on Power BI Desktop and the Power BI service, and implemented simple but pragmatic scenarios to build solid foundations for future implementations and more complex scenarios. In the next chapter, we will look at other new features and capabilities for data modeling in Power BI.

Join us on Discord!

Join The Big Data and Analytics Community on the Packt Discord Server!

Hang out with 558 other members and enjoy free voice and text chat.

https://packt.link/ips2H