Data Integration_eBook_Top 10 FDMEE and ODI Tutorials

Page 1 of 120

|

CONFIDENTIAL

TABLE OF CONTENTS 1. How to Utilize an HFM Application as a Source ................................................... 3 2. How to Utilize an Essbase Application as a Target ........................................... 18 3. How to Utilize an Essbase Application as a Source .......................................... 37 4. How to Enable and Configure Map Monitor Reports ......................................... 58 5. How to Install ODI Studio for FDMEE .................................................................. 74 6. How to Change the ODI SUPERVISOR Password .............................................. 77 7. How to Improve Productivity in ODI Studio with 3 Settings Changes ............. 88 1. User Parameters ............................................................................................................................. 88 2. Conf File .......................................................................................................................................... 90 3. Log Files .......................................................................................................................................... 92

8. How to Update a Scheduled Batch from the Web Interface .............................. 93 9. How to Load Periodic Data to HFM ................................................................... 106 10. How to Use a Source Adapter Parameter to Filter Data .................................. 109 Step 1: Add Parameter to a Source Adapter (FDMEE) .................................................................... 110 Step 2: Make Note of Source Adapter Details (FDMEE) .................................................................. 111 Step 3: Input Value into Parameter (FDMEE) ................................................................................... 112 Step 4: Locate the Appropriate ODI Project (ODI) ............................................................................ 113 Step 5: Add ODI Project Variable (ODI)............................................................................................ 114 Step 6: Link ODI Project Variable to FDMEE Source Adapter Parameter (ODI).............................. 115 Step 7: Locate the Appropriate ODI Package (ODI) ......................................................................... 116 Step 8: Add the ODI Project Variable to the Appropriate ODI Package (ODI) ................................. 117 Step 9: Modify the ODI Interface to Include Filter (ODI) ................................................................... 118 Step 10: Regenerate ODI Studio ...................................................................................................... 119

1. How to Utilize an HFM Application as a Source One of the enhancements to FDMEE 11.1.2.4 is the functionality to utilize a HFM application not only as a Target but also as a Source. This step-by-step tutorial will demonstrate this functionality, e.g. extracting data from HFM instead of a file or ERP such as SAP or EBS. Our next post will demonstrate interfacing this data to an Essbase application as the target. This tutorial will proceed with version 11.1.2.4 of EPM installed on Windows Server 2012 R2 and with the COMMA4DIM HFM application. 1. Log into Workspace.

1 HOW TO UTILIZE AN HFM APPLICATION AS A SOURCE

2. Navigate --> Administer --> Data Management.

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


4. Select Add.

5. Select Financial Management.

6. Select COMMA4DIM and then OK.


7. Select save after the Application Details have displayed.

8. Select Import Format.


9. Select Add.

10. Select EPM as the Source, which enables the HFM application to be used as a Source.

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


12. The COMMA4DIM 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.

13. Select Location and then Add.


14. Update the Location Details to COMMA4DIM 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.

15. Select Period Mapping.


16. Create the Period Key by selecting Add and then keying the values displayed. The Target Period Month and Year Target will be utilized as the period and year filter for the data extract; therefore, the values keyed should be consistent with HFM. Select Save and proceed to the next step.

17. Select Category Mapping.


18. Key BudV1 as both the Category and Target Category with a Frequency of Monthly. Select Save once this has occurred.

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

20. Select Data Load Rule and then Add.


21. Name the rule COMMA4DIM and then select Save.

22. A Source Filter is needed for each Dimension Name, which can be selected from the drop down. Every HFM dimension must be specified. Note: Filter Conditions, which provide data from the extract, will be specified. The Year and Period dimension will not be specified. Select Add to specify a filter, and then from the Dimension Name drop down select Account then the Ellipsis.


23. Select Refresh Members and then Online for the Member List to populate. Once this is done, the Legal member will be added to Selected Members and then OK will be selected. Note: Only select members that have data. Otherwise, zero records will be extracted, which will generate an error message.


24. The process of adding a Source Filter for dimensions Channel to View is the same process as Account; therefore, only the selections will be displayed. Once all filters are specified, select Save.

25. If a dimension is not specified or an invalid member is specified for a dimension, an error message similar to the image will be displayed in the log accessible from Process Details. For example, this error message was generated by an invalid year (Y#200 instead of Y#2007 in the Filter Slice).

26. Select Execute.


27. When the Execute Rule dialog box displays, select the Import From Source option which automatically selects Recalculate. Once this is done, select Run.

28. Click OK.

29. Select Monitor and then Process Details.


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

31. Select Data Load Workbench to view the record extracted.


32. With a successful completion, select the COMMA4DIM application to review the HFM application log.

33. Once the application is accessible, select Consolidation --> Maintenance --> Task Audit, which will display the extract occurred as a Flat File Extract.


2. How to Utilize an Essbase Application as a Target In the first tutorial, new FDMEE functionality with release 11.1.2.4 was demonstrated. This tutorial will demonstrate the HFM source data interfaced to an Essbase application and assumes completion of part one. This post will proceed with version 11.1.2.4 of EPM installed on Windows Server 2012 R2, the COMMA4DIM HFM application, and the Sample Basic Essbase application. 1. Log in to Workspace.

2 HOW TO UTILIZE AN ESSBASE APPLICATION AS A TARGET

2. Navigate --> Administer --> Data Management.

3. Select the Setup tab and then Target Application.

3 HOW TO UTILIZE AN ESSBASE APPLICATION AS A SOURCE

4. Select Add.

5. Select Essbase.

6. Select Sample-Basic and then OK.


7. Select Save after the Application Details have displayed.

Starting with this step and continuing to STEP 25, the following is from Part One and can be skipped if Part One was completed.



9. Select Add.

10. Select EPM as the Source that enables HFM application to be used as a Source.

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


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


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



16. Create the Period Key by selecting Add and then keying the values displayed. The Target Period Month and Year Target will be utilized as the period and year filter for the data extract; therefore, the values keyed should be consistent with HFM. Select Save and proceed to the next step.



18. Key BudV1 as both the Category and Target Category with Frequency of Monthly. Select Save once this has occurred.





21. Name the rule COMMA4DIM, and then select Save.

22. A Source Filter is needed for each Dimension Name that can be selected from the drop down as every HFM dimension must be specified. Filter Conditions will be specified which provide data from the extract. Note that the Year and Period dimension are not specified. Select Add to specify a filter, and then from the Dimension Name drop down select Account then the Ellipsis.


23. Select Refresh Members and then Online for the Member List to populate. Once this is done, the Legal member will be added to Selected Members and then OK will be selected. Select members that have data; otherwise, zero records will be extracted, which will generate an error message.


24. The process of adding a Source Filter for dimensions Channel to View is the same process as Account; therefore, only the selections will be displayed. Once all filters are specified, select Save.

25. If a dimension is not specified or an invalid member is specified for a dimension, an error message similar to the one below will be displayed in the log accessible from Process Details. For example, this error message was generated by an invalid year, Y#200 instead of Y#2007 in the Filter Slice.


26. Select Data Load --> Data Load Map.

27. Each dimension requires at least one map to specify the import member(s) to the mapped member(s). In the interest of time, a one line like map will be created in this tutorial.


28. Key the following: Source Value of * e.g. any import member(s), Target Value of East e.g. a valid Essbase member for the Market dimension, and a Rule Name. I would recommend a naming convention for the Rule Name. For example, for Like Maps I use WC indicating a Wild Card e.g. *map and the name of the dimension. The naming convention is to assist with troubleshooting as the Rule Name is captured in the Process Details log when an error occurs.

29. Select the Dimensions drop down and choose the next dimension, which is Measures.

30. Select Like and then Add.


31. Duplicate the map in the image and select Save.

32. Repeat the addition of a map for the Product, Scenario, and Year dimensions, utilizing the same process as Market and Measures. The maps are displayed for each dimension.


33. Select Data Load --> Data Load Rule to return to the COMMA4DIM data load rule previously created.

34. Select Execute.


35. When the Execute Rule dialog box displays, select the Import From Source option, which automatically selects Recalculate and Execute to Target. Once this is done, select Run. Execute Check is greyed out, as a Check Rule has not been specified and the Export Mode default of Replace is not changed. Note that the Replace Export Mode is essentially the same option as Replace when loading data with Essbase Administrative Services.

36. Click OK.





39. Select Data Load Workbench to view the successful submission.

With a successful completion, this tutorial concludes.


3. How to Utilize an Essbase Application as a Source 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. 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. 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.


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

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.


4. Select Add.

5. Select Essbase.

6. Select Sample.Basic and then OK.


7. Select Save after the Application Details have displayed.



9. Select Add.

10. Select EPM as the Source, which enables the Essbase application to be used as a Source.

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.


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.



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.



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.

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

18. Select Period name JAN2015 and then OK.


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.



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.




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

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.


26. Expand Market and select East. Select the Function

symbol and pick Level 0 Descendants.

Once this is done, select the greater than arrow 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.


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.

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.

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.


30. Click OK.




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

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


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

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.


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.

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.


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.

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

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.

42. Select Calculation Script Parameter.


43. Select Add and then the Script Parameters binoculars.

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.


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

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


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.

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.


4. How to Enable and Configure Map Monitor Reports For many organizations, Map Monitor Reports are a key internal / SOX control. Specifically, the PSU 11.1.2.4.100 Readme states, “Two new Map Monitor Reports have been added to the Audit Reports report group: Map Monitor for Location and Map Monitor for User. Map Monitor Reports show changes made to the data load mapping rules using any of the methods data load mappings UI, Excel template imports, text file imports, and Lifecycle Management imports. The Map Monitor Reports do not capture historical data earlier than release 11.1.2.4.100. Map Monitor Reports are enabled only if the Enable Map Audit is set to 'Yes' in System Settings.” This blog post will demonstrate how to enable the functionality and detail which map types are logged “monitored” based on the type of change and which are not. 1. Select the Setup tab and then System Settings.

2. Select System Settings –> Other.

4 HOW TO ENABLE AND CONFIGURE MAP MONITOR REPORTS

3. Update the Enable Map Audit Value from No (which is the default) to Yes and then select Save, which facilitates logging of map changes from this point forward with exceptions noted later in this post.


4. Select Workflow –> Data Load Mapping.

5. Select the Explicit Map Type.


6. As mentioned, the Map Monitor Reports have three options: Add, Change, and Delete. First, create a map by selecting Add, keying the appropriate Source Value and Target Value, and then Selecting Save.

7. Update the Source Value to record a change and then select Save.

8. Finally, delete the map by selecting the Source Value and then Delete. Once the Delete Confirmation occurs, select Yes.

9. Select Report Execution.


10. Select Audit Reports.

11. Two reports are provided: Map Monitor Report by Location and Map Monitor by User. Select Map Monitor Report by Location and then Execute .

12. The Location will default to the POV location. Select the Action Type magnifying glass, which provides the Select Parameter Value dialog box. Select All and then OK.


13. Key the appropriate Start Date and End Date in the date format specified and then select OK.


14. When prompted select Open, which will generate a report with the Delete and Add records but without the Changed record. Time to check the Map Monitor Report by User for the same records and then investigate.

15. Select Map Monitor Report by User and then Execute.

16. The Generate Report prompts are the same between reports with a User prompt replacing the Location prompt, with a notable exception: the User prompt does not have the lookup feature. Therefore, key the appropriate User, Start Date, and End Date in the date format specified and then select OK. Once this is done, select OK and when prompted Open the report.



17. Select Open when prompted. The report is rendered with the Add and Delete records also. Curious but not unexpected, as the reports are basically the same as we will learn from a query perspective. Nevertheless, time to investigate further.

18. Select Setup –> Report Definition.


19. Restrict the list of reports to the Map Monitor Report(s) by keying Map and then selecting Enter. The Query Definition utilized for both reports is Map Monitor Query.

20. Select Query Definition.


21. Restrict the list of queries to the Map Monitor Query by keying Map and then selecting Enter, which will display two queries: Map Monitor Action Query and Map Monitor Query.

22. After keying a handful of enters to adjust formatting, the entire query syntax is visible. The Select and Where Clause sections are the typical syntax. Therefore, the analysis will begin with the one of the two objects “aif_artifact_audit_v” listed after the FROM. Note: The tpovpartition object is the table that stores location detail, which is the reason for starting with “aif_artifact_audit_v”.


23. For this environment, the relational repository is SQL Server. Therefore, I start SQL Server Management Studio and log into the appropriate database server. The object that we are investigating is a view, so select the object, right click, and script the view as Alter to a New Query Editor Window.

24. The view syntax will be displayed and, after a few enters, reformatted for readability. Essentially, the view is a standard select with conversion of LAST_UPDATE_DATE to a VARCHAR(25) datatype and aliases as both CHANGE_TIME and CHANGE_DATE. As the view does not have a WHERE clause, our next step is to review table AIF_ARTIFACT_AUDIT.


25. The AIF_ARTIFACT_AUDIT table is queried with the WHERE clause specified as ARTIFACT_TYPE = ‘MEMEBER_MAP_AUDIT’ as this table stores other types of audit records, such as System Settings. Once the query is written “SELECT * FROM AIF_ARTIFACT_AUDIT WHERE ARTIFACT_TYPE = ‘MEMBER_MAP_AUDIT”, execute and once again two records are displayed. Time to test the other map times: In, Between, Multi Dimension, and Like. To alleviate reader boredom, I will skip the creation of the maps and proceed to the results.

26. Essentially, I added a map for each FDMEE map type and then changed the map. The results of the subsequent Map Monitor By Location is displayed. In summary, the change is recorded for map types Between, In, and Like, but not Multi Dimension. Table 1 provides further detail.


Table 1 summarizes the Map Type, the Action (Add or Change), What Changed, and whether the change is Logged. TABLE 1 Map Type

Action

What Changed

Logged

Explicit

Add

N/A

Y

Explicit

Change

Source Value

N

Explicit

Change

Target Value

N

Explicit

Change

Change Sign

N

Explicit

Change

Description

N

Explicit

Delete

N/A

Y

Between

Add

N/A

Y

Between

Change

Source Value

Y

Between

Change

Target Value

Y

Between

Change

Change Sign

Y

Between

Change

Description

N

Between

Change

Rule Name

Y

Between

Delete

N/A

Y

In

Add

N/A

Y


In

Change

Source Value

Y

In

Change

Target Value

Y

In

Change

Change Sign

Y

In

Change

Description

N

In

Change

Rule Name

Y

In

Delete

N/A

Y

Like

Add

N/A

Y

Like

Change

Source Value

Y

Like

Change

Target Value

Y

Like

Change

Change Sign

Y

Like

Change

Description

N

Like

Change

Rule Name

Y

MultiDimension

Delete

N/A

Y

MultiDimension

Add

N/A

Y

MultiDimension

Change

Source Value

N

MultiDimension

Change

Target Value

Y

MultiDimension

Change

Change Sign

Y

MultiDimension

Change

Description

N

MultiDimension

Change

Rule Name

Y

MultiDimension

Change

Condition from Explicit to Between

N

MultiDimension

Change

Source Value

N

MultiDimension

Change

Target Value

Y

MultiDimension

Change

Change Sign

Y

MultiDimension

Change

Description

N

MultiDimension

Change

Rule Name

Y

MultiDimension

Change

Condition from Explicit to Like

N

MultiDimension

Change

Source Value

N

MultiDimension

Change

Target Value

Y

MultiDimension

Change

Change Sign

Y

MultiDimension

Change

Description

N

MultiDimension

Change

Rule Name

Y

MultiDimension

Change

Condition from Explicit to In

N

MultiDimension

Change

Source Value

N

MultiDimension

Change

Target Value

Y

MultiDimension

Change

Change Sign

Y

MultiDimension

Change

Description

N


MultiDimension

Change

Rule Name

Y

MultiDimension

Delete

N/A

Y

N/A

Delete All Mappings

N/A

Y

When creating this blog post, I opened a SR with Oracle and a Bug # was assigned: Bug 22526857 AIF_ARTIFACT_AUDIT TABLE NOT LOGGING ANY CHANGES FOR EXPLICIT AND MULTI DIMENSION.


5. How to Install ODI Studio for FDMEE FDMEE has a full Oracle Data Integrator (ODI) backend installed by default. If you enter through “Workspace,” you will see that not much has changed with the user interface. However, if you are an ODI developer, the screens that you are used to seeing in ODI Studio are not available. ODI has expanded logging and offers generally more granularity than what is available within the FDMEE user interface alone. To use this granularity, you have to install the ODI Studio client, which can be installed on the server, and even most laptops. ODI Studio is especially helpful when things don’t quite go as expected since it allows better insight into the processes and how they function (or don’t function). Oracle installs most ODI things now that in the past were not a simple install, such as the ODI agent and the necessary Windows services, within the Hyperion configuration process. But one thing that they still do not install by default is ODI Studio. ODI Studio can be found by going to eDelivery and, after some searching for Hyperion products, you will find the option to download Oracle Data Integrator Studio (11.1.1.7.0):

Note: Now that you know that the item number ‘V37940-01’ (1.5GB) is the ODI Studio that you require, you can search for that number to minimize your search efforts. You might be able to find the full version of ODI 11.1.1.7 (V37390-01_1of2.zip and V37390-01_2of2.zip and 2.8 GB), but we are not using those files here. Notice that this is not the full version of ODI Studio. If you try to install anything other than the Studio, you will get errors. In fact, you will get errors when you use this install, even if you only install the Studio. It appears that, with this download, Oracle has left out a few CAB files. The good news is that the CAB files that they left in allow for the install of the Studio and connects nicely to the FDMEE backend. You will need to login to Workspace and open FDMEE to get the configuration information used to login to ODI.

5 HOW TO INSTALL ODI STUDIO FOR FDMEE

You will also have to know that the default password for ‘SUPERVISOR’ is ‘SUNOPSIS’, a reference to the company that Oracle acquired. Also needed would be the password for the relational that is the backend of FDMEE, which you may have to get from your DBA. To get the configuration information, in FDMEE click the ‘Setup’ tab, select “System Settings” in the “Configure” section under “Tasks.” You might even want to select the ‘ODI’ option from the ‘Profile Type’ dropdown menu.

This information is all that you will need to configure your newly installed ODI Studio. Once you start your ODI Studio console, you will need to click on the ‘Connect To Repository’ button. This will bring up our old friend − the ODI login screen:


Plug in the information that you obtained from the FDMEE screen:

You should be able to click on the button for the “Work Repository” search and select “FDMEE” at this time. This also tests your relational setup as well, if you get this option:

There you have it: the ODI Studio in all of its glory. The agent is already running and you can use the logging of the Session List.


6. How to Change the ODI SUPERVISOR Password During the installation and configuration of FDMEE, the ODI master repository and agent are installed with a default username of SUPERVISOR and SUNOPSIS. Securing the Oracle Hyperion EPM environment by changing the SUPERVISOR password is not a single step but requires changes in three separate interfaces: FDMEE, ODI Studio Console, and WebLogic / Oracle Enterprise Manager. This post will proceed with version 11.1.2.4 of Oracle EPM installed on Windows Server 2012 R2. The steps for 11.1.2.3.X of EPM are the same except for Operating System differences. 1. Log into Workspace.

6 HOW TO CHANGE THE ODI SUPERVISOR PASSWORD

2. Navigate –> Administer –> Data Management.

3. Select the Setup tab and then System Settings.


4. Select ODI from the Profile drop down.

5. Key the new ODI password for User Name SUPERVISOR and then select Save.

6. After a successful Save, the message “Your changes have been saved” will display.

7. To verify the ODI SUPERVISOR password has changed, select “Check ODI Connection," which will display an Error. Select OK to close the error.


8. Select ODI Studio from Apps By Name.

9. Key the User “SUPERVISOR” and the Password “SUNOPSIS,” and then select OK.

10. Select View –> ODI Security Navigator.

11. Select Users –> Supervisor then right click and choose Open.


12. Select Change password.

13. Key the New Password and then select OK.

14. Select File –> Save.

15. Select View –> ODI Topology Navigator.


16. Select Agents –> OracleDIAgent and then double click OracleDIAgent.

17. Select Test, which will generate an error. This verifies the password has changed. Select OK to close the Error dialog box.

18. Select Start Admin Server for WebLogic Server Domain from Apps By Name.

19. Once the Admin Server has started, select Admin Server Console from Apps By Name.


20. Key the WebLogic Username and Password created during installation and then select Login.

21. Select Oracle Enterprise Manager from Helpful Tools.

22. Key the WebLogic User Name and Password created during installation and then select Login.


23. If the Accessibility Preference dialog box appears, update if needed (the default is displayed below) and then select Continue.

24. Select and expand WebLogic Domain.

25. Select EPMSystem –> WebLogic Domain –> Security –> Credentials.


26. Expand and select oracle.odi.credmap –> SUPERVISOR.

27. With SUPERVISOR selected, click Edit.

28. Key the updated password and then select OK.


29. Return to FDMEE –> System Settings –> ODI and select Check ODI Connection, which will display a successful test of the ODI agent.

30. Return to ODI Console and select ODI –> Disconnect “SUPERVISOR.”

31. Select ODI Connect.

32. Key the User “SUPERVISOR” and Password “Updated One,” and then select OK.


33. Select View –> ODI Topology Navigator.

34. Select Agents –> OracleDIAgent and then double click OracleDIAgent.

35. Select Test, which will generate an Agent Test Successful dialog box. Select OK to close the dialog box.


7. How to Improve Productivity in ODI Studio with 3 Settings Changes Similar to any client program, ODI Studio has various settings that can be updated to improve productivity. This post details three settings that I typically adjust. Assuming a default install and previous configuration of the ODI Studio Client, begin by selecting Start --> All Programs --> Oracle --> Oracle Data Integrator --> ODI Studio.

Log in to the ODI environment.

1. User Parameters To change any User Parameter value, execute these steps: double click the value, key the updated value, and then select Enter. Select OK to exit the Edit User Parameters dialog box. To enable the change in ODI Studio Client, exit and then open the program. 7 HOW TO IMPROVE PRODUCTIVITY IN ODI STUDIO WITH 3 SETTINGS CHANGES

Select ODI --> User Parameters from the menu bar.

When building interfaces, the source and target often have column names that are the same. To enable Automatic Mapping without having to confirm the feature, change the default from Ask to Yes. For the Automatic Mapping option, key "Yes".

When executing procedures, interfaces, packages, scenarios, etc. from the ODI Studio client, the default execution Agent selection is "Local (No Agent)," which would execute with the resources of the computer you are logged into. Typically it is preferred to execute with server resources; therefore, to set the Default Agent, key the preferred Agent (which in this example is OracleDIAgent). This can be a significant time-saver, whether your environment has one agent or seven agents.

7 HOW TO IMPROVE PRODUCTIVITY IN ODI STUDIO WITH 3 SETTINGS CHANGES

The default number of elements displayed in Operator is 100. If you have packages which have loops or the logs are seldom purged, I would recommend updating the value to 0 "no limit" or a significantly higher number than 100. To change the value, double click the 100, key 0, and then select Enter.

Alternatively, the limit dialog can be disabled by changing the default from 1 to 0.

2. Conf File Next a configuration change will be made to increase the memory available to the ODI Client to reduce / prevent java.lang.OutofMemoryError: Java heap space, which occurs in file ide.conf.

With a typical install, the location of the ide.conf file is install drive and then "oracle\product\11.1.1\Oracle_ODI_1\oracledi\client\odi\bin". To find the location from the ODI Studio client, log in to the client and then select Help --> About --> Properties.


Scroll and find the value for ide.conf, which in this default install is to the C: drive and specifically C:\oracle\product\11.1.1\Oracle_ODI_1\oracledi\client\odi\bin\odi.conf.

Open this file with your preferred text editor. The IncludeConfFile parameter provides the location of ide.conf, which is C:\oracle\product\11.1.1\Oracle_ODI_1\oracledi\client\ide\bin\ide.conf. Open ide.conf with your preferred text editor.


Increase the Add VMOption –Xmx640M as appropriate for your environment and computer resources. In this example, the option is changed from 640M to 1536M, as the client machine has 32 GB of memory. Once the changed has occurred, save the file and close / open the ODI Studio Client for the change to occur.

3. Log Files ODI has several logs in different locations. One of the logs is written to java.io.tmpdir, which is used by multiple Knowledge Modules. To find the location from the ODI Studio client, log in to the client and then select Help --> About --> Properties and scroll until java.io.tmpdir is displayed. This location is utilized when "Local (No Agent)" is used.

If you are using the OracleDIAgent installed with FDMEE, assuming installation on Windows, the location is defined in the registry. Specifically, HKEY_LOCAL_MACHINE --> SOFTWARE --> Hyperion Solutions --> ErpIntegrator0 --> HyS9aifWeb_epmsystem1.


8. How to Update a Scheduled Batch from the Web Interface One of the numerous enhancements when migrating from FDM Classic to FDMEE is the capability to schedule a batch from the Web Interface. However, once the batch is scheduled, how does an individual update or validate the schedule? The interface does not provide this option. Before I proceed with how to update or validate the schedule, below is a brief tutorial on creating an open batch definition, which will subsequently be utilized in the scheduling example. Log into Workspace.

Navigate –> Administer –> Data Management.

8 HOW TO UPDATE A SCHEDULED BATCH FROM THE WEB INTERFACE

Select the Setup tab and then Batch Definition.

Once the Batch Definition selection renders, select Add


.

The Batch Definition page will display. As this is a brief example, I will not review or comment on all the options that can be selected at this time except for the items specifically chosen as part of the Batch Definition in this example.

In this example, the Name will be “Schedule”. The name of a Batch Definition is at the discretion of the individual creating the Batch Definition; however, I would recommend creating a name that is relevant to either the FDMEE location or FDMEE data load rule. The Target Application selected is COMMA4DIM, which is a Hyperion Financial Management (HFM) application previously created as a target, and Type will be “Open Batch,” which enables the automation of loading one or more data files. Once the selections are updated, select Save.

Select the Workflow tab.


Select Batch Execution.

The Batch Execution Summary will display the Batch Definition previously created. Select the Batch Name “Schedule” and then select Schedule

.

The Schedule options presented are typical of most scheduling software. In this example, the schedule will be daily at 12:30 AM, which is displayed in the second image after the initial Schedule options. Once the scheduled is updated, select OK.


When the Information dialog box displays indicating a successful scheduling, select OK.

With the Batch Name scheduled, the question is now what. From the FDMEE web interface, currently the options are Cancel Schedule, which will cancel one or more schedules for the Batch Name, or Check Status, which provides the current status. Neither option provides functionality to change or validate the schedule. Hopefully, Oracle will provide this feature in a soon to be released PSU or PSE. Without this feature accessible from FDMEE, the alternative is to utilize ODI Studio.


ODI Studio is a standalone installed separately from FDMEE program and the installation of which will not be detailed, as several other blogs provide this information. Note that the standard FDMEE install also includes a web interface “ODI Console,” which provides some feature parity to ODI Studio but does not provide the capability to change or update a schedule at this time. After ODI Studio is installed and configured, select the ODI Studio shortcut, which with a default install is typically Start –> All Programs –> Oracle –> Oracle Data Integrator –> ODI Studio.

Login into the FDMEE environment.

From the menu bar, select View –> ODI Operator Navigator.


With ODI Operator Navigator selected, expand Scheduling.

Select and expand the following: All Schedules –> COMM_EXECUTE_BATCH Version 001 – > Scheduling –> GLOBAL / OracleDIAgent. Once this is done, select Open. Note: COMM_EXECUTE_BATCH is the ODI Scenario that is scheduled when the Batch Definition Type is Open Batch.


The previously implemented schedule will display for validation purposes and for reference purposes is included as the second image. Note: ODI Studio displays two additional options, which are Startup and Yearly.


To update the schedule, select the Schedule option or update the time. For example, to change the time from 12:30 AM to 2:00 AM, change Hour(s) from 12 to 2 and Minute(s) from 30 to 00. Once this is done, select File –> Save from the menu bar.

As mentioned, COMM_EXECUTE_BATCH is the ODI Scenario that is scheduled for an Open Batch. What occurs when two or more Batch Definitions are scheduled? In the circumstance of two scheduled Batch Definitions, two GLOBAL / OracleDIAgent listings are displayed. How does one differentiate between the two (i.e. which Batch Definition is associated with which listing)?


Select the first listing, then right click and choose Open.

Select the Variables option, which will display four Session Variables, one of which is the Batch Definition name.

Another method to verify the schedule which will be used to change the time from 12:30 AM to 2:00 AM is to select View –> ODI Topology Navigator from the menu bar.

Select and expand the following Physical Architecture –> Agents -> OracleDIAgent. Once this is done, select Update Schedule.


Select OK with the FDMEE repository selected.


Select OK when the ODI Information dialog box renders.

With OracleDIAgent selected, right click and choose View Schedule.

The Schedule dialog box will render with a default time frame of two hours from the executed time. Select the greater than sign

to update the schedule two hours at a time.


After two clicks, the COMM_EXECUTE_BATCH job will be listed at 2:00 AM, which verifies the change from 12:30 AM to 2:00 AM and concludes this tutorial.


9. How to Load Periodic Data to HFM One of the best practices of interfacing general ledger data to Hyperion Financial Management (HFM) is the loading of data with a Year To Date (YTD) View instead of Periodic. However, there are circumstances, such as a partial year implementation of a ledger without history, that prevent this best practice. At a client of a colleague this recently happened. When presented with this requirement, my colleague checked the Import Format and Target Application options, which did not have a View option. As a result, this blog post presents the solution needed to load Periodic data to HFM utilizing FDMEE. After I received the inquiry from my colleague, I also checked the same options with the theory of two sets of eyes are better than one. Being unsuccessful, I checked the latest version of the FDMEE Administrator guide and found the DATAVIEW field in table, TDATASEG. The DATAVIEW is “Hard coded to YTD for file," which is how data was being loaded in this circumstance. Based on this knowledge, a short event script was needed.

Before I display the script, this simple one-row data load displays the business need to load data with a Periodic View instead of YTD based on the ledger data received. In this example, two hundred dollars was interfaced to an Income Statement account in June of the current year. The first image is YTD and the second is Periodic. Both images display two hundred dollars “200,” as expected.

9 HOW TO LOAD PERIODIC DATA TO HFM

In the next example, two hundred and fifty dollars “250,” which represents the periodic value, was interfaced to July of the current year. The first image is YTD and the second is Periodic. Based on a periodic value of “250”, the YTD value should be four hundred and fifty dollars “450” instead of “250.”

To correct the data load, an event script to change the value in the DATAVIEW field from YTD to Periodic prior to the export file being created is needed. Time to open Eclipse, my editor of choice for Python, and type a few lines of code displayed in the next image.

After the implementation of the script as a BefExportToDat event, the interface was executed again. The first image after this paragraph reflects a corrected YTD value of “450,” and the second image reflects a remedied periodic value of “250.”



10. How to Use a Source Adapter Parameter to Filter Data The various menus, options, and setup screens within FDMEE and ODI can be overwhelming. This is why I was intimidated when initially challenged with the task of leveraging FDMEE parameters to filter data sets, but the task turned out to be a great learning experience. Going through the process helped me gain a better understanding of the relationship between FDMEE and ODI objects. OBJECTIVE Provide users with the option of filtering data based on account type. In this particular example, users can choose to include or exclude Income Statement accounts in their data load.  Accounts (Level 0) are 5 digit numbers 

Non-Income Statement Accounts (Balance Sheet) begin with 1,2,3,4


Step 1: Add Parameter to a Source Adapter (FDMEE) A. B. C. D. E. F.

On the Setup tab, click on Source Adapter Select the desired Source Adapter (SOURCEEX in this example) On the Parameters tab, click on Add Parameter Name: p_incl_inc_stmnt Parameter Data Type: Char F. Parameter Prompt: Include Income Statement


Step 2: Make Note of Source Adapter Details (FDMEE) A. ODI Package Name: ODI_Package_Ex B. ODI Project Code: OEX


Step 3: Input Value into Parameter (FDMEE) A. On the Workflow tab, click on Data Load Rule B. Select the desired POV (Location: LocationEX in this example) Location selected must be setup with the Source associated with the Source Adapter the Parameter was added to in Step 1. C. On the Source Options tab, notice the “Include Income Statement” property This will mirror the value entered in Step 1F > Parameter Prompt: Include Income Statement D. Include Income Statement: Y Y = Yes, Include Income Statement Accounts N = No, exclude Income Statement Accounts


Step 4: Locate the Appropriate ODI Project (ODI) On the Designer tab, locate the ODI Project that correlates to the ODI Project Code associated with the Source Adapter from Step 2B > ODI Project Code: OEX


Step 5: Add ODI Project Variable (ODI) A. On the Designer tab, expand the Project folder identified in Step 4 B. Add a new ODI Project Variable: p_incl_inc_stmnt 

Name: p_incl_inc_stmnt To keep things simple, set the variable name to mirror the value entered in Step 1D > Parameter Name: p_incl_inc_stmnt

  

Datatype: Alphanumeric Keep History: Latest Value (Default, feel free to change) Default Value: 0 (Leave blank or set to Y or N)


Step 6: Link ODI Project Variable to FDMEE Source Adapter Parameter (ODI) On the Refreshing menu of the ODI Project Variable created in Step 5, select the appropriate Schema and enter the following code into the Select Query window: f_get_param_value(‘p_incl_inc_stmnt’) This reference to ‘p_incl_inc_stmnt’ in the code above is a direct reference to the FDMEE Source Adapter Parameter created in Step 1 > Parameter Name: p_incl_inc_stmnt


Step 7: Locate the Appropriate ODI Package (ODI) On the Designer tab, locate the ODI Package that correlates to the ODI Package Name associated with the Source Adapter from Step 2A > ODI Package Name: ODI_Package_Ex


Step 8: Add the ODI Project Variable to the Appropriate ODI Package (ODI) A. B. C. D.

Click on the Diagram tab of the ODI Package identified in Step 7. Add the ODI Project Variable p_incl_inc_stmnt from Step 5 & 6 Variable Type: Refresh Variable Order and link variable to other package objects as desired


Step 9: Modify the ODI Interface to Include Filter (ODI) A. Click on the Diagram tab of the ODI Package identified in Step 7. B. Identify the appropriate ODI Interface that is responsible for the data load into FDMEE (ERPI Open Interface to ERPI Balances in this example) C. Create the filter on the source column that houses Accounts (COL01 in this example)  Input suitable code / logic to achieve the desired filter results, I leveraged the following code for the filter:



Based on the above logic: If the Parameter Value in Step 3D was set to ‘Y’ then all accounts would be considered VALID (part of the data result set) If the Parameter Value in Step 3D was set to ‘N’ then only accounts beginning with a 1,2,3 or 4 would be considered VALID (part of the data result set)


Step 10: Regenerate ODI Studio To ensure the change is stored in the Scenario when FDMEE executes, be sure to regenerate the ODI Scenario corresponding to the modified package. Once all the FDMEE and ODI objects have been successfully set up and saved, the user will then be able to filter the data set by updating the parameter value on the Data Load Rule menu in FDMEE and clicking on the Execute button. This is just one example of how to leverage FDMEE Source Adapter Parameter. There are numerous ways to utilize this feature.


About US-Analytics US-Analytics is a full-service consulting firm specialized in Oracle Enterprise Performance Management and Business Analytics solutions. Applying decades of experience along with advanced degrees and certifications, our team of functional and technical experts

Dallas, Texas 600 East Las Colinas Blvd. Suite 2222 Irving, TX 75039

bridge the gap between business goals and IT deliverables.

Houston, Texas 2500 CityWest Blvd. Suite 300 Houston, TX 77042

To ensure end-to-end coverage of your technology, we provide a

[email protected] 877.828.USAS

have helped hundreds of the nation’s largest and brightest companies

complete range of services: process and advisory, infrastructure, implementations, upgrades and migrations, training, and managed services.

Learn more at www.us-analytics.com.

Data Integration_eBook_Top 10 FDMEE and ODI Tutorials

Recommend Documents