Scheduled Data Refresh in the Cloud
In this article, we will look at how Power BI workbooks in Office 365 can be automatically refreshed with data from on-premise SAP data sources.
With Microsoft’s Data Management Gateway, on-premise data sources can be connected with Power BI workbooks in Office 365. You can use the Data Management Gateway to schedule automated data refreshes and provide your Power BI users with up-to-date reports and business insights in the cloud.
Connecting to on-premise SAP data
In previous articles on the topic of Connecting Power BI with SAP, we have introduced you to Xtract PPV – a tool from Theobald Software that allows you to expose SAP data as OData stream(s). You can directly connect to an OData stream from Power Pivot or Power Query and build your Power BI reports and visualizations on top.
In this article, we’d like to introduce you to another tool from Theobald Software – Xtract Universal. With Xtract Universal you have all the capabilities of Xtract PPV, plus a whole range of additional target environments for your SAP data. With Xtract Universal, you can not only expose SAP data as OData stream(s), you can also automate the creation and update of your data warehouse environment (based on SQL Server, Oracle, Teradata, or DB2) with SAP data. In addition, you can write SAP data to various file formats, including Tableau’s TDE format.
With this brief introduction of Xtract Universal, let us take a look at how things works.
For this sample scenario, we will define a data extract using customer data from SAP ERP. As with other products from Theobald Software, Xtract Universal allows us to access a broad range of SAP source objects, including SAP tables, remote-enabled function modules/BAPIs, SAP BW cubes and hierarchies or OLTP data sources.
For this scenario, we will use the standard SAP remote-enabled function module SD_RFC_CUSTOMER_GET which returns customer data for a specified customer name or number.
Using the Xtract Universal Designer, we start by creating a new extraction called SAPCustomers based on the function module.
With the data source configured, we can now proceed to define the destination for our SAP data extraction. Xtract Universal offers a range of different data destinations, as shown below. For our scenario, we select HTTP – ODdata Atom which is easily consumable in Power BI.
Let’s run and verify the data extraction in Xtract Universal. The Run Extraction dialog shows the URL which can be used from any external system to connect to the OData feed with SAP customer data, in our example it is http://localhost:8065/OData.svc/SAPCustomers.
Discovering data in Excel
Using the Power Query add-in in Excel, we can easily connect to the OData stream provided by Xtract Universal.
On the Power Query tab, select From OData Feed as the External Data Source and copy/paste the URL from Xtract Universal.
Using the Query Editor, you can apply additional transformations on the data, like splitting columns or replacing column headers or values. From the Home tab, select the Close and Load To… command (this is important so that the automated data refresh will work later on).
In the Load To dialog, select the Only Create the Connection option and the Add this data to the Data Model option. We will need the connection to schedule the automated data refresh and we will use the data in the Data Model for reporting and visualizations.
Select the Load button to execute the query and load the data to the Data Model.
Visualizing the data
Using Power View, you can now create visualizations and reports on top of your SAP data.
Taking it to the cloud
Now we’re ready to upload our workbook to a Power BI site in Office 365 to share it with other users, or to provide access to the report from mobile devices. In Office 365, we have additional Power BI features like the natural language query in Power BI Q&A at our disposal.
Creating a Data Management Gateway
The first step in enabling an automated data refresh for your Power BI workbooks in Office 365 is creating a Data Management Gateway Instance. In the Power BI Admin Center of your Office 365 site, select the Gateways link to configure a gateway and a gateway instance.
For further details, please refer to Introduction to Data Management Gateway.
On the machine where you have Xtract Universal installed, select the link Download gateway installation package here. This will download and install the Data Management Gateway on-premise client.
In the next step, you will register the client with the Data Management Gateway in Office 365, using the key that was generated when you created the gateway instance in Office 365.
You will also specify a certificate that will be used to encrypt the data source credentials that are stored in the cloud. You can use an existing certificate or one that is generated by Power BI.
In the final step, you will define the endpoint access to be either HTTP or HTTPs for your on-premise data source.
Confirm that the service status of the Data Management Gateway Host Service is Started and that your Data Management Gateway client is connected to the Data Management Gateway cloud service.
Creating a Data Source
With the Data Management Gateway configured, we can now create a new data source in Office 365. For more information please see Scheduled Data Refresh Update: New Data Sources.
Navigate to the Power BI Admin Center and select Data Sources -> New Data Source -> Power Query
For the next step, we need to get the connection string to our OData feed from the Excel Power BI workbook.
Open the workbook and select the blank worksheet. From the Data tab, select Connections -> Power Query connection -> Properties -> Definition. Copy the connection string.
Go back to the Office 365 Power BI Admin Center and paste the connection string into the connection info page.
Specify a name, gateway instance, and set the credentials (e.g. Anonymous) for the connection.
Save the data source and test the connection.
Specify the users and groups that can access the data source.
You have now configured a data source that can be used for automatic refreshes.
Scheduling a data refresh
Let’s schedule our Power BI report for automatic data refreshes. Go back to your report on the Power BI site in Office 365 and from the report menu select Schedule Data Refresh.
On the Settings page, specify the frequency and time for the data refresh and an email address for notifications. You can manually start the refresh by selecting Save and Refresh Report.
On the History page, you will find a log of the refreshes that were executed and their status information.
Give it a try
With a few steps outlined in this blog, you can auto-refresh SAP data in your Power BI workbooks in Office 365. It’s easy to get started, both Theobald Software’s Xtract Universal and Microsoft’s Power BI tools are available for free for a trial period. For further info, check out:
Christoph Schuler (firstname.lastname@example.org)