As I have been giving demos on Power BI for Office 365, I have received a tremendous amount of positive feedback, with many clients really loving it. There are common questions that I hear, and I thought I would list them here with answers:
Will there be an on-premise version of Power BI for Office 365?
Power BI for Office 365 is an umbrella name for numerous technologies and tools that blend well together. Most of the tools are strictly on-premise versions: Power Query, Power Pivot, Power View, Power Map, and Data Management Gateway. There are three tools that are cloud-only versions (they only work with SharePoint Online): Power BI Sites, Power BI for mobile, and Power BI Q&A. Microsoft’s strategy with new tools is “cloud first”, which gives the benefit of being able to have monthly updates. Microsoft has not announced if there will be an on-premise version of these tools (meaning if they will work with on-premise SharePoint). See Power BI for Office 365 requirements for more details.
What is stored on the cloud when using Power BI for Office 365?
You will have a SharePoint Online site that has the app “Power BI Sites” that is used to search shared queries and data sources. Search is done via a Data Catalogue that indexes the metadata from the shared queries and optionally indexes the data. You can also optionally upload workbooks to the Power BI Sites to share with others as well as use for use with Power BI Q&A feature.
Eventually there will be the ability to have data move from an on-premise source (via the Data Management Gateway) through the Power BI Sites to an external user (a user outside your network). Right now this feature is not available as you can only route data to an internal user (one that is part of your network) in which case the data is not routed through the Power BI Sites but instead goes directly from the on-premise source to the internal user (the Data Management Gateway is smart enough to notice the user is internal and to not waste sending the data to the Power BI Site).
What will I miss out on with Power BI if our company does not allow SharePoint Online?
Because Power BI Sites does not have an on-premise version, for those companies that won’t or can’t use SharePoint Online, they will not be able to search on-premise data sources exposed by the Data Management Gateway via an OData feed and won’t be able to share queries created with Power Query (since they both require Online Search in Power Query which requires the metadata in the data catalogue, which is in the cloud via a Power BI site). They also can’t use Power Q&A or Power BI for mobile, so much of the “power” in “Power BI” is not available to them.
What is the benefit of being able to share queries with Power Query?
This is a huge benefit! Think of the old way of sharing queries: users write T-SQL queries and save them in .sql files out on a network folder, or they write the queries in Excel and save them in a network folder. If you need to create a query and want to see if someone else has previously created something similar, you browse the network folder and hope the file name gives you a clue as to what data the query uses. What a pain! With Power Query, all the metadata about the query (query name, description, column names) is stored in a Data Catalogue in the cloud (via the Power BI site), and any user can use the Power Query Online Search feature to easily see if a query they need already exists. You can even preview the data to see if it what you need. Not having a data dictionary or metadata catalog has always been a missing piece in the Microsoft toolset, and that has been rectified with the Data Catalogue.
What is the benefit of exposing on-premise data sources via the Data Management Gateway (DMG)?
DMG allows the cloud to connect to on-premise data sources. The way it does this is by exposing data from the on-premise data source as a OData feed, where the metadata for the OData feed (data source name and description, table names, column names) is automatically indexed in the Data Catalogue in the cloud (via a Power BI site). Then, any user can use the Online Search feature in Power Query to search for data without having to know any server names or connection info. Even better, they can search for data sources by data source name/description, table names, or column names.
Let’s use an example to better illustrate: Say there are three servers, each with SQL Server. Each has a database with various tables and users would like to pull data from all three databases via a query. Let’s say customer info is spread across all three servers: one server has customer info that includes customer number and customer name, another server has customer number and customer address, and another server has customer number and customer phone. A user wants to merge the data from all three servers into one table. How would they do that now? The common solution is a DBA exports those three tables into text files and puts them in a network folder for the user to import. Or the DBA merges the data together into a SQL Server table somewhere and gives the user access to it. Either way, it requires that the user gets the DBA involved, and you wind up with many copies of the data after similar requests are made by different users.
But if you use a Power BI site and DMG, any user can use the Online Search in Power Query and enter keywords like “customer number” and those three customer tables from the three different servers will show in the query results. The user can then create a query for each one, and then merge those three queries together, all done easily inside Excel via Power Query. The DBA does not need to get involved, so this is true “self-service BI”. You can even preview the data to see if it what you need. Not having a data dictionary or metadata catalog has always been a missing piece in the Microsoft toolset, and that has been rectified with the Data Catalogue.
Is the Data Management Gateway (DMG) still useful if it is against company policy to store metadata in the cloud?
No, unfortunately not. The DMG needs to register with the Power BI Admin Center, which is only available via SharePoint Online, and will always index the metadata (there is not a way to turn that off). So there is not a way to use the DMG without using SharePoint Online and indexing the metadata.
What type of security is used for the various tools?
- Data Management Gateway enables you to expose the data from on-premises data sources as an Open Data Protocol (Odata) feed. This data can be accessed either using HTTP or HTTPS. When using HTTPS, a SSL certificate is required
- Power Query adheres to the Crypto Mobility guidelines, according to the Microsoft SDL Process, by encrypting local credentials using DPAPI
- SharePoint Online. Explained at Is My Data Secure In SharePoint Online? and Security in Office 365 Whitepaper
- Power BI Sites – Follows the same security as SharePoint Online
- Data Catalogue – Follows the same security as SharePoint Online
- Server connection info to data sources stored on SharePoint online – Follows the same security as SharePoint Online
When will Power BI for Office 365 be released?
Two of the tools are already released: Power Pivot and Power View. The tools still in beta are Power Query, Power Map, Power BI Sites, Power BI for mobile, Power BI Q&A, and Data Management Gateway. Power Query has had monthly updates with the other tools having updates every 2-3 months. Microsoft has not announced when these tools will be released, but all are quite stable based on my use of them.
