The following blog describes how to use certain cloud-based Power BI for Office 365 products (Power View and Q&A) on a Power BI Site that will reference data on-premises. To do this you will use certain on-premise Power BI products (Power Pivot and Power Query) to gather the data. Note that you cannot currently upload Power Map reports to a Power BI site (instead use maps in Power View, see Maps in Power View).
This blog applies to on-premise data that resides in SQL Server that is on an Azure VM or an Analytics Platform System (APS), as well as a normal server. I’ll make note of any differences.
Download and install the Data Management Gateway (DMG) on the server that contains the on-premise data that resides in SQL Server. If you are using APS, this will have already been done for you on a VM installed with AU3. This VM will already have the DMG installed which allows a gateway to APS. Next create a Power BI site if you have not already done so (see Add Power BI sites to your Office 365 site). On your Power BI site, create a data gateway (see Data Management Gateway Introduction) and note the generated gateway key and then enter that key in the DMG. This will link your DMG to the Power BI site (a DMG can only be linked to one Power BI site). Then create a SQL Server data source on your Power BI site that uses the data gateway (see Data Sources via Power BI), enter the connection info to your server, and specify the SQL Server tables to expose. Take note of the created OData data feed URL.
Next you will open an on-premise Excel workbook, go to the Power Query tab, sign in to your Power BI site, and use either “Online Search” or “From Other Sources -> From OData Feed” and choose the table(s) to load (both will create an OData feed connection). If loading the data into Excel, once loaded select the data and choose “Add to Data Model” on the PowerPivot tab. Or, load the table directly to a data model. Then create a Power View report. Save the Excel workbook and upload it to your Power BI site. You will now be able to view the Power View report on the Power BI site. Then use the “Add to Q&A” option on the workbook on the Power BI site, and you will then be able to use Q&A.
Note that you are using data in the Power Pivot model in the workbook you uploaded to Power BI and not the data directly on SQL Server (so the Power View report is hitting Power Pivot, so if data changes in SQL Server, that won’t be reflected in Power View unless you setup the DMG to refresh the data from SQL Server as I explain below). To refresh the data in the workbook in Power BI from SQL Server, use the “Schedule Data Refresh” option on the workbook in Power BI and create a daily refresh (there is no option to update more than once a day). To refresh the data more frequently, you must do it manually by going to the setting tab on the “Schedule Data Refresh” page and clicking the button “refresh report now” (the status of the connection must be OK to see this button). See Schedule data refresh for workbooks in Power BI for Office 365.
Before you use data refresh, be aware that the OData feed connection info that was in the Excel workbook when it was uploaded will not work for a data refresh. This is because any data sources that are retrieved via Power Query using an OData feed are not supported for data refresh. So what you need to do is get a proper connection string by importing data from SQL Server using an SQL Server connection: In on-premise Excel, go to the Power Query tab, click “From Database -> From SQL Server Database”. Enter the connection info (server name) and then Excel will populate the Navigator with names of all the tables. Then load one of the tables into the data model. Now go to the Data tab, click Connections, select the properties of the Power Query connection, and on the Definition tab copy the connection string. Next go to your Power BI site and configure a Power Query data source (see Scheduled Data Refresh for Power Query) where you will paste that connection string. After adding the Power Query data source, any Excel workbooks that you want to refresh should use a connection to SQL Server (“From Database -> From SQL Server Database”) and not an OData feed (“Online Search” or “From Other Sources -> From OData Feed”). Once uploaded to Power BI the workbooks can be refreshed as Power BI knows to discover your gateway and data sources. You won’t need to create another Power Query data source as long as each workbook uses the same raw connection info (server name).
Now in beta for Power BI is a way to access data from the Power BI site in real-time on on-premise SQL Server. This is accomplished by having Power BI connect to an SSAS tabular model that is on-premise and uses Direct Query mode. That SSAS tabular model is connected to SQL Server, so a Power View report or Q&A on the Power BI site would be hitting SQL Server in real-time. Power BI is able to “find” the on-premise SSAS via an installation piece that is similar to the DMG, called the Power BI Analysis Services Connector. Note that Installing the Power BI Analysis Services Connector and the Data Management Gateway on the same computer is not supported. Once the Power BI Analysis Services connector is installed and connected to an SSAS server, the tabular model databases for the server instance you configured will appear when users select the “Get Data” option when using Power BI Designer Preview (this is the only place the tabular models can be used). The connector currently supports Analysis Services 2012 and 2014. In the future, Analysis Services Multidimensional, SQL Server and other data sources will be added.
Some notes:
The DMG can be used to publish raw data sources (connecting to data sources directly using the server name) or expose them as OData feeds (using OData to find the data source to save users from having to know the server name but with the disadvantage of workbooks the use an OData feed will not be able to be refreshed on a Power BI site). You will want to create both a raw data source connection as well as a OData feed to the same data source so users can use both features: search for data without needing connection info as well as have the ability to refresh workbooks on the Power BI site.
When using the OData feed URL in Excel, you will be asked for the method you want to use to access it. Select the Organizational account and enter your Office 365 credentials. Also make sure you are signed into Excel with the same account (see Power BI – Data Management Gateway).
If you are using an Azure VM for your SQL Server, when creating a data source in Power BI, the set credentials does not work (you get the message “Failed to verify gateway ‘InstanceName1′ status. Unable to connect to the remote server”). As a workaround, login to the VM itself and configure the data source. See Using Power BI Data Management Gateway on Non-Domain Azure VM.
If you are using an Azure VM for your SQL Server you will want to change the computer name of the Azure VM (see Using Power BI DMG on Non-Domain Azure VMs – August 2014 Update) and add endpoints in Azure Management Portal and firewall rules exceptions on your Azure VM (see Using Odata feed on Azure client whose Data Source is configured against DMG on Azure VM and How to Set Up Endpoints to a Virtual Machine).
More info:
How to set Power BI Schedule Data refresh with simple excel file Office 365
Power BI Data Management Gateway 1.2 Changes the Game
Limitations for Power Query OData Feeds in Power BI
When is a Data Management Gateway Needed in Power BI?
Tip! How To Configure Power BI Preview Analysis Services
