Tuesday, May 13, 2014

A deeper insight into drill-through from Planning & Essbase

Drill-through is a word you should be familiar if you are either FDM or ERPI/FDMEE fan.
I remember trying to explain classic FDM customers differences between drill-through, drill-back, drill-down...all getting crazy with so many drills!

What's drill-through?
FDMEE admin guide defines it as "the navigation from one value in one data source to corresponding data in another source"
Or in other words:
Navigate from my EPM application's data to corresponding source data
The formula would be Data(EPM) > Data(FDMEE) > Data(Source) and the relationship between source data values and target data values is usually N to 1 so multiple source transactions (lines in flat files, rows in tables, line item details in ERP...) aggregate to one target value in our EPM application. But definitions or relationships apart, we basically want to trace our data from source to target or target to source.

Another concept you will be familiar with is landing page. When drilling through from our EPM application, a page will be displayed. That page is the landing page.
When drilling from EPM application to FDMEE we will have the FDMEE landing page opened as new tab in Workspace. From this page we will continue with the drilling to the source system where landing page will be normally opened in another navigator page.

With drill-through functionality FDMEE users can get answer to questions like "Where is this $1000000 coming?" By drilling through we can determine if the root cause are wrong mappings, wrong source data, or any other cause who will let us blame someone else :-)

In this post, I will be focused on drill-through from Planning/Essbase. If you want to know more details about drilling through to source systems I strongly recommend you the following document from Oracle Support.

How does drill-through work?
Firstly, we must say that, although it is recommendable, drill-through from EPM application to FDMEE is optional. We have to specify in the target application options that we want to load drill regions (Setup > Register > Target Application):
When drill regions are enabled, they are loaded into the target application after data is loaded and consolidated. A cell is drillable in the target application if it belongs to the drill regions defined in the application.
What must be clear is the following: you will be only drilling successfully to your FDMEE data if the same intersection was loaded through FDMEE... and you did not clear data in FDMEE!
For example, if you loaded $1000 to Entity1;Account1 intersection, you will not be able to drill-through from Entity1;ParentAccount1 as you did not loaded data for that intersection, even if you see drill-through icon like happens in HFM. Drilling from parents and seein detailed data for all children would be nice but it is not possible yet.

What are drill regions in Planning/Essbase?
We are talking about drill regions but we did not explain what they are, where they are stored, etc.
Drill regions are defined by combination of dimension members. They will say from where we can drill to source. When we define a drill-through URL in Essbase (do not confuse with drill-through concept used by EIS or Essbase Studio), it is stored in the Essbase database file as metadata.
The URL has 4 components:
  • URL Name
  • URL XML
  • List of Drillable Regions
  • Level-0 Boolean Flat
Don't worry about creating drill-through definitions, we are working with FDMEE, so FDMEE will create it for you including drill regions.
If you want to learn more about drill-through and drill regions in HP/Essbase, take a look to the following Essbase Addendum Document.

How are drill regions loaded by FDMEE?
For HFM, a file with extension .DRL will be created and exported. This will happen after data is successfully loaded and consolidated (optional). Drill URL and drill regions will be stored in two HFM database tables (HFMApp_ERPI_URL and HFMApp_ERPI):
For HP/Essbase, FDMEE will load drill regions directly into Essbase with no file being exported.
Technically, FDMEE uses Essbase API although that will be transparent for end user. You will see how FDMEE consumes [HPLService] and [EssbaseService] to create drill regions:

Which dimensions are included in the drill region loaded from FDMEE?
The main difference between Planning and Essbase is that for Essbase, you can select which dimensions are included in the drill region (Setup > Register > Target Application) while Planning includes Entity/Account/Scenario/Year/Period.
Having this flexibility for Essbase lets you define the granularity for the drill region. For example, if we select only the Product dimension, we can drill on a cell that at least does not have a null for year.

How are drill regions generated by FDMEE?
I consider this part the most important because is what will let us know how FDMEE works.

  • If your source system is EBS or PSFT and you have defined metadata rules for them, drill regions are created based on the metadata rule
  • For all other source like flat files, drill regions are created based on the target members defined in the data load mappings
  • For Year, Period, and Scenario, FDMEE uses audit information to create drill regions
I know this can generate some controversy so let's analyze each of the three points above.
  • If you have metadata rules, drill regions will be only created for dimension members loaded from your metadata rules. If you need to create additional drill regions you would have to create them via FDMEE script.
  • For the other two points I will show you an example you will understand all details.
Understanding how drill regions are created when no Metadata rules exist
I have successfully loaded my data to Planning/Essbase through FDMEE:

No what?
I navigate to my HP form and check data has been loaded:
I see my data but no cells are drillable! so gold fish + data in the form + no drillable cells = weird.
Did I check process details in FDMEE?
Ok, I can see that there was an error creating drill regions in Essbase. Let's ask your best friend in FDMEE: the process log (with log level set to 5 of course)
The log shows an error in [REGION DEFINITION] so I will analyze the region definition :-)
It seems that there is an account A00090000 which is not familiar to me. I know accounts included in the drill region will come from my data load mapping definition so I should have a look to it:
Rule 30_Group.090 is not correct. Account A00090000 does not exist in Planning/Essbase Cube. However the account was included in the region definition as it is explicitly defined in the data load mapping tables. That made drill region creation failed.
In this support document you will find details about solving your issues with drill regions in Essbase.
I can remove that mapping rule as I don't need it for audit purposes. So after removing the mapping and loading data again...I still see the error!
I then go for plan B, let's have a look to Audit table AIF_APPL_LOAD_AUDIT:
I can see that there are some entries for old executions which failed due to my issue. Probably they have some data related in TDATASEG, so if I remove these entries, that residual data will be removed as well.
Done, now reload again and...
Drill regions created!
I can now navigate to my form and drill to FDMEE to see details:
And my landing page is displayed in Workspace as expected: 

 What can I conclude?
  • Drill regions will be created for dimension members even if you are not loading data to them. If they are explicitly defined as target values in the mappings, then they will be included in the drill region definition (as you can see in the process log shown above)
  • Drill regions will include only dimension members which are defined as explicit target vales (no wildcards) in any kind of mapping type (Explicit of course, In, Between, and Like) For example,  if you have a LIKE mapping rule defined as A100 > HP*, it will not include account HPA100 in the drill region. On the other hand if you have an IN mapping rule defined as "1000,2000,3000" > ACC123 theb ACC123 will be included innthe drill region regardless data is loaded to that account or not.
  • Therefore you must be careful when using wildcards in your mappings like * to *
  • Review your audit tables. It can also happen that you create a mapping for a non-existing target period in your Period Mappings, making this FDMEE fail when creating the drill  regions. For example, if you create a period mapping for "March" but the valid one is "Mar", the audit table will have an entry for the wrong period after data is processed. Fixing the typo will not help, you have to remove wrong entries from audit tables.

Classic FDM Vs. FDMEE
In classic FDM, drill regions were created for all intersections with POV members being loaded:
Having explicit, in, between, or like mappings using wildcards did not impact.
Let's say that we load one file with one data row for specific account:
We can see data loaded in Planning and the drillable cell:
If we enter data manually in the other 3 cells:
Voilà! you can also drill from these cells...can does not mean you will be able to successfully do it as you did not loaded data from FDM to these 3 intersections!
You will be only successfully drilling to intersections loaded through FDM:

As far as I know, the way drill regions are generated may be changed in 11.1.2.4 so they are created based dimension members where data is loaded. Therefore the creation process will be looking more to target values after conversion rather than mapping definitions. In any case, this is something I cannot confirm 100%.

And finally, a bit more about Drill-through ... 

Where can I see drill-through definitions in Essbase?
You can use EAS Console to see drill-through definitions (right click on cube > Edit > Drill through definitions):

Woops, you will not see drill-through definitions created by FDMEE. Why? there is bug in Essbase related to displaying definitions having special character like ":" in the URL name. You can check drill-through definitions exist using MAXL command:
By default the URL name of the drill-through definition created by FDMEE is Year:Scenario

Drill-through from ASO Plan Type
Starting with the 11.1.2.3 release, Planning customers having a full use Essbase license can now have plan types which use aggregate storage databases:
When working with ASO plan types in Planning, you will not be able to drill-through from Forms due to product limitation:
It is only possible to drill-through from Smart View/Financial Reporting using the Essbase Provider:
BTW, if you want to load data to an ASO plan type you will have to do it by registering the Essbase cube and not by loading to the plan type as you normally do when loading to Planning.

I'm sure these two functionality will be introduced in future releases.

Drilling through from where?
We can drill-through from Planning/HFM forms, Smart View, and Financial Reports.
If you want to drill from SV or FR bear in mind that it will only work if the data source is HFM, HP or Essbase. So if you try to open a Financial Report in Smart View you will not see any option to drill.

I noticed the post is bit long. Sorry for that :-)

Enjoy!


26 comments:

  1. Nice post but have you ever seen a user/customer happy with the FDM / FDMEE drill through feature?

    Here we have strong reject of this feature since it is not possible to drill through on parent intersection...
    In day to day work, this is rather tedious to work with the FDM drill through feature.

    So we are loosing a loy of time to build complex datawarehouse with Essbase Studio just for having the drill through at parent level...

    If only FDMEE could do that...

    ReplyDelete
    Replies
    1. Hi Stephen,
      thanks for your feedback.
      I agree that Drill-Through and Metadata Loading are two features that should be enhanced in the next releases. And my feeling is that they will be.

      Drilling-through from parents is very important for end users although drilling from base members can be very useful for audit purposes when users want to navigate to source system.

      Regards!

      Delete
    2. You are right I should say to my users that this feature should be used for audit purposes and not day-to-day analysis (even if it's what they want..)

      Regards,

      Delete
  2. Hi Francisco,

    To confirm your above statement, Drill Through will only work when using Explicit mapping types, and will not work using In, Between, Like, and Multi-Dimensional?

    This seems like a big letdown vs. previous versions which allowed this, as well as would force people into miles and miles of maintenance-heavy Explicit mappings vs. lists/ranges/wildcards.

    Trey

    ReplyDelete
    Replies
    1. Hi Tray,
      What i sais is that drill regions will only include dimension members which are defined as explicit target values regardless the mapping type. So if you have an in, between, or like ruke where the target value is explicit (i.e: A123*-> ACC1000), that target value will be included in the drill region (i.e: ACC1000)

      I will clarify in the post if it makes confusion

      Delete
    2. Ah, that makes more sense. As long as there are no wildcards in the Target mapping value, you will be fine.

      Still a bit scary that we wouldn't be able to drill on wildcard Targets, but perhaps in the future releases :)

      Thanks for the clarification

      Trey

      Delete
    3. You can also crate a custom solution to create drill regions based on target data being loaded.

      Delete
  3. Thanks for this post, and all of your other helpful posts! One question still remains, as you said you have explained the difference between drill-through and drill-back before - can you clarify?

    thanks,
    Sam

    ReplyDelete
  4. Hi Francisco,

    You should add one thing to you post...

    With this beautiful tool, explicit mapping in FDMEE must be case sensitive, otherwise the drill through will not work...

    I have lost 3 days understanding why the drill through was not working...

    ReplyDelete
    Replies
    1. You mean, typing target members in mappings as they are in target application, right?

      I would need to test. In the ERPI log you can see the query for the drill-through. Is that taking into consideration case sensitive?

      Definitely, enhancement needed then.

      Thanks!

      Delete
    2. Yes exactly if in your FDMEE explicit mapping, you are mapping product1 => product1 and that in essbase the member name is in fact Product1 then the Drill Through will not work.

      Oracle DB is case sensitive :)

      tested with source=flatfile and target=Planning

      Delete
  5. Is drill-through feature available for all ERPs and data warehouse? What's the required technical requirement to enable drill-through? Thanks.

    ReplyDelete
    Replies
    1. Not for all ERPs. You just need a landing page accepting input parameters which will be then passed from FDMEE. You can also drill-through using a javascript to open a client tool. There is a support doc for drill through.

      Delete
  6. Is there any feature which can handle the error message which appear when user drill through higher level.
    can any meaning message appear for example"You have drill from higher level"

    ReplyDelete
  7. Does PBCS Financial Reports drill back to FDMEE? I just tested it and the drill indicator is not available for FR. I'm able to drill back from Smartview and Plannign Web forms though via PBCS.

    ReplyDelete
  8. I haven't tried yet but I would say yes. Have you enabled report with drill-through option?

    ReplyDelete
  9. Hello Francisco,

    Thanks for a valuable and informative post. Just wondering, for Drill-Thru to work via FDMEE, is it mandatory that we need to load data via FDMEE.

    Eg: I want to know if I load metadata hierarchies using FDMEE, but not the data, can I create Drill-Thru regions using metadata rules in FDMEE. Data would be loaded for the same interactions via flat file from the same data warehouse which sources metadata for Essbase application via FDMEE

    ReplyDelete
    Replies
    1. the only way I can think is if you have a custom script which loads your metadata and then create the drill regions

      Delete
  10. Thanks Francisco for a great write-up. I was able to setup the drill through. To test, I just wrote an ASP page to display post values being passed. Through Workspace it is working like a charm. When I use Smart View, the drill through to page works but variables are not displayed. Does it still use post method when accessed through smart view? Thank you!

    Nitin

    ReplyDelete
    Replies
    1. NOt sure how it works internally in Smart view. From SV you would drill to FDMEE and then from FDMEE to your ASP page

      Delete
  11. Hi Francisco,
    Great blog and notes. We have devised a way to synchronize the planning hierarchy with a utility table and enable drill down to FDM in 11.1.2.3.000 data from level 1 or higher members. It basically finds all level zero values that match the roll up defined for those members. Works fantastic of course it is custom.

    In your section "How does drill through work" you mention FDMEE does not support drilling from a "parent". This blog was in 2014. Are there any releases yet that support drilling from a parent level out of the box? This would be a fantastic feature and help us eliminate a custom component we are maintaining.

    Thank you for your response.
    -Kevin G

    ReplyDelete
  12. Hi Francisco,

    Is possible to drill to a Oracle Database? If yes, how?

    Thank You
    Cátia

    ReplyDelete
    Replies
    1. not out of the box you would need to have 3rd party tool to show data when passed url with parameters

      Delete

Thanks for feedback!