us-analytics.png
Contact Us

FDMEE Tutorial: How to Utilize Essbase as a Source

 

In previous FDMEE tutorials, we've talked about how to utilize HFM as a source and how to utilize Essbase as a Target.

One of the enhancements to FDMEE 11.1.2.4 is the functionality to utilize an Essbase application not only as a Target but also as a Source. Unfortunately, prior to PSU 11.1.2.4.100, the product had a bug that prevented this functionality. Now with the release and installation of PSU 11.1.2.4.100, bug 20747662 “Import from EPM source (Essbase) fails” has been resolved. This step-by-step tutorial will demonstrate this functionality, i.e. extracting data from an Essbase Block Storage “BSO” application.

eBook: Top 10 FDMEE & ODI Tutorials

 

This post will proceed with version 11.1.2.4.100 of FDMEE and 11.1.2.4.000 of the other EPM products mentioned installed on Windows Server 2012 R2 and with the Sample.Basic application.database. 

1. Log in to Workspace.

Screen_Shot_2015-12-29_at_9.08.24_AM.png

2. Select Navigate –> Administer –> Data Management.

Screen_Shot_2015-12-29_at_9.09.45_AM.png

3. Select the Setup tab and then Target Application, which will be the source and target in this example. EPM applications are not specified as a Source System.

Screen_Shot_2015-12-29_at_9.10.51_AM.png

4. Select Add.

Screen_Shot_2015-12-29_at_9.11.21_AM.png

5. Select Essbase.

Screen_Shot_2015-12-29_at_9.12.09_AM.png

6. Select Sample.Basic and then OK.

Screen_Shot_2015-12-29_at_9.13.08_AM.png

7. Select Save after the Application Details have displayed.

Screen_Shot_2015-12-29_at_9.14.10_AM.png

8. Select Import Format.

Screen_Shot_2015-12-29_at_9.15.08_AM.png

9. Select Add.

Screen_Shot_2015-12-29_at_9.16.10_AM.png

10. Select EPM as the Source, which enables the Essbase application to be used as a Source.
Screen_Shot_2015-12-29_at_9.16.51_AM.png

11. Update the remainder of the Import Format Details: Name, Description, and Source as Sample.Basic and Target Type as EPM. Once this is done, select Save.

Screen_Shot_2015-12-29_at_9.19.50_AM.png

12. The Sample.Basic mappings require an update, which in this circumstance will be to map the same Source Column to the same Target. Once the mapping is complete, select Save.

Screen_Shot_2015-12-29_at_9.20.50_AM.png

Screen_Shot_2015-12-29_at_9.20.59_AM.png

13. Select Location and then Add.

Screen_Shot_2015-12-29_at_9.22.15_AM.png

14. Update the Location Details to Sample.Basic for Name and Import Format. The remainder of the Location Details are defaults and will not be changed in this example. Once the updates have occurred, select Save.

Screen_Shot_2015-12-29_at_9.23.13_AM.png

15. Select Period Mapping.

Screen_Shot_2015-12-29_at_9.23.55_AM.png

16. Create the Period Key by selecting Add and then keying the values displayed. The Target Period Month will be utilized as the period for the data extract; therefore, the values keyed should be consistent with the Year dimension. Select Save and proceed to the next step. Note: Year Target is not needed, but it is required by the interface.

Screen_Shot_2015-12-29_at_9.25.07_AM.png

17. Select Application Mapping and then Sample.Basic from the Target Application drop down. Then select Add.

Screen_Shot_2015-12-29_at_9.26.09_AM.png
Screen_Shot_2015-12-29_at_9.26.42_AM.png

18. Select Period name JAN2015 and then OK.

Screen_Shot_2015-12-29_at_9.27.22_AM.png

19. Key Jan as the Target Period Month and then Jan as the Year Target. Once this is done, select Save. Jan is specified for the Year Target as the Year dimension in the application is months “MMM” at level 0.

Screen_Shot_2015-12-29_at_9.31.59_AM.png

20. Select Category Mapping.

Screen_Shot_2015-12-29_at_9.32.38_AM.png

21. Key ActBud as the Category and Budget as Target Category with a Frequency of Monthly. Select Save once this has occurred. Note: ActBud is an acronym for Actual to Budget, as this Data Load Rule is intended to represent the extracting of Actual data and loading to Budget for seeding purposes.

Screen_Shot_2015-12-29_at_9.33.29_AM.png
Screen_Shot_2015-12-29_at_9.34.48_AM.png

22. Select Workflow and then Data Load Rule. If needed, update the FDMEE POV for the Location, Period, and Category created in the previous steps.

Screen_Shot_2015-12-29_at_9.35.50_AM.png
Screen_Shot_2015-12-29_at_9.36.30_AM.png

23. Select Data Load Rule and then Add.

Screen_Shot_2015-12-29_at_9.37.15_AM.png

24. Name the rule Sample.Basic and then select Save.

Screen_Shot_2015-12-29_at_9.38.00_AM.png

25. A Source Filter is needed for each Dimension Name, which can be selected from the drop down as every Essbase dimension must be specified. Filter Conditions will be specified which provide data from the extract. Reminder: The Period dimension is not specified.

Select Add to specify a filter. Then, from the Dimension Name drop down select Market and then the Ellipsis.

Screen_Shot_2015-12-29_at_9.39.24_AM.png

26. Expand Market and select East. Select the Function Screen_Shot_2015-12-29_at_9.40.38_AM.pngsymbol and pick Level 0 Descendants. Once this is done, select the greater than arrow Screen_Shot_2015-12-29_at_9.41.07_AM.png to add the selection to the Selected Members and then OK. Note: Select members that have data — otherwise zero records will be extracted, which does not generate an error message.

Screen_Shot_2015-12-29_at_9.42.31_AM.png
Screen_Shot_2015-12-29_at_9.42.41_AM.png
Screen_Shot_2015-12-29_at_9.44.13_AM.png

27. The process of adding a Source Filter for the other dimensions is the same process as Market; therefore, only the selections will be displayed. Once all filters are specified, select Save.

Screen_Shot_2015-12-29_at_9.48.30_AM.png

28. Select Execute. Note: Wildcard maps were previously created for each dimension except for Scenario, which is an explicit of Actual as the Source and Budget as the Target.

Screen_Shot_2015-12-29_at_9.49.26_AM.png

29. When the Execute Rule dialog box displays, select the Import From Source option, which automatically selects Recalculate, and then select Export to Target. Once this is done, select Run.

Screen_Shot_2015-12-29_at_9.50.20_AM.png

30. Click OK.

Screen_Shot_2015-12-29_at_9.50.55_AM.png

31. Select Monitor and then Process Details.

Screen_Shot_2015-12-29_at_9.51.42_AM.png

32. A successful completion is displayed for the Process ID submitted.  

Screen_Shot_2015-12-29_at_9.52.29_AM.png
at_9.53.35_AM

33. Select Data Load Workbench to view the records extracted.

Screen_Shot_2015-12-29_at_9.54.52_AM.png
Screen_Shot_2015-12-29_at_9.55.21_AM.png

34. With a successful completion, log into Essbase Administrative Services to view the log, a sample of which is captured below.

Screen_Shot_2015-12-29_at_9.56.16_AM.png

35. Return to FDMEE and select Process Details. Then select Show for Process ID 170.

Screen_Shot_2015-12-29_at_9.57.07_AM.png

36. Depending on your browser settings, answer the dialog boxes appropriately to open the file. Based on my browser settings, I selected Leave this page and then Open.

Screen_Shot_2015-12-29_at_9.59.25_AM.png

37. Based on a review of the log, the extract of data occurs by the creation of a calculation script with the DATAEXPORT calculation command. The first four members  / list of members in the FIX correspond to the Source Filters previously created, and the last member “Jan” equals the Target Period Month specified as the Period Application Mapping. The file created “SjR555ea91.txt” is temporary and is renamed to Sample_LoadID.dat and moved to the FDMEE application Data folder.

Screen_Shot_2015-12-29_at_10.00.13_AM.png

38. Further in the log, the Sample_171.dat file is imported, and because the ARCHIVE MODE is set to Move, an archive file is created.

Screen_Shot_2015-12-29_at_10.01.24_AM.png

39. The last step in this blog is the creation of an Essbase calculation script to clear the Scenario and Period prior to data being loaded. Log into Essbase Administrative Services and create a calculation script with RUNTIMESUBVARS. The RUNTIMESUBVARS are set to Feb and Actual for validation purposes only, as the values which will be set by Essbase from FDMEE are Jan and Budget.

Screen_Shot_2015-12-29_at_10.02.04_AM.png

40. Select Setup –> Target  Application –> Calculation Scripts.

Screen_Shot_2015-12-29_at_10.02.55_AM.png

41. Select Add and then key and/or select the following: Script Name ClrAToB, Script Scope Data Rule, Scope Entity Sample.Basic, and Event Before Data Load.
Screen_Shot_2015-12-29_at_10.03.49_AM.png

42. Select Calculation Script Parameter.

Screen_Shot_2015-12-29_at_10.04.32_AM.png

43. Select Add and then the Script Parameters binoculars.

Screen_Shot_2015-12-29_at_10.05.12_AM.png

44. Select RTYear and then Script Value POV Period. Once this is done, repeat the process to set the RTScenario to POV Category. Once both parameters are specified, select OK.

Screen_Shot_2015-12-29_at_10.05.49_AM.png
Screen_Shot_2015-12-29_at_10.06.25_AM.png
Screen_Shot_2015-12-29_at_10.06.32_AM.png

45. Select Save from the Target Application dialog box and then select Workflow.

Screen_Shot_2015-12-29_at_10.08.11_AM.png

46. Repeat Step 29 for the Sample.Basic Data Load Rule.

Screen_Shot_2015-12-29_at_10.08.45_AM.png
Screen_Shot_2015-12-29_at_10.09.12_AM.png

47. Select Process Details and then Show for Id 175. Search for ClrAToB, which will display the RTScenario parameter is set to Budget and RTYear is set to Jan.

Screen_Shot_2015-12-29_at_10.09.44_AM.png
Screen_Shot_2015-12-29_at_10.10.18_AM.png

48. The successful setting of the RUNTIMESUBVARS can also be verified from the Essbase Sample application log, as displayed in the excerpt from this log.

Screen_Shot_2015-12-29_at_10.10.58_AM.png

10 Handy FDMEE & ODI Tutorials

New call-to-action

Ask an EPM/BI Advisor

If you're here, you've got questions — and we've got answers. Book your consultation to ask us about any range of topics, including:

  • Evaluating EPM or BI technologies
  • Comparing on-prem vs. cloud
  • Planning upgrades and migrations
  • Estimating project costs and timeframes
  • And much more — ask us anything!

Let our experts tackle your toughest questions for you.

Let's Talk