PBCS Enable Summary Drill

Intro

Its not often that something comes along that is this exciting to talk about. Well, maybe I’ll take that back, with Oracle PBCS we get monthly patches and we have been seeing a lot of really cool things coming through on a monthly basis. Unlike Hyperion on-premise, we are now regularly/monthly getting patches and enhancements applied to PBCS. In case anyone is not aware of the patching schedule it is as follows:

• 1^st Friday of every month in Test
• 3^rd Friday of every month in Prod

This gives you the ability to test changes before they are applied in Production and yes that is useful because at times these patches and/or enhancements introduce other issues or break existing functionality.

Anyways, back to the topic at hand, a topic that I am quite interested in, a topic that is on the minds of many EPM system users, a topic that has been awaiting an answer for so long…….and the topic for today………..is…… Summary Level Drill through to source and it is available natively through built-in PBCS functionality!!! Long intro I know…..but an introduction like that of the Chicago bulls line up from the 90s is befitting of this.

Yes, it is finally here. Now despite my excitement from above, I promise to remain unbiased in this blog post and tell you what I like and what I don’t like about the recently enabled feature.

So why is this so exciting? For starters in the past drill through was available, but out of the box you only got drill through from level-0 or bottom level members in PBCS to source systems. Whilst this checked a box for most implementations that drill through was achieved in the implementation, a lot of users found this functionality very limiting as by the very nature of working in planning and budgeting systems you are analyzing numbers at summary levels. To get around this a lot of solutions emerged to help provide this functionality that was missing in PBCS such as: third party tools that specialized in drill through, getting clever with data management in PBCS to load data to summary levels and then drill through to source systems from the summary levels or alternatively using a full flown reporting tool like Oracle Analytics Cloud or OBIEE to deliver full functionality drill through as well as advanced reporting and dash-boarding.

So what exactly has Oracle delivered in the latest PBCS patch (At time of writing this post September 2018 )? Thought you would never ask… Oracle has released a mechanism to easily enable summary level drill through for any load with little steps and without a lot of rework to any existing data loads. Sound to good to be true, read for yourself in the Oracle PBCS What’s New September 2018 Update. Still sound too good to be true well lets analyze what is written in the release notes. The below is taken direct from the release notes I have highlighted what I like in Green and what I absolutely don’t like in Red.

To Summarize:

• What I like: you can enable Summary Drill Through via a setting in Data Management.
• What I don’t like: you are limited to 1000 rows of data when drilling from summary levels in the Data Management Drill Through Report.

So what does it really mean? You get really easy to setup drill through, but with limits and you will have to get clever in how to implement this in order to ensure is useful and functional for your solution. For example if you activate drill through at combinations that will yield more than 1000 records then drill through will only work on occasions where the limit is not exceeded. This can yield an unpleasant experience for users as at times they will get errors and other times they will get drill results. In my experience I like solutions that work, therefore if a drill icon appears the user should see data on drill through. I therefore would restrict my summary drill capability to specific dimensions and forcefully try to limit the functionality so that the solution works for users and the 1000 record limit is not met. To ensure a pleasant user experience, I would create special drill through summary forms & reports and train users to ensure they are able to recognize when data points are enabled for drill through (in other words the system would be enabled for drill through at possible drill combinations and the user is able to recognize and use the functionality). In the mean time I pray Oracle does increase this limit in the future. In fairness, I do understand why the limit is set as each query on the data results in relational queries on the data loaded in Data Management and the hierarchies of planning. This can be quite taxing on the backend relational if not limited which is why I can understand the limit, but I would still like to see it increased in the future.

Key Relevant Oracle PBCS Documentation

Question you maybe asking; Will I be able to just follow the Oracle documentation and just get it to work? And my answer is probably NO!!!!! Where would the fun be in that and I also wouldn’t get to write a blog post and share the how to with you. The Oracle documentation assumes a working knowledge of Data Management and a good level of knowledge of drill definitions and drill region creation in Data Management. As part of the Enable process below I will advise tips or issues that I had when following the documentation and what I did differently to get to a working state.

However, to start I would recommend starting with this link for Oracle PBCS Drill-Through, just to get an understand of what is involved.

How to Enable PBCS Summary Level Drill

For the purpose of this blog I have used an Oracle Fusion Drill Through example, but the concepts should be similar to other sources.

Step 1 – Setup the Drill URL

In data management under setup, go to source system. Choose the appropriate source system and update the URL. For Fusion the Oracle documentation recommends the following:

• In Drill Through URL, specify one of the following Oracle Financials Cloud release URL formats:
  • R13—system uses the Oracle Financials Cloud Release 13 URL format
  • R12—system uses the Oracle Financials Cloud Release 12 and earlier URL format.
  • (Null)—system uses the Oracle Financials Cloud Release 12 and earlier URL format.
• If you need to overwrite the server in addition to specify the release URL format, specify one of the following Oracle Financials Cloud release URL formats:
  • R13@https://server—system uses the Oracle Financials Cloud Release 13 URL format and your server.
  • R12@https://server—system uses the Oracle Financials Cloud Release 12 and earlier URL format and your server.

I had issues using the R13 default format and needed to use something more like R13@https://server. To get the correct URL to use I found out what the Fusion URL for analytics reporting was and added to this format. I ended up with something like this: R13@https://xxxx.xx.xxx.oraclecloud.com/analytics. Without this I was landing on the main fusion page and not the drill report.

Step 2 – Enable the Summary Level Drill Option

In data management under setup, go to target application. Choose the application/cube that you intend to drill from at summary level. In the application options Enable Drill from Summary by setting to Yes.

Step 3 – Activate the Drill Regions

On the dimensional details tab, you need to select your drill fix by checking the Create Drill Region Boxes for dimensions. What does that mean, try to concentrate with me on this because it is very important. By selecting Create Drill Region for a dimension means that only combinations specified in the load will be marked as drillable. In other words if you do not tick a dimension, that dimension will get summary drill enabled. Got to love it!!!!

This is where I take the time to pick a combination that will result in values and hopefully combinations that don’t break the 1000 record limit. This can be time consuming, as involves testing and analyzing the data, but is essential to ensure the system will work in a way that is in line with good practices and should align with user expectations to see data when a drill region is active. As a default I recommend setting the drill region for as many dimensions as possible and leaving only the dimensions unticked that you want to analyze from summary levels. For example, you may select everything and then leave Entity unticked to enable summary level drill from anywhere in the Entity Hierarchy.

Step 4 – Remove Previously Created Drill Region

Login to Calculation Manager, under the database properties, right click the relevant cube and select drill through definitions. The reason we clear them is if previously you had the regions activated for level-0 drilling, the tool is not clever enough to update for summary. Again this is something not in the Oracle documentation and assumes you know Drill Definitions. We then delete the existing drill region if it exists (proceed with caution here, maybe backup the content of the drill region in case you need to restore).

Hint: You can create your own drill definitions.

Step 5 – Run Data Load Rule

No smarts here, just run the data rule from Data Management for the data load you want to activate summary drill for. Ensure on the load rule that the create drill region setting is enabled under the target options.

Step 6 – Activate Drill Through on Reports

This is only relevant if you want to drill from PBCS Reports; Planning Forms and AD-Hoc Smart View will be enabled by default. Using reporting studio web, under the grid properties activate drill through. This will need to be done for every report where you want to activate drill through.

See it in Action

For demo purposes I will show a drill from reports, but the same concept applies for Forms and Smart View. In this case I am drill from a summary entity level with all other dimension combinations at level-0.

You will notice that in the report numbers that only drillable values are underlined.

Clicking the hyperlinked number (underlined number) will open the Data Management Landing page with the various combinations of Entities loaded to make up this total at this parent. Clicking the navigation wheel on any of the records will display option to drill to Fusion to further analyze the transactions behind the value loaded.

Conclusion

Though I would like to see it get better or in other words less restricted I am very happy this functionality is now available and see good use cases for it. Looking forward to see what more Oracle developers are going to pull out of the hat for us in future releases.

A Message

Like a Jedi you are in search of knowledge and I wish the Force is with you on your journey. The Force is always with you!!

 

 

 

 

 

Advertisement