How to Run DevOps CI/CD Pipelines for Paginated Reports That Use a VNet Gateway

This is a short guide on what you need to know to run DevOps pipelines on Power BI paginated reports that use a VNet gateway. This is written as of Aug 2022 while the VNet gateway feature is still in public preview. Hopefully, as this offering matures, we will see more options to make this work.

What Does Our Pipeline Do?

This guide does not include how to configure your pipeline, this guide is aimed at users trying to run their existing CI/CD pipelines for paginated Power BI reports that use a VNet gateway. The pipeline we are running does the following--deploys the target report to a development workspace, next it modifies the data source target and moves a copy in to a QA workspace. Eventually it will be moved to production.

What you need to know.

As of writing this, you cannot use an Azure Service Principal in your DevOps pipeline configuration, you must use a user principal that has MFA disabled. An Azure Service Principal cannot modify the embedded data source configuration of paginated reports that use a VNet gateway. The issue is that you cannot grant data source access to an Azure Service Principal at this time, only user principals. I imagine Microsoft will need to address this eventually.

Configuration Steps

Create a new user principal in AD to use for your CI/CD pipelines, we will need to permission this account in several places in Power BI.

Once you have the new account, log in to the Power BI service with an admin account and assign the new user principal to the VNet gateway and data sources used in your CI/CD pipeline.

First navigate to the VNet gateway - Options>Manage Gateways>Virtual network data gateways. Find the target gateway and click More actions>Manage users. Add the new user principal you created:

Next we are going to permission the new user principal to the target data sources. On that same page there is a tab for Data sources. We are going to assume you have already created a data source for each of your pipeline environments (dev, qa, prod, etc). We need to add our new user principal to each of these data sources. On the data source navigate to More actions>Manage users. Add the new user principal.

In Azure DevOps make sure the service connection is configured to use the new user principal we created. Go to DevOps and your target project. Click Project setting, under project settings look under the Pipeline group and click the Service connections option. You will see your service connections listed here.

Either modify the existing service connection that is using the Azure Service Principal or else create a new service connection. Either way we need to use the new user principal we created. Click on the target service connection and in the top right corner click Edit.

You can see there are two types of authentication methods here, one is the user principal and the other is the service principal. We want to select the username type and then complete the rest of the configuration as seen in the screenshot above.

Save the changes and you should be ready to run your pipeline. Double check all settings if you run in to errors. This has been validated as of Aug 2022.

Configure Paginated Power BI Reports to use a VNet Gateway

Part I - Data Source and the VNet Gateway

What is a VNet data gateway? According to Microsoft:

The virtual network (VNet) data gateway helps you to connect from Microsoft Cloud services to your Azure data services within a VNet without the need of an on-premises data gateway. The VNet data gateway securely communicates with the data source, executes queries, and transmits results back to the service.

The VNet gateway went into public preview in Aug 2022. For organizations that store data in the cloud (Azure data services) and want to expose that data to other cloud services (Power BI) without a public IP, they can do that now with a VNet gateway. In this example we are connecting Power BI reports in the service back to an Azure SQL Database via the VNet gateway.

Chances are the person reading this document will not be the person setting up the VNet gateway in Azure, to get with your systems and networking teams to get the VNet gateway setup before you do anything else.

Power BI Setup

Make sure you are a Power BI admin in the tenant. Log in to app.powerbi.com with your admin account. Click the gear in the topmost right corner, up around your account avatar, and click on Manage Gateways:

You should see three options on that page, Data Sources, On-premises data gateways, and Virtual network data gateways. We want to select the Virtual network data gateways. This is the VNet gateway setup by your cloud administrators. This is what will allow us to connect our Power BI datasets to our data sources in our Azure SQL database.

If you don't see anything in the virtual gateway list, make sure you turn on the option in the top right corner, it’s a toggle named Tenant administration for gateways:

If your admins configured the VNet gateway(s) and you have the correct permissions, you should now see your VNet gateway(s) listed here. Now we are going to navigate back to the list of data sources so we can add a new one that will use our gateway.

At the very top of the screen directly about the Data sources option, click on "New" to add a new data source. That will bring up the following window on the right of your screen:

Click the dropdown for the Gateway cluster name option, you should see a list of all available VNet gateways. Select the target VNet gateway--once your select a gateway a new option will appear in the window named Data source type. Give your data source a name and then select the correct data source type from the drop-down list. We are selecting SQL Server for our Azure SQL database.

Once you have selected the type, you will now see new fields to configure the connection. Add your server connection string, the same thing you use to connect to SQL from SSMS. We are using the basic authentication method; this will allow us to use a SQL Server account we have configured in our instance. It is preferred to use OAuth2. Once you have finished setting up the data source you should now see it listed on the page. Before you can use it, you need to assign permissions to the data source. Hover over the data source name and select Manage users.

Typically, you would want to setup a security group with all users who need access to this data source. Once you add the users you are now ready to use the data source. Next, we need to link this up to the paginated report.

Part II - Configure Paginated Reports to Use Data Source on VNet Gateway

Paginated reports (rdl) are a little different than your regular Power BI reports (pbix) in that paginated reports don't have a separate dataset; the dataset and the report are a single asset in Power BI.

Once you have published your paginated report from Power BI Report Builder app, go to the target workspace and find the report you published. On your report, click the three vertical dots for more options and then select Manage. This will take you to a screen where you can configure your data source via the gateway.

There is an implicit relationship detected by Power BI. It looks at the data source you configured in your report and will match that up with a gateway that uses that data source underlying connection. So if you setup you report using a dataset that you have not yet configured a data source for, it might give you problems.

Select the option to Use an On-premises or VNet data gateway. Expand the target gateway if you haven't already and under the Maps to option, select the data source we created in the part I of this guide. It should look something like this:

Once you map the data source you should now be able to run the report.

How to Implement Composite Security in Power BI - RLS for Paginated Report Visualizations

There are two types of reports in Power BI, the standard .pbix type which is native to the Power BI desktop and service, and then you have the paginated report type or .rdl, this type of report can only be created in Power BI Report Builder, a stand-alone tool for the desktop. You might recognize the .rdl extension from SSRS and that is because the Power BI Report Builder is just a lightweight version of SSRS. As great as Power BI is, we still have the need for paginated reports--pixel perfect reports intended for printing, rich data export options, or filtered, near real-time analysis of operational data. Microsoft continues to make moves to consolidate the SSRS features in to Power BI, but it is still a work in progress (as of 2023.02.03).

Microsoft introduced the paginated report visualization to Power BI in 2021 and it has been receiving more attention as of late, but the paginated integration with Power BI still has some glaring limitations and issues we assume will be worked out in the coming months. For a paginated report built on a Power BI dataset, it's not possible to call that paginated report from another Power BI report and pass down filters from the Power BI report to the paginated report. Well you can pass them down but they don't actually filter the data as you would expect. When you pass down filters to a paginated report that is built on a Power BI dataset it returns all data from the underlying dataset and then applies the filter. For very large datasets that just won't work.

 Problem Statement

Our report consumers want to be able to export data in different formats without some of the inherent limitations of exporting a single visualization in Power BI. Because our source data is so large, we need to be able to pass in filters to limit the amount of data we are returning to the client. We also want to preserve the Power BI user experience our report consumers are accustomed to with features like searchable parameters and a more presentable user interface.

The solution we present here is not ideal, but rather a workaround until Microsoft can address some of these issues.  You will see that even with the workaround, there are yet additional limitations to that as well, but this does solve a lot of our problems for now.

Paginated Reports in Power BI Desktop

Before we can call a paginated report using the paginated report visualization in Power BI, we first need to create a parameterized paginated report. We assume you all know how to do that already so we won't get in to the details of that. Once we have built the paginated report, we are now ready to call it from Power BI.

Open Power BI desktop and create a new report. Since the target paginated report we want to call accepts parameters, we first need to make sure we have a dataset defined in the Power BI report, from that dataset we will pull the filter parameters to pass down to the paginated report.

Because of the way Power BI processes filters, the filters you want to send to the paginated report, your filters must be related. This can present some challenges depending on how many filters your target report has since the greater number of filters means a larger cartesian product. Let me explain.

If the paginated report accepts 3 parameters - merchant, start date, and end date, then we need to pass those from the Power BI report and dataset. The same way we used to create datasets in SSRS with values that would populate a parameter in the report, we are doing the same thing here. First we create a table from a SQL query for all merchants and then we create a second table from SQL query for dates. We don't use a full dates table here because of the cartesian product we mentioned earlier (we will get in to the details of that next). Instead of a full range of dates we create the following:

SELECT 
   CONCAT(SUBSTRING(DATENAME(mm, MIN(CalendarDate)), 1, 3), '-', DATEPART(yy,MIN(CalendarDate))) AS DateLabel
  ,MIN(CalendarDate) AS StartDate 
  ,MAX(CalendarDate) AS EndDate
  ,ROW_NUMBER() OVER (ORDER BY MIN(CalendarDate) ASC) + 3 AS SortOrder -- plus the number of relative date attributes
  ,1 AS CommonKey
FROM dbo.DateDim
WHERE CalendarDate > DATEADD(DAY, -365, CAST(GETDATE() AS date)) AND CalendarDate <= CAST(GETDATE() AS date)
GROUP BY YearMonthName

UNION

SELECT 
  'Yesterday' AS DateLabel
  ,CAST(DATEADD(DAY, -1, GETDATE()) AS date) AS StartDate
  ,CAST(DATEADD(DAY, -1, GETDATE()) AS date) AS EndDate
  ,0 AS SortOrder
  ,1 AS CommonKey

UNION

SELECT 
  'Today' AS DateLabel
  ,CAST(GETDATE() AS date) AS StartDate
  ,CAST(GETDATE() AS date) AS EndDate
  ,1 AS SortOrder
  ,1 AS CommonKey

UNION

SELECT 
   'Last 7 Days' AS DateLabel
   ,CAST(DATEADD(DAY, -7, GETDATE()) AS date) AS StartDate
   ,CAST(GETDATE() AS date) AS EndDate
   ,2 AS SortOrder
   ,1 AS CommonKey

UNION

SELECT 
    'Last 30 Days' AS DateLabel
   ,CAST(DATEADD(DAY, -30, GETDATE()) AS date) AS StartDate
   ,CAST(GETDATE() AS date) AS EndDate
   ,3 AS SortOrder
   ,1 AS CommonKey
ORDER BY SortOrder, StartDate

This provides a good number of relative date slices representing the most common ways the data is interacted with. Now on to how we relate all this and why the number of records in each table matters.

Each table needs to be related in order for us to pass values from the different table down to the paginated report as input parameters. Because there is no relationship between merchants and dates, we will need to manufacture one with a common key. So in addition to the merchant and dates table we are also going to create a common key table. This common key or bridge table allows us to relate the otherwise unrelated tables. Create a common key attribute in each of your three tables and then create relationships between each of the three tables on that key. Because of the way these need to relate, you will need filtering in both directions and depending on how many parameters you have, many to many relationships even if there is only a single record in the bridge table. This is what we were referring to with the cartesian product, its why we want to limit the number of rows in a table used for parameter filtering.

Once you have set this up your report model will look something like this:

The way we setup this up is a requirement to be able to connect your parameters to the target paginated report. If you do not do this correctly you will receive errors when you try to define your filter parameters for the paginated report. 

Go back to your report view in your Power BI report and drag a few slicers to the report page. One slicer will list all merchants and the other will list the user friendly date labels you defined in your dates table. While you might think this is what gets passed to the paginated report its not actually. This is just filtering the data for the underlying parameter values we will map in the next step.

When working in Power BI desktop and you add the paginated report visualization to the canvas you will be presented with the following message:

Before we can connect to the target report we first need to define the report parameters in the Power BI report. When you have the paginated report visualization highlighted you will now see a parameters group in your Visualization tree where we can select the values we want to pass in as parameters to the paginated report. We have added our merchant number and start and end dates.

Once our parameters are defined make sure you are logged in to Power BI with a user account that has access to your organization's Power BI tenant. We are now ready to click the Connect to Report button that appears when we pulled the paginated report visualization on to the canvas. Click the connect to report button and we will be directed to a list of all paginated reports we have access to in our organization. This assumes we already have paginated reports deployed to our Power BI service, which we do, if not you'll want to create and publish one first. When you select a report from the target workspace we will then see an option to set the report parameters.

What is happening here is Power BI wants to know what values in the Power BI desktop report you want to pass in as parameters defined in the paginated report. We already defined our Power BI report parameters in a previous step, now we are ready to tie it all together. In the following image you can see we mapped the target parameter to the parameters we created in the Power BI report. If you set this up correctly you should be able to click the See Report button and the paginated report will be returned in the paginated visualization window.

Row Level Security (RLS)

 One of the nice features of using this pattern is we can apply RLS to the merchant table the same way we would on any other table in a Power BI report model. We can might use the USERNAME() or CUSTOMDATA() functions to filter the merchant table down to just what the report user needs to see. We have a security modifier in our merchant table query we can use to set the correct filter context based on the current user. The difficult part of building this pattern has less to do with the RLS piece and more to do with the constraints around pass through filtering for a paginated report visualization.

 In addition to RLS security the paginated report can have another layer of security baked in to the code for its underlying datasets, the same way you would have set it up for SSRS reports in the past. In SSRS there is a built-in field for the current user ([&UserID]) and you can use this to filter your report by joining to a dataset in your database that has permissions defined for the different users, a common pattern for this type of report.

 So between RLS in your Power BI calling report, data filtering on UserId in your paginated report, you can be sure that users only have access to the data they are supposed to see.

How to Prevent the Deletion of Datasets in Power BI

Note - This guide is being published in Oct. 2020. This month Microsoft began rolling out the "New Look" to the Power BI service. We have enabled the new look and the features underlying so any screenshots in our guide are based on the new look. Also to note, we are using SSAS tabular with a data gateway and a live connection from the Power BI service.

Update May 2023 - As of May 2023 the protected dataset is still the best approach to prevent the deletion of datasets (and associated reports) in your Power BI tenant. Store your datasets in a workspace that only admins have access to. Keep all master reports in your GIT repository. Share the protected dataset from the admin workspace to user groups in your organization. Hard to believe this is still an issue, but it is.

Overview

Datasets are what we use to build reports in the Power BI service. A dataset will reference a data source that has been configured in the Power BI service. We are using SSAS Tabular to build models that can be consumed by Power BI, we publish these models and then access them through a data gateway. We create the data sources on the data gateways in the Power BI Service and then link the data sets to the data sources.

All reports you build in the Power BI service are going to reference a dataset. If a single dataset has 100 reports built off of that data and somebody comes in an accidentally deletes that dataset, all the reports that are tied to that dataset are also deleted. Power BI is a self-service product and you want your teams to be able to build their own reports. In order for teams of a Power BI workspace to build their own reports you need to grant those users contributor permissions on their workspace. This permission also allows those users the ability to delete datasets and that is where you can run in to trouble.

For some reason Microsoft has not done a great job with options to protect content in Power BI. There is no concept as a recycle bin in the Power BI service, when it's gone it's gone. The security roles for workspaces are too broad, the main roles are admin, contributor, and viewer (we'll get more in to this in a minute) and most users will be in the contributor role if you want them to create their own reports (we do). The contributor role is over-permissioned unfortunately so we have to take extra steps to protect our content in Power BI.

That is the purpose of this guide, to you walk you through steps on how to protect content in the Power BI service and reduce the risk of a dataset (and all of the content that depends on it) from being deleted while at the same time enabling your teams to use self-service reporting and workspace management.

Dataset Basics

• Datasets depend on data sources. We can define a data source on a data gateway but to create a dataset, we first need to create a report in the Power Bi desktop. In our environment our Power BI desktop report will use a live connection to an SSAS Tabular database/model. When we publish this report to the Power BI service it will create a dataset. We link the dataset to the data source.

• When you delete a dataset in a workspace, all reports that are built from that dataset in that same workspace will be deleted along with it. If a report was created in another workspace that referenced the deleted dataset, that report will become orphaned and no longer work.

• Datasets are based on GUIDs and not names. If you delete a dataset and try to republish it again with the same name, as far as Power BI is concerned that is a new dataset and no existing content can be linked to it. Since datasets are based on GUIDs and not names, it is possible to have more than one dataset with the same name so this could cause some confusion in a workspace.

• A dataset inherits permissions from the workspace it resides in, but it can also be granted additional permissions beyond the workspace permissions by modifying the dataset permissions (we will explain this below).

Workspace Configuration

In order to protect our Power BI datasets we need to store them in a workspace that only your BI developers have access to. From there we will grant build permissions to users on the datasets we publish in this workspace but they won't have access to delete that dataset. Using this along with version control (DevOps, BitBucket, etc.) is the best way to secure and protect content in your environment.

Note - what we are doing here assumes you and your users are using the Power BI environment in the Professional or Premium capacity. You will need this type of licensing to share content across the organization.

In the Power BI Service, create a new workspace named BI Admin. For the members, start by granting admin rights to your BI Developer admin(s). These are the people who will publish, modify, and maintain all the content in this workspace. In the screen shot below you can see we created the new workspace and permissioned myself as admin to the workspace, but there is no content in the workspace yet. While we are at we are also going to add a single user who only has view permissions in the workspace. This is the user we will delegate build permissions to on the dataset.

Publish Dataset

Now we are ready to publish our first dataset to the secure workspace.  Remember, we are using an SSAS tabular model with a live connection, your environment might be different but the securing process is still the same.

Open Power BI desktop and make a connection to your data source, for us that is an SSAS tabular model.

When you connect to the SSAS tabular model your will see all the tables in the model populate in the report.

On the report canvas there is no need to create a visualization, what you should do instead is to make a note about the report and the dataset, something that tells you what it is and what team it is for. For example, if this dataset is for the "Product Team" I might add something like you see in the screen shot below.

When you publish your first report for a particular dataset to the Power BI service it will also publish the dataset that report is connected to. In the case of our report here it is connected to the SSAS tabular model for the Adventure Works database. It's important to keep in mind that the dataset in the Power BI service will have the same name as the report it references. You do not want to rename the dataset after it is published, instead give your template report a name you want reflected in the dataset, something like "Product Support Dataset".

Now we are ready to publish this to the Power BI service. Click on the Publish button on the home tab, select the target workspace, in this case the BI Admin workspace, and click the Select button.

You will see the following message when the report and dataset have been published.

Now let's open up the Power BI workspace we created earlier, you should now see your report and your dataset.

I understand the naming around the report doesn't make much sense, but we don't want to rename the dataset after it is published so we give the report a name that best suits the dataset. After we have published the dataset next we need to link it to a data source. Click on the Manage Gateway option in the settings gear. When you are in the Gateway settings, click Add a Data Source and add the model of the SSAS tabular model your dataset is based on. You will need all the details of the SSAS server you are connecting to, this also assumes you already have the data gateway up and running.

Once the data source has been added we can go back to settings of the dataset. Expand the Gateway connection section and for the target database, select the data source you just added in the previous step. As you can see in the image below, we named the target data source AdventureWorks.

If you recall earlier, we setup a user on the workspace that has only view permissions. That user is a member of the Product Support team and wants to use the new dataset we just published to build reports from. We want to make sure the user can't delete the underlying dataset thus deleting any reports tied to that dataset. So what we are going to do is give the user build permissions on the dataset directly, this will allow them to build reports off of the dataset but will not let them modify the dataset. If this user was a contributor to the workspace they would be able to delete the dataset, that is what we are trying to prevent.

By default the dataset will inherit permissions from the workspace, but we can grant additional permissions on the dataset. Click on the Manage Permissions option of the dataset.

Now we can grant the build permission to our view only user, this will allow that user to select this dataset and build reports from it while working in their own workspace. It's basically sharing the dataset without elevating the users rights to the dataset beyond exactly what they need.

If our user Kevin wants to build a report from this new dataset, in Kevin's team workspace he can click New>Report. From there he can view all datasets he is permissioned to and can select the Product Support dataset we just published and granted build permissions on.

Now we need to select the Product Support dataset and click create. This will effectively create a linked reference to the dataset allowing the user to create a new report in their workspace.

When they save the report they won't even see the dataset in their workspace much less be able to delete the master copy of it. This is how we ensure that our users have permissions to create and modify their own content without running the risk of errantly deleting a dataset that then wipes out reports for the rest of the team.

Also, now when we want to add changes to the underlying data model in SSAS all we have to do is update the model, refresh the report in Power BI desktop, and republish the report back to the Power BI service. This will preserve all connections from reports to the dataset while also giving those reports access to the new assets published to the SSAS model.

Microsoft Power BI Report Server Licensing - On-Prem Power BI

Wanted to make a quick post about the licensing model for Power BI on-prem, how it works with SSRS Report Server, and a few other details you might want to know:

• If you currently have a SQL Enterprise license with software assurance and a minimum of 4 cores, you are entitled to the on-prem Power BI Report Server at no additional cost.
• If you do not have a SQL Enterprise license with SA you can opt to purchase the Power BI Server standalone license for $5000 a month. I think it is cheaper to buy an enterprise license with SA.
• The Microsoft PowerBI on-prem solution is called Power BI Report Server. This can be confusing because we already have SSRS Report Server which is often referred to as "Report Server". The Power BI people at Microsoft are also calling the Power BI Report Server "Report Server"...so much for clarity.
• When you install Power BI Report Server, it's the same as SSRS Report Server with the added support of publishing Power BI reports to it. When you install a new instance of Power BI Report Server you will want backup and restore your existing SSRS Report Server databases to the Power BI Report Server. This will preserve all SSRS reporting while adding in the option to publish and consume Power BI reports from "Report Manager".
• The current version of Power BI Report Server only works with SSAS, it has no support for other data sources at this time.
• There is no dashboard support in the initial release of Power BI Report Server..