Articles

Jul 24 2016

On-Premises Data Gateway – the Replacement for Power BI Enterprise and Personal Gateway

Author: Ginger Grant • Discussion: No Comments

 With the announcement July 6, Microsoft has merged the number of gateways used in Power BI. The new On-Premises Gateway not only replaces both of those applications, but also replaces the data gateway used to access on premise data for other applications, such as Azure Machine Learning. This reminds me of Lord of the Rings as There is now One Gateway to bring them all. One Gateway to bind them.

Replacing the Old Power BI Gateways with the New On-Premises Data Gateway

When installing the new gateway, the place to look for the new installer is not on some Microsoft Download page, but from within Power BI PowerBIGatewayInstallScreenitself. The new gateway needs to be installed from Power BI, rather than a generic location. As the gateway is providing data for your account, installing it in this fashion will record the gateway used on this account. To download the gateway, login to the Power BI Service and click on the <<insert icon here>> the download menu and select the Power BI Gateway. This gateway is truly the one gateway to rule them all as it contains the ability to install either the On-Premises Data Gateway, which is the direct replacement for the Enterprise Gateway, or the Power BI Personal gateway. You can install both gateways, but you have to pick one to install first. Both gateways can also run on the same device. Once the software is installed, you can convert your existing gateways to the new gateway, so there is no need to enter the configuration twice.

New Gateway, New Security Model

While the personal gateway works in a similar fashion as before bu using a single user’s credentials, the new gateway uses a different security model than was employed in the past. Instead of using the credentials of the Id which installed the software, it uses a new service account NT SERVICE\PBIEgwService. OnPremSecurityDiagramThis account is for the gateway only, it does not need to be granted any data access. The new application is creating a connection to the Azure Service Bus, acts as the clearing hours for all data requests, including Power BI and other applications occur. The application gateway is where the credentials for accessing the data are applied. This security model is used not only for Power BI, but for other applications like Azure Machine Learning where there is a need to get data from a on-premises source and load it to the cloud. Previously it was not possible to have the gateway used for Power BI to be loaded to the same machine as the other data gateway that was used for Azure Machine Learning. Now that they use the same gateway, this restriction is removed.

Functional Differences in the New Gateway and the Previous Gateways

The underlying security model and application is different, but operationally are the new gateway does not behave differently from the Enterprise and Personal Gateways Power BI, there is just a new application do to what they did. If you want to use the data access rights of a single specific user to refresh and update data, the Personal Gateway will do that. The Enterprise Model provides for the ability to inherit the security of the user accessing the data. If you have a tabular model stored locally which is used as a data source for Power BI, the users will see the data they were granted access to from within the tabular model. The scheduled update features are not changed and are still accessed from the same area as they are in Power BI Service.  Unfortunately, if you are interested in connecting to Azure Machine Learning or Data Factory, you will still need a different gateway, and that gateway cannot be installed on the same PC as a Power BI Gateway.  I will have more information on that gateway in a later post.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

 

 

Mar 11 2016

Is a Data Model Needed in Power BI?

Author: Ginger Grant • Discussion: 2 Comments

Power BI does of course need a data model, but often times there is no reason to create a new one for Power BI. After all the data model could always be contained within the data PowerBIDataModelsource Power BI is using. Figuring out if a data model is required is directly related to the data source(s) being used. In turn the selection of the data source also determines when to us Power BI online client and/or Power BI Desktop. Although they look the same, there are a couple of key differences which govern which to use.

When Should I Use the Power BI Online Client?

When comparing the features of the Online Client with the Desktop version of Power BI, there is one very obvious difference, there is no way to create a data model in Power BI online. It is not possible to create a data model using the online client. The online client is designed to connect to an existing online source such as Sales Force or Azure DB. If you are using an existing model, there is no need to create one. When using the enterprise gateway, which uses an on-premises database such as a SQL Server, SSAS or Hana, the data model is contained within the database exposed via the enterprise gateway, so again no reason exists to create a data model. Report creation can occur either using the online client or desktop as there is compelling technical reason that I am aware of which would determine where the report is created.

When Should I use the Power BI Desktop?

If the reports need to use data mashups, a data model is required to join the disparate data together. For example, if I need to create a report consisting of tables from two different SQL Server databases and an Excel Spreadsheet, relationships linking those tables need to be created. I need a data model, and since it is not possible to create a data model in Power BI Online, I will create my data model in Power BI Desktop. If later on the report data source needs to be changed, this is possible if you create it in Power BI Desktop. Again you have your choice of either creating the reports in Power BI Desktop or in the online client. Creating a data model in Power BI Desktop does not mean that you must to create the reports there too.

Refreshing the Power BI Report Data for the Online Client

The data model used in Power BI dictates how the report is refreshed. If you are using only a cloud based source, you don’t need a gateway of any kind. The data refresh can be automatic–meaning Power BI will do it for you, scheduled, or use a live dataset depending upon the source used. SQL Azure Database, SQL Azure Data Warehouse and Spark on HDInsight connect live. Changes in made in the source will be reflected in Power BI. The data source connected with an enterprise gateway operates the same way. When the source data is updated, Power BI will use the new data in the reports. Please note that the Analysis Services Connector has been deprecated. If you want to directly connect to a Tabular model located on-premises, use the enterprise gateway to make the connection. As I discussed in a previous post, the enterprise gateway uses local Active Directory information to connect, so users will need to have an AD Account with database access to be able to access data in an enterprise gateway. Unlike many web applications, you must set up each user to have access. All users cannot use one account to connect with the enterprise gateway, but you can do that with a personal gateway.

Personal Gateway – How to Refresh the data from Power BI Desktop

If you have created a data model in Power BI Desktop and want to refresh the data, install the personal gateway on a machine within your network, and set the schedule to perform the data refresh for each source within the model. The personal gateway uses the credentials which are entered when it is configured, which means that users who do not have database permissions can look at the data within Power BI updated by the personal gateway.

I hope this clears up some of the confusion surrounding the data models, the Power BI online client and Power BI Desktop. If you have any questions or are interested in other Power BI topics, please ping me on twitter @DesertIsleSql or post a comment as I am always interested in your feedback.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

Mar 04 2016

Help! The Power BI Enterprise Gateway is not Working

Author: Ginger Grant • Discussion: 4 Comments

Given some of the questions I’ve been getting recently there seems to be a lot of confusion regarding how the Power BI Enterprise Gateway works. The most common scenario seems to be that one person, probably the one who created the gateway, can use it and create reports, but when the reports are shared to others, there is no data, just an error message. Here’s the TL;DR verison. When using the Power BI Enterprise Gateway the Power BI user needs to use the security model of the on premises data source. Sounds simple enough, but in practice things can and do go wrong. Before you bother to read any further, if you can’t for a number of reasons change any Active Directory or Power BI User information, you can stop reading now and just use the Personal Gateway. All of the data access issues described here are specific to the Enterprise Gateway.

What is Required to get a Power BI user to use data from an Enterprise Gateway?

Still reading? Ok, so here’s another description on how the Enterprise Gateway works. When the Enterprise KnockingonDoorPowerBIGateway is installed, it puts in a secure door into the firewall. Power BI is the little man knocking on the door. The gateway asks two questions: Who are you? And What is your Password? The answers to those questions come from Power BI, in the form of your email address the and the password used to login to the online application. If those don’t match what is found in Active Directory, entry is denied. The reports will blank. In other words, AD is saying, I Hear You Knocking, but You can’t Come In, which is also a great song lyric.

Resolving Enterprise Gateway Access Issues

The first thing that you need to do is ensure that the user has an ID on the network containing the data source which has permissions to access the database in the Enterprise Gateway. Second, the password on both the network account and the Power BI account must be the same. If they are not, change one. Lastly you need to check the UPN [User Principal Name] to make sure that the Power BI email address matches what the UPN has in Active Directory. A UPN is sort of a replacement for an account name and does not need to be the same as the account name used to log into the network. As a side note, while you can run a SQL which will show that you are getting an error, it won’t show what the userid and password being sent by Power BI are. The email address in the UPN does not have to match the email set up for the account, and chances are if it is not working, they are not the same. To review the settings, you are going to need access to Active Direct Directory and check the UPN for the user who cannot access the data exposed by Enterprise Gateway. Once these issues have been resolved, the firewall will be opened and users will have the same access to data on Power BI on the internet as they have on the On premises server.

Let me know if you’ve found this post helpful or would like to hear more on this topic.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

 

Jan 17 2016

Which Power BI Gateway: Personal or Enterprise?

Author: Ginger Grant • Discussion: 5 Comments

UPDATE: Power BI now has one Gateway, with the option to use a personal gateway. Check out this post on the new gateway for more information.

Power BI has two data gateways, Personal and Enterprise.  Since I assist companies with Power BI, the name powerBIGatewayPersonal Gateway made no sense to me, especially when I used a personal gateway to update an Organizational Pack. While this is a valid reason for a name change, after all Power Query is now called Get & Transform, so why not just rename Personal Gateway? I digress. Enterprise Gateway is not a replacement for Personal Gateway. It is partially an upgrade of the Power BI Analysis Services Connector, as it contains the features in that app and more. In the future, Enterprise Gateway going to be a way to manage all of the data connections within Power BI. I look forward to writing about that once it is available. The Enterprise Gateway, which I should mention is a Preview Release, supports three different Data Source Types: SQL Server, SAP HANA, and Analysis Services. One enterprise feature which is available is the ability to add users to the gateway you just added, rather than relying on one ID to grant access to everyone. For connections to SQL Server or SAP, the connection to the database is made via the user you entered, so that user should have appropriately limited reporting connections to the data source.

Power BI Data Access Based on User Security

Please note that right now, data access based upon user credentials only works for Analysis Services. For reports with an Analysis Services data source, the information passed to the server is the User Name of the user accessing the report. Using Active Directory, this user is granted the same access to the data on the server that they have on the on-premises network. Here’s an example, let’s say Jason is the sales manager for the Eastern Region, and doesn’t have access to the Western Region within Analysis Services security. Jennifer is the sales manager for the Western Region and has only been granted the ability to see the Western Region information in Analysis Services. If a sales report is created in Power BI which uses Analysis Services as it’s data connection via the enterprise gateway, Jason will only see the information on the Power BI report for the Eastern Region and Jennifer will see the same report with only the information for the Western Region. If Jason gets promoted to National Sales Manager and needs to see everything, once the security in Analysis Services is updated granting him access to all sales regions, he will see everything. Unfortunately, if you have 2008R2 or Standard Edition for SQL Server 2012 or greater, you won’t be able to connect to the server via the Enterprise Gateway.

Factors for selecting  Power BI’s Personal Gateway

The Personal Gateway takes the data and imports it into Power BI. If you want to extract data from a variety of different places such as an Oracle Database, and Excel Spreadsheets, the Personal Gateway will support this, and the Enterprise Gateway won’t.   Remember the Enterprise Gateway only connects to three different data sources, and Excel and Oracle are not on that list. If you want to manage connection and refresh of the data as the administrator or provide access to the data to everyone who needs it, use the Personal Gateway.

When might one want to use Power BI’s Enterprise Gateway?

All of the connections via the Enterprise Gateway are live connections to the underlying server, so there is no need to have a scheduled refresh. After all you are always using the connection to live connect to the server accessing the data. Enterprise Gateway imports nothing, so if you have really large databases which you are reporting upon, it probably makes sense to user the Enterprise Gateway as nothing gets copied. If you have a security policy which forbids storing data in the cloud, Enterprise Gateway meets that requirement as all of the data is stored locally and is merely accessed when needed, like a web page.

Future Plans for the Enterprise Gateway

Microsoft is planning on providing the ability to monitor and audit all of the data sources in the Enterprise Gateway. That would make it truly enterprise as it will provide the ability to see what data is being used throughout Power BI. When that happens, it might be time to get rid of the Personal Gateway. Right now, if the Personal Gateway is working for you for connecting to SQL Server, Analysis Services or SAP and the data refreshes are working, I would hold off upgrading. It’s not terribly easy to what refresh methods are configured in Power BI right now, which is also something that I hope gets fixed in the future as part of the Admin features. When Microsoft releases new versions of the Enterprise Gateway, I will definitely discuss them here. To get future updates, please subscribe to my blog to be notified when they happen.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur