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
(click to enlarge)

(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
(click to enlarge)

(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)
(click to enlarge)

(click to enlarge)

  • From the Actions section of the FDM Essbase adaptor select “Export” and double click to edit 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
(click to enlarge)

(click to enlarge)

  • Comment out the “UD(#)” portion in the second case statement that is found
(click to enlarge)

(click to enlarge)

  • Comment out the “UD(#)” portion in the third case statement that is found
(click to enlarge)

(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

  • Create pass through mappings for FDM dimensions not in Essbase by using the “Like” type
(click to enlarge)

(click to enlarge)

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

 

(click to enlarge)

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

 

  • The final mapping should look like the screenshot below:
(click to enlarge)

(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 any questions or need any help?  Feel free to reach out directly:

Dan Bjurstrom, dbjurstrom@ecapitaladvisors.com

Jon Harvey, jharvey@ecapitaladvisors.com