Power BI + Oracle PBCS - Part 2: Connect and Visualise

This post is in continuation to Part 1 of the same topic in which I talked about how PBCS REST APIs can be used to pull data as JSON response. We used the Postman app to view our results. In this post, we will see how we can make a call to an HTTP POST request and parse the JSON response within Power BI.

Note: I am not a Power BI expert and method shared herein is one such way of doing this. There might be more efficient ways to achieve the same where Power BI experts can help.

Power BI offers a lot of inbuilt connectors and methods to pull data from discrete sources and they serve the purpose perfectly. But in certain cases, prebuilt connectors do not serve the purpose and that’s when “Blank Query” brings out its magic, helping to connect to anything which can be defined in Query terms. Because Power BI’s Web connector makes an HTTP GET request by default, we cannot use it to execute our data slice REST API which is POST in nature. Thus we use the Query to enable Power BI to make an HTTP POST request.

In the blank query connector, we have “Advanced Editor” where we can define the query to pull data from PBCS. There is a specific way to define the query and provide the required parameters. A blank editor comes up as shown in below snapshot and we need to define what should go in each section.

Taking the same cash flow example, we define the authentication key and the request body what was passed to the POST request. This is where I find Postman app quite handy as it can provide an encrypted key for the password which we used when attempting to make a request and this encrypted key can be used in a query without compromising the actual password.

After you receive a successful response in the Postman app, click on the code option under the Send button. This gives the user an option to generate the code in various languages. Under each option, you could find the header content which was sent with the HTTP POST request. In the below example, it shows the payload that was used and the authorization key indicating that Basic authentication was used.

The query has 2 parts, first the authentication key and content (which is the request body JSON, we submit with HTTP POST request) and the submission of request using Web. Contents method. Since the JSON we submit with HTTP POST request have all member and dimension names wrapped in quotes, in order to pass the quotes with the request, we need to wrap the member and dimension names within double-quotes.

On hitting Done, it will return the JSON response and I like the way its quite easy to parse the JSON response into a tabular structure which I explain in following steps.

1. It returns three groups of the response – POV, Columns and Rows. Since the POV and Column members were fixed, we will be expanding Rows
2. Power BI uses Power Query to do this parsing. In case you have not observed, Power Query is also available in Excel to pull data from different sources and the same actions can be performed there too. Power Query uses DAX language to perform all these steps. Nice thing about this is that you can view all the steps on the right pane and can go back to any step when required and start over. The step 1 above would produce list objects with all the records
3. Convert this into a table using the option on top left corner
4. The available table has one column, which needs to be expanded using the button at top of the table
5. Expanded columns are further expanded one by one and from the option choose “Extract Values”
6. This would pop-up a window. Choose a delimiter to delimit members from multiple dimensions. For the header (first) column, choose the option to choose a delimiter. I this case I chose | (pipe) delimiter, but anyone of available options or custom option can be used. Do not choose any delimiter for the data (second) column
7. If we look back in the request body, we used scenario and year dimension on rows and the response returned members of both dimension in rows. When using the delimiter, we can easily split the members in the following steps 
8. Right-click on the headers (first) column and choose split column –> By Delimiter and choose |(pipe) to split the contents into two separate columns. Similarly, the data column is text and should be converted to values to be used on charts. Right-click on data (second) columns and choose option Change Type –> Decimal Numbers to convert them into numbers. We can also rename the columns to help identify them when using on charts
9. All the steps are captured in the editor on the right pane and we can name this query a relevant name so it can be used as a source. In this case I renamed it from Query2 to Cash Flow Data
10. Choose the option “Close and apply” to save it as a data source
11. At this point, it may prompt validation of credentials, select edit credentials option and then choose Anonymous in the subsequent window to connect
12. Similarly, other queries can be created to pull required data from PBCS and used in different charts to give a pretty dashboard

Since the data is being pulled using web services, every time Power BI dashboard is refreshed, the query sends a request to PBCS to pull real time data. Moreover, the data from various other sources can be viewed together in single dashboard. This provides ability to bring Actual and Budget data from all possible sources at one place making the decision making easy.

Cheers!!!