Often when developing FDM for Essbase reporting applications, 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
- 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
- In the “fGetEssbaseDimArray()” function comment out all of the “UD(#)” that are not part of the Essbase reporting cube dimensions (save when complete)
- 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
- Comment out the “UD(#)” portion in the second case statement that is found
- Comment out the “UD(#)” portion in the third case statement that is found
- 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
- Provide values for all required mapping fields:
- Rule Name (any name will work)
- Rule Description
- Rule Definition (set to “*”)
- Source (“None”)
- The final mapping should look like the screenshot below:
- 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:
- Contact us using the form here, or call us at 952.947.9300.
- Drowning in data but parched for predictive insights? Arrange a free half-day analytics health check.
- Talk to our experts face to face at one of our upcoming industry events.