Often when developing Essbase reporting applications with FDM, there are additional levels of granularity of the data that are not needed in Essbase, but exist in FDM.  For example the “Invoice #” or “PO #” may be needed when drilling into detail from Essbase, however that same data does not need to reside in the Essbase cube.  Out of the box, FDM would have trouble making this work.  The good news is that FDM is very customizable (once you know where to look).  The secret is in the Visual Basic code that FDM uses to extract and load data.

For example, say you have the following dimensions in your Essbase reporting cube:

  • Account
  • Period
  • Department
  • Scenario
  • Year
  • Product

The drill through data that is being loaded contains the following:

  • Account
  • Period
  • Department
  • Year
  • Product
  • Journal ID
  • Journal Date
  • Line Number
  • Distribution Line Number
  • Line Description
  • Source System
  • Source Description

The Journal ID, Journal ID, Journal Date, Line Number, Distribution Line Number, Line Description, Source System and  Source System Description are data points that need to be retrieved when drilling, however they have no place in the Essbase reporting cube.  A few simple changes can be made in FDM that will allow for loading only the Essbase data needed while presenting the user with the added detail in the FDM tables.

Step 1 – The FDM Essbase Adapter

The FDM adapter configuration will set up the link between FDM and Essbase.  The adapter is a very customizable component used by FDM to integrate with Essbase

  • Open the FDM Workbench and configure the FDM adapter
  • Map the source FDM data to the target dimensions in the reporting cube
Using FDM for Essbase Drill-Through - mapping FDM to target dimensions

(click to enlarge)

  • Configure the FDM dimensions that are not part of the Essbase dimensions by double clicking the dimension.  The Visual Basic code for the FDM dimension should be displayed
  • Replace all the existing VB code with the code below (in this example UD2 Source is shown)
  • Save the new code and repeat for each FDM dimension created that is not part of the Essbase reporting cube.  Make sure to update the “UD2” to match the FDM dimension value

Now that the FDM dimension is set up correctly the FDM load process needs to be modified in order to create the file that will be loaded into the Essbase reporting cube.  This is also completed inside the FDM Workbench / FDM Essbase adapter.

  • From the Actions section of the FDM Essbase adaptor select “Connections” and double click to edit the VB
Using FDM for Essbase Drill-Through - selecting connections

(click to enlarge)

  • In the “fGetEssbaseDimArray()” function comment out all of the “UD(#)” that are not part of the Essbase reporting cube dimensions (save when complete)
Using FDM for Essbase Drill-Through - commenting out dimensions

(click to enlarge)

  • From the Actions section of the FDM Essbase adaptor select “Export” and double click to edit the VB

Using FDM for Essbase Drill-Through - editing the VB

  • Search for the “UD(#)” that is not part of the Essbase reporting dimension
  • Comment out the “UD(#)” portion in the first case statement that is found
Using FDM for Essbase Drill-Through - commenting out the UD

(click to enlarge)

  • Comment out the “UD(#)” portion in the second case statement that is found
Using FDM for Essbase Drill-Through  -second case statement

(click to enlarge)

  • Comment out the “UD(#)” portion in the third case statement that is found
Using FDM for Essbase Drill-Through - third case statement

(click to enlarge)

  • Save the Export VB script and close the FDM Workbench

Step 2 – FDM Mappings

The next and final step is where the changes made in the adapter are linked to the FDM mapping process used in loading data

  • Open the FDM application
  • Navigate to the “Activities”-> “Maps” section in FDM

Using FDM for Essbase Drill-Through - navigating to activities - maps

  • Create pass through mappings for FDM dimensions not in Essbase by using the “Like” type
Using FDM for Essbase Drill-Through - creating pass-through mappings for FDM dimensions

(click to enlarge)

  • Provide values for all required mapping fields:
    • Rule Name (any name will work)
    • Rule Description
    • Rule Definition (set to “*”)
    • Source (“None”)

Browse for target value

 

FDM for Essbase - target key value

(the target key value is what was setup in the FDM Essbase adapter)

 

  • The final mapping should look like the screenshot below:
FDM for Essbase - final mapping

(click to enlarge)

  • Select Update Grid to save the changes.  Create mappings this way for all FDM dimensions that are not in the Essbase reporting cube

At this time the drill through from Essbase setup is complete.  Load data from FDM to Essbase and test the drill through.

Also it helps to create an Essbase load rule in EAS based on the .dat file that FDM creates in the FDM export process.  This will allow FDM to load the cube faster and provides a comprehensive data load error file that is useful when metadata is missing from the Essbase cube.

Have more questions? We can help: