I’ve noticed there might be a confusion with the difference between drill-down, drill-through and drill-back in the context of Oracle EPBCS and PBCS, and understandably so. Although they are very similar from a naming conventions standpoint, they are very different in functionality and configuration. Some might even sound more complicated than they are. I will be shedding some light on this functionality starting with the simplest to the more complicated of concepts and configuration.
When we talk about drill-down, we are always referring to “zooming” into the hierarchy from a higher-level member to a lower-level member. For the sake of simplicity, I will be using only three members in this example.
Drilling down on the Period dimension member “Qtr1” will give me “Jan”, “Feb” and “Mar”. It is simply showing me what members make up the aggregation of the “Qtr1” member.
When it comes to drill-through, the context is different as it is not even related to dimensionality. Drilling though is the concept of adding additional relational data to your multi-dimensional data loads for the end user. The end user can drill-through to that information if the individual wishes to see what is making up the value by viewing the detail records.
Drill-through can only be enabled using data management using the following checkboxes for the dimensions you would like to enable this functionality for. To get to the following screen: Navigator -> Data management -> Setup -> Target application.
Once drill-though is enabled and data has been loaded, we can right click on a 0-level member in our forms or ad hoc grids to drill-though to that region. As shown below, using a Smart View ad hoc grid I can drill-through on the cell shown.
As shown below, there is more information besides the dimensions being shown. This is very valuable in not only in terms of knowing where this piece of data is coming from but also what it consists of. Users can find information, like the GL number, to link back to the GL system this data is coming from. Note that any information can be added to specific data records being loaded into EPBCS and PBCS.
We can add to this concept by allowing drill-through not only on 0-level members, but also on higher level members. Yes, it is possible thanks to the calculation manager, but there are prerequisites to this step.
Prerequisites for parent member drill-through:
- Parent member cannot be a dynamic calc
- Parent member must be mapped in Data Management even though we will not be loading data to it
- Data is to be loaded to 0-level members
- A mapping in data management for the parent member you wish to drill-through on must exist
Once we have our prerequisites set, we can go to the calculation manager under the navigator named as “Rules”. From here we go to “Database Properties”-> “Drill-through Definitions” as shown below.
From here, we will need to replicate a drill-through definition that is exactly like the one that already exists, but with the “Level 0 Flag” turned to false and our additional parent member we are mapping to being added to the regions section. This can easily be done by copying the contents to a notepad and then pasting them back into the new drill through definition. Don’t forget to add the parent member in the regions section.
Final step is loading data to the parent member using Data Management and you will notice that that drill through functionality now exists with the parent members as well!
Drill-Back is very similar to Drill-Through in that it gives you more information about your data. However, this information is not stored in EPBCS and PBCS; it is stored in the source GL application used to extract the data from. With Drill-back, users can go back to the source of the data with one click. Users can see what records make up the aggregated number they see in EPBCS and PBCS just like they would with drill-through but will also already be in the GL source application rather than EPBCS and PBCS. EPBCS and PBCS can be configured to connect to HCM cloud, PeopleSoft and Oracle Financials Cloud. For more information about configuration please feel free to visit this blog post by Key Performance Ideas: http://www.keyperformanceideas.com/finding-answers-in-the-cloud-drill-through-from-oracle-pbcs-to-oracle-fusion-financials-cloud-service
Blog post by Yousef Saead of Key Performance Ideas.