Power BI + Oracle PBCS - Part 3: Hierarchical reports

After I wrote the other two posts and shared the same with few of my colleagues, a lot of question poured in asking ‘How about this..?’ or ‘Can we do this…?’, etc and one question asked by our very own Hyperion Jedi (Ahmed Hafez) left me intrigued looking for answers to ‘Can we have hierarchy driven report in Power BI when connecting to PBCS?’ This kept me busy for a few hours with an outcome, the snapshots of which look something like below. The first chart shows the top-level entity roll-up ‘All Entities’, providing an option to drill-down to BUs. The same action can be performed on BUs to lead to entities.

Let’s look at the steps to achieve the required hierarchical structure. I would not talk about the steps to connect Power BI to PBCS and how to parse data, rather suggest to go through Part 1 and Part 2 of this post. In past posts, we utilised the concept of calling PBCS export data slice web service to pull data and parse it. In addition to pulling data slice, we would also need the metadata hierarchy and its parent-child relationship to define the roll-ups. This can be achieved using the ‘Get Member’ endpoint of the REST API.

URL: https://<<service-name>>//HyperionPlanning/rest/{api_version}/applications/{application}/dimensions/{dimname}/members/{member}

The api_version is V3, dimname is the dimension from which member information needs to be extracted and the member is the member name. Interestingly, this service returns the entire dimension structure if we provide the top member of the dimension as a member name. The response is a nested JSON response which keeps going one level down when a member has children.

It starts from the first step on the left and applying all the steps in the second snapshot, give the output as a flattened hierarchy or in Essbase world, a generation layout extract. The query was saved with the name ‘Entity Metadata’.

In Power BI, we can convert columns of a table into a hierarchy by right-clicking on one of the columns and choosing ‘New Hierarchy’ option. This would lead to the creation of the hierarchy as ‘Entity_Hierarchy’ starting from Entity dimension name, roll-up member All Entities, followed by BUs and finally leaf entities.

As discussed in earlier posts, we would now need to pull our entity data. In this case, we pull EBIT data by entity and Year to give us the below data table. We pull only the level zero entities as the hierarchy aggregation will be handled by Power BI internally.

Note: I am not a Power BI expert to claim the described method is best and their could be more efficient ways to achieve the same.

We can now set up a relationship between the two extracts – entity metadata extract and the EBIT data extract by entity and year. This can be done by merging the two data queries and create a new table. On editing an existing query, we have an option of merging queries as a new. We merge the

After merging, the data and year from the merged query and the hierarchy is dropped on the visual to get the desired results.

The bar chart in this example will be a hierarchy driven chart giving options to drill-down or drill-up and view the results. At the same time, this can be also managed through the drill-don and up buttons on the chart.

Ta-da !!!

Its important to pay attention to the volume of the data handled with such integrations. In my opinion, we need to pull only that data what we need to show on the chart rather than pulling the entire cube. This can be broken down into multiple data sources and used together on the chart by establishing a relationship between data sources. Pulling large volumes might hit performance and can be avoided by breaking down the data into multiple data sets. Moreover, I haven’t tried experimenting with ragged hierarchy and need to see how it can be achieved.

Cheers !!!