Power BI + Oracle PBCS - Part 1: REST API Structure

“Visualization gives you answers to questions you didn’t know you had.” – Ben Schneiderman.

Undoubtedly, visualization is playing a vital role in today’s corporate world. The data is no longer confined to tables or worksheets, be it a CXO or analyst, its far easy to consume information and report via pretty visuals knit together into colourful dashboards. This has lead to an upward trend of visualization tools like Oracle Analytics Cloud (OACS), Microsoft Power BI, Qlik Sense, Tableau, etc.

With each product bringing its own benefits to the table, it is important for the firms to invest in the best and harness each product’s capability. PBCS might be a leading planning and budgeting SaaS application but doesn’t offer all dashboarding capabilities or the varied charts available with Power BI. Similarly, Power BI or OACS are best for dashboards not for planning and budgeting. Not all firms invest in OACS with PBCS and tend to have a heterogeneous architecture of BI products. Thus, it is important that these products talk to each other in real-time and this post demonstrates how it can be made possible using Web Services.

One fine example of such dashboard is below developed in Power BI and connected to PBCS to pull data in real-time.

Oracle PBCS offers REST APIs to perform import and export of data slices, its documentation can be accessed here. I will be using the Postman app to show how the request is made and what response we receive. I personally like the Postman app (REST API client) as it is quite developer-friendly and helps to generate a lot of code beforehand. You can though use any REST API client available.

Firstly, let's understand the PBCS request structure. Each HTTP request uses certain method to interact with web services, and export data slice uses the POST method. We can read about these methods in detail from the W3Schools page.

URL:Https://<service-name>>/HyperionPlanning/rest/{api_version}/applications/{application}/plantypes/{plantype}/exportdataslice

The api_version currently is V3 and the application and plantype should be replaced with the relevant PBCS app and the plantype from which data needs to be fetched. The parameters required or optional are available as part of the documentation with a snapshot below.

In the below example, I post a request to fetch data for cashflow_amount for the year FY17 to FY20 across my scenarios – Actual, Plan and Budget. On the Postman app, you need to choose the method as POST, provide the URL as per the defined structure above, provide authorization details (choose Basic authentication and provide user name and password) and request body which defines what data is requested.

The response that we get is something as below. Since the response JSON is nested JSON with multi-level collections, the best way is to put those dimensions on rows which need to be displayed on the chart in the dashboard. In this example, I have put Year and Scenario on rows as I like to display my chart comparing cash flow amount by years.

Similarly, we can fetch data for different data slices from PBCS as JSON response which can be easily parsed in Power BI. In the following post, I will talk about how we can parse this response in Power BI to populate charts.

Note: Neither I have any affiliation to Power BI or Postman app, nor do I promote their use. I created these examples as a technology enthusiast to showcase what’s possible.
Cheers !!!