The course is part of this learning path
Typically the life cycle of a Power BI dataset doesn't end with publishing. This course looks at managing Power BI datasets after they have been deployed. More often than not, the source data changes regularly, so we look at updating functionality available within the Power BI service and other cloud-based methods for keeping your data fresh. Not all source data is cloud-based and readily accessible to the Power BI service, so we'll see how to use data gateways to access on-premises data, which ironically also includes data residing on cloud-hosted virtual machines.
Sometimes you'll need to grant access to datasets beyond just viewing the data. We see how you can permit users and user groups to repurpose a dataset for reports they publish. Row-level security groups are a good way to partition data for different audiences, but assigning domain users to each group can be tedious. You'll see how to assign users to row-level security groups by leveraging Office 365 user group membership. The course finishes by going through the global options in Power BI Desktop to see how to customize the user experience and improve performance.
Learning Objectives
- Learn how to implement a data gateway to access on-premises data sources
- Learn to map Office 365 user groups to Power BI row-level security groups
- Permit other report designers to use a deployed dataset for their own purposes
- Understand the different options for keeping deployed data fresh
- Learn about the global file options in Power BI Desktop
Intended Audience
This course is designed for anyone who wants to learn how to manage their Power BI datasets after they have been deployed.
Prerequisites
To get the most out of this course, you should be comfortable using Power BI desktop and have some knowledge of publishing reports to PowerBI.com
Power BI uses gateways to access on-premises data sources when a published dataset needs refreshing. In this context, on-premises applies to the usual suspects such as locally hosted databases and files, but also data sources on virtual machines, which are in turn hosted in the cloud. Latency is an important consideration when deploying a gateway, and it's preferable to place the gateway as close as possible to the data source. While you could put the gateway on your database server, although best practice says not to, nothing can be done about the distance between your server's physical location and the data center hosting Power BI. If your data sources are on virtual machines, and you have the freedom to move them, you can relocate VMs to the same Azure region as your Power BI host to reduce latency.
Let's go through setting up a gateway for a database and file data sources. Here I have a very simple imported data model comprised of one table, SalesAndMargin from an on-premises SQL Server database located on a remote machine, and an Excel spreadsheet called branches on this PC. There is a many to 1 relationship between SalesAndMargin branch and branches ID. The associated report contains a couple of card and bar chart visualizations with a drop-down year filter. I'll publish this dataset and report to the OnPremData workspace on Power Bi.com. In this case, OnPremData has a double meaning. It means from on-premises data, but this workspace also uses the premium license model, denoted by the diamond icon. To refresh the dataset, I will have to install a data gateway. The gateway software is currently only available for Windows machines.
At the top right, click the download arrow menu item and choose data Gateway. I've got a couple of choices here, standard or personal. As the name implies, personal mode allows only one person to use the gateway and only allows Power BI data traffic. The standard, or as it used to be known, enterprise version, allows multiple users and supports Power Apps, Microsoft Flow, Azure Logic apps traffic, and Power BI. The personal version supports imported data or scheduled refresh, while the standard gateway also supports live connection and direct query. Personal mode has no form of monitoring or control, while standard mode does allow centralized monitoring and control. I'm going to install the gateway on my local machine and not the database server, so I'll still be smiling when I put my DBA hat on. Next, I'll register my gateway using an email address associated with Power BI.com and my office 365 subscription. This is a new gateway installation, but I could migrate or repair an existing gateway at this point. I'll give the gateway an imaginative name and create a recovery key. Take note of the message regarding the recovery key. While you can change the recovery key after installation, you can't recover it; i.e., you need the existing key to set a new one. A gateway cluster is two or more gateways that act as one, providing redundancy and improving throughput, so a high availability solution. This is where you can add the new gateway to an existing cluster. Clicking configure finalizes the gateway set up. We can see the different services and apps it supports in the status tab. Service settings allow us to restart the gateway and change the account it runs under. Diagnostics enables us to set up additional logging, export the gateway logs, and perform diagnostic network port tests. Network allows us to switch between TCP and HTTPS modes. If we have any custom data connectors, we can load them under connectors, and we can change the recovery key while the existing one is still fresh in our minds.
That's the on-premises setup taken care of; now, back in the browser, we can set up the Power BI.com gateway end by selecting schedule refresh. Within the target dataset, expand the gateway connection. Here we can see an earlier personal gateway I had installed but have since deleted and our new OnPrem gateway, which needs to be configured. Clicking on the down arrow next to the settings cog reveals the two data sources included in the SalesAndMargin dataset. Both of these sources need to be mapped to the gateway. You can either click add to gateway or go up to the right settings cog and select manage gateways from the drop-down menu. There are multiple ways to add the data source on this page.
You can click add data source or use the context menu to the right of the gateway. Give the data source a name and select its type, which will be SQL Server. The server name must be the same as that in the dataset connection. This data source connection uses text matching, meaning that you can't use an IP address in one instance and the server name and another. However you referred to the server in the dataset, you must use the same method here. I'll choose authentication using an SQL login. Users is where you can specify other people that can have access to the dataset, so other publishers. You don't need to specify people who will be viewing the report, only people who have permission to modify the dataset configuration. Next, I'll add the Excel data source with the name BranchesSheet. There is no Excel type, so we use File. The path you specify here is relative to the machine where the gateway is installed. If I had put the gateway on the database server, then the file wouldn't be local to the gateway, so I would have to specify a full UNC path.
My credentials will be my Windows PC login, and I'll click add. Now that the data sources have been set up in Power BI.com, we need to go back to our gateway connection configuration and map the data sources to the dataset by selecting each source from the related drop-down. It looks like Power BI has got a little bit ahead of itself, complaining of invalid credentials, but as soon as I click Apply, that message disappears. Okay, that all seems to be in order. Let's try refreshing the dataset. Clicking refresh now begins the operation, and the last refreshed time has been updated, so let's go and check out the refresh history. Back inside the SalesAndMargin settings, clicking on refresh history shows that an on-demand refresh has successfully been completed.
Hallam is a software architect with over 20 years experience across a wide range of industries. He began his software career as a Delphi/Interbase disciple but changed his allegiance to Microsoft with its deep and broad ecosystem. While Hallam has designed and crafted custom software utilizing web, mobile and desktop technologies, good quality reliable data is the key to a successful solution. The challenge of quickly turning data into useful information for digestion by humans and machines has led Hallam to specialize in database design and process automation. Showing customers how leverage new technology to change and improve their business processes is one of the key drivers keeping Hallam coming back to the keyboard.