I was on a BI project recently which required bringing together data from several cloud-based systems (including Salesforce CRM and Oracle Fusion Financials) and creating analytical dashboards using Power BI. Power BI includes an out-of-the-box connector for Salesforce which makes it very easy to connect to this source; however, there is no such thing for Oracle Fusion nor is there any documentation on the Power BI site on how to accomplish this.
This post describes how to make web service calls from Power BI to the Oracle Financials Cloud Release 11. Specifically, we will use Power Query (and the M language) to make a SOAP API call to execute an existing Oracle report and load the result into a Power BI table. You can read more about the web services supported by Oracle Business Intelligence Publisher here.
Start by creating a SOAP UI project. When you don’t really know where to start with Web Services, SOAP UI is a free application will get you up to speed quickly with how a SOAP request and response should look like. In our case, it will also allow you to test your code using a tried & tested application, whereas Power Query may be a bit of a black box, complete with the usual cryptic messages that Microsoft apps output when something goes wrong.
After you download and install SOAP UI, create a new project using the following initial WSDL:
https://<your host name>/xmlpserver/services/PublicReportService?WSDL
This will give you all the available endpoints for the PublicReportService, including runReport(), which is what we are going to use. Scroll down to it and select the request. You will see a SOAP envelope in XML format which includes all possible options for configuring how you want the report to run. It is not mandatory to send every possible element. In my case, using the following subset was enough:
Note that in my case using the Authentication header from SOAP did not work; I had to pass in user credentials directly in the SOAP envelope using the userID and password fields, which is not ideal.
When you execute this request, you should get a response which looks like this:
The <reportBytes> node contains the report data in Base64 encoding. As a side note, I had changed the output of my Oracle report to CSV and used pipe “|” as separator.
Now that we have the SOAP UI project working, we can go on to create a Power Query which uses Web.Contents() to simulate what SOAP UI is doing. Start by selecting Get Data\Blank Query, and modify you code in the Advanced Edit to look something like this:
A few things to note here:
- The request is basically copied & pasted from the SOAP UI request.
- When calling Web.Contents(), you have to pass in the request in Base64 binary format.
- The headers that go along with the request are also very important, and remember that Oracle is very finicky with the spelling so make sure to use the correct letter case.
- The result of Web.Contents() is passed directly to Xml.Document(), but only “reportBytes” element is needed, the rest is fluff. Remember that it is in Based64 encoding though, so we use Text.FromBinary() to convert it to actual text.
- Finally, the whole text field is loaded into a Csv.Document() call, with pipe as delimiter, as I was mentioning before.
That’s it. We can now do additional processing such as promoting the first row as headers, changing column names and data types, and so on. Some final thoughts:
- The Power Query above should be improved to not require storing user credentials in the PBIX file in clear text. (When you run the query for the first time, you are being asked to provide credentials for how to connect to the web source. Unfortunately, Web.Contents() only supports anonymous authentication when the Contents parameter is supplied. Moot point though, as I haven’t been able to use Basic authentication from SOAP UI either. Let me know if you figure out a better way to do handle this part.)
- Another concern is that this method is most likely not ideal for transferring large volumes of data (though I haven’t tried it yet).
Are you interested in implementing Power BI into your company? Contact an expert at 813.265.3239 for help in developing a strategy for your data needs.