Managing the Account Reconciliation Manager Data Load Process in FDMEE

Purpose

In this tutorial, you learn how to import and validate data with data load mappings in FDMEE, and load the transformed data in Account Reconciliation Manager.

Time to Complete

Approximately 30 minutes

Introduction

Account Reconciliation Manager is a module of Oracle Hyperion Financial Close Management. Data loading to Account Reconciliation Manager is facilitated with Oracle Hyperion Financial Data Quality Management, Enterprise Edition (FDMEE) release 11.1.2.3 and higher and Oracle Hyperion Financial Data Quality Management ERP Integration Adapter for Oracle Applications, Fusion Edition (ERP Integrator) release 11.1.2.2 and prior.  It is important to understand the details of the data load process to build and maintain data load mappings in FDMEE or ERP Integrator.  The data load mappings transform source data into a format that you can load to profiles in Account Reconciliation Manger.  The focus of this tutorial is to walk you through the process of loading data to Account Reconciliation Manager, and explain how to manage unmapped members, such as accounts, that you may encounter during this process.

You can take data from a source data file or from an Enterprise Resource Planning (ERP) source ledger and load it to Account Reconciliation Manager.

In this tutorial, you perform the following tasks to load data into Account Reconciliation Manager:

1. Review and set up profiles In Account Reconciliation Manager.
2. Perform the following tasks in FDMEE:
   1. Review the Account Reconciliation Manager target application.
   2. Create an import format. (In this tutorial, you create and import format for a source data file.)
   3. Define the location, period, and category.
   4. Set up the data load mappings.
   5. Create and execute a data load rule to import and validate the source data.
3. Once the data is imported and all members on validated in FDMEE, return to Account Reconciliation Manager and load the data.

You should understand each step in detail so that the integration design results in good performance with easy-to-manage data load mappings.

Note: This tutorial demonstrates the tasks in FDMEE.

Prerequisites

Before starting this tutorial, you should have:

• A working knowledge of FDMEE and Account Reconciliation Manager.
• Access to FDMEE, Account Reconciliation Manager, and a source data load file.

The first step in the Account Reconciliation Manager data load process is to understand the Account Reconciliation Manager profile definition.  You define profiles with two or more segments.  In this tutorial, the profile is made up of two segments, company and account. For example, the profile 109-1200 refers to company 109 and account 1200, separated by a dash.

In Account Reconciliation Manager, you can load data to profiles for each period, but you must first copy the profiles to the periods in which you want to load the data.

To review the profiles in Account Reconciliation Manager, and copy them to the May 2014 period, perform the following steps:

1. From EPM Workspace, select Navigate > Applications > Financial Close > Reconciliation Manager to launch Reconciliation Manager.
   

   
   
   
   Reconciliation Manager is displayed.

   

2. Select View > Profile List to view the available profiles.

   
   
   The list of profiles is displayed with the profile name in the Account ID column. The profiles include two segments separated by a dash. For example, 102-1110, refers to company 102 and account 1110.
   
   

3. In the navigation pane on the left, expand Actions so that you can copy the profiles to the period into which you want to load data.
   
   
   
   The Actions pane is displayed.
   
   

4. Optional: Select one or more profiles. You can click and drag to select multiple sequential profiles, and you can use Ctrl+Click to select non-sequential profiles.
   
   

5. In the Period list under the Copy to Period area, select the period to which you want to copy the profiles, May 2014, and then click Apply.
   
   
   
   The "Copy to Period: May 2014" dialog box is displayed.
   
   
   
   Note: The Selected Profiles option is available only if you selected profiles from the list (as shown in previous step 4).

6. Select All Profiles, and click Apply.
   
   

7. In the “Copy to Period: May 2014” dialog box, review the results, and click Close. In this example, 93 profiles were copied to May 2014.
   
   

8. Do not close Account Reconciliation Manager. After you perform tasks in FDMEE, you return to Account Reconciliation Manager to load the data.
   

To review the Account Reconciliation Manager target application in FDMEE, perform the following steps:

1. In EPM Workspace, select Navigate > Administer > Data Management to launch FDMEE.
   
   
   
   FDMEE displays the Workflow tab.
   
   
   

2. Click the Setup tab.
   
   
   

   

3. Under Register, select Target Application to display the Target Application page.
   
   
   

4. On the Target Application page, select the ARM type target application. In this tutorial, you select the target application named Account Reconciliation Manager.
   
   
   Note: If a target application for Account Reconciliation Manager does not exist, you must add it.
   

5. In the Application Details area, review the dimension details for the target application.
   Among other dimensions, this application includes the Account, Company, Department, Profile, and Source Type dimensions.
   
   
   

In FDMEE, you create an import format based on the source type that you want to load into Account Reconciliation Manager. The import format defines the layout of source data.

In this tutorial, you define the layout of a source data file that includes fields delimited by a comma. The following columns are included in the file:

1. Company
2. Account
3. Department
4. Currency
5. Amount

In the import format, you define the location of these columns and map them to dimensions in the target application. In this tutorial, you also define the Profile dimension as a concatenation of the Company and Account dimensions, separated by a dash. The concatenation facilitates a simple set of data load mappings, which is a recommended practice. However, you can also use multi-dimension mappings to define how the source data is mapped to a target profile.

The following screenshot displays a portion of the source data file, ARM_Balances_Source_May14.csv.

To define the import format, perform the following steps:

1. Under Integration Setup, select Import Format to display the Import Format page.
   
   
   
   
2. On the Import Format page, click Add to add an import format.
   
   
   
   A blank row is added to the top grid, and the Details area is displayed below the grid.
   
   
3. Under Details, enter the import format name, ARM_BAL, source system, File, file type, Delimited, file delimiter, Comma, and target application, Account Reconciliation Manager.
   
   
   
   
4. Click Save to save the changes.
   
   
   
   The target application dimensions are displayed in the "ARM_BAL:Mappings" area.
   
   
   
   
5. You must now map the columns in the source data file to the dimensions in the target application. For Account Reconciliation Manager, only two dimensions (Profile and Source Type) are required to successfully load data. Optionally, you can use the other dimensions for reference or to determine the mapping definition. 
   
   Start by mapping the Account, Amount, Company, and Department columns in the source data file to the dimensions in the target application. You also define Company as the first part of the Profile target dimension.
   In Source Column and Field Number, enter the source name and field number to map it to the corresponding target dimension.
   
   
   
   
6. In this tutorial, two source columns (Company and Account) represent the Profile dimension. You include multiple Profile target dimensions in the import format to concatenate Company and Account, separated by a dash. You also add a Currency target dimension and map the source currency to it.
   
   
   1. Under Mappings, click Add, and then select Dimension Row > Profile to add an additional row for the Profile target dimension.
      
      
      
      
   2. In the new Profile row, enter the source name, Dash, the field number, 1, and the expression “-“ (a dash).  You then repeat steps “a” and “b” to add another Profile row for the source name, Account, and field number, 2. The three mappings for the Profile dimension concatenate a company name with an account, such as “102-1110”.
      
      
      
      Note: You are not required to concatenate the profile components from the source data file, but it is an option that you can use to make the subsequent mappings easier, which is a recommended best practice. When you concatenate the source dimensions in the same order as the target profile, it facilitates the creation of data load mappings.
      
      
   3. Click Add, and then select Currency Row to add the Currency target dimension to the import format mappings.
      
      
      
      A blank row is added for the Currency target dimension.
      
      
   4. In Source Name, enter Currency, in Field Number, enter 4, and click Save to save the changes.
      
      
      
      The import format mappings are complete.

In FDMEE, you define a location to which you want to load data with the import format, ARM_BAL, that you just created. You also review the period and category for the data load.

Note: This tutorial assumes that the periods and categories are already defined in FDMEE. 

To add a location and review the period and category definitions, perform the following steps:

1. On the Setup tab, under Integration Setup, select Location to display the Location page.
   
   
   
   
2. On the Location page, click Add to add a location.
   
   
   
   A blank row is displayed in the Location grid.
   
   
   
   Under the Location grid, the Details area displays the fields for the location properties.
   
   
   
   
3. In the Details area, enter the location name, ARM_BAL_Loc, and select the import format, ARM_BAL. The Target Application and Source System fields are populated for you.
   
   
   
   
4. Click Save to save the changes.
   
   
   
   
   The location name and properties are displayed in the Location grid.
   
   
   
   
   
5. On the Setup tab, under Integration Setup, select Period Mapping to review the period definitions.  
   
   
   
   The Period Mapping page displays the Global Mapping tab.
   
   
   
   Note: If the periods to which you want to load data do not exist, you must add them.
   
   
6. Click the Application Mapping tab, and from the Target Application list, select Account Reconciliation Manager. Scroll down to view some of the 2014 periods for the Account Reconciliation target application. In this tutorial, you load data into the May 14 period.
   
   
   
   Note: In this tutorial, global mappings are defined for a Planning target application which has a different year format than Account Reconciliation Manager. Application mappings were created for the Account Reconciliation Manager target application to define the periods with the correct year format. In your instance, the period mappings for Account Reconciliation Manager might be defined as the global mappings.
   
   
7. On the Setup tab, under Integration Setup, select Category Mapping to review the category definitions.
   
   
   
   The Category Mapping page is displayed. In this tutorial, you load data into the Functional category.
   
   
   
   Note: If the categories to which you want to load data do not exist, you must add them.
   

You create data load mappings in FDMEE to map source dimension members to their corresponding target application dimension members. You define the set of mappings for each combination of location, period, and category to which you want to load data.

The integration to Account Reconciliation Manager requires that you define mappings for the Profile and Source Type dimensions.  The system is not impacted if you do not define mappings for the other dimensions.  

In this tutorial, the Company and Account dimensions are concatenated to form the Profile target dimension. For example, the company, 102, and the account, 1110, are concatenated into 102-1110, which matches exactly with the profile, 102-1110, in Account Reconciliation Manager. Because we have the same profile definition in FDMEE and in Account Reconciliation Manager, the data load mapping is very simple. You use a “Like” mapping with the * wildcard for the source and target values. The “Like” mapping maps the source “Company-Account” combination to the same combination in Account Reconciliation Manager. 

To define the data load mappings, perform the following steps::

1. In FDMEE, click the Workflow tab.
   
   The Workflow tasks are displayed.
   
   
   
   
2. Under Data Load, select Data Load Mapping.
   
   
   
   The Data Load Mapping page displays the Account dimension mappings for the current Point of View (POV).
   
   
   
   
3. You must select thePOV to define the data load mappings for a specific location, period, and category. At the bottom of the page, click the current location name to define the POV. In this example, you click FranceSales.
   
   
   
   The Select Point of View dialog box is displayed.
   
   
   
   
4. Select the location, period, and category for which you want to define the data load mappings, and click OK. In this tutorial, you select the ARM_BAL_Loc location, May 14 period, and Functional category.
   
   
   
   
5. In the Dimensions list, select Profile to define data load mappings for the Profile dimension.
    
   
   
   There are currently no mappings for the Profile dimension for the current POV.
   
   
   
   
6. Click the Like tab, and then click Add to add a blank mapping row in the grid.
   
   
   
   
7. For Source Value and Target Value, enter *, and for Rule Name, enter a name for the rule, such as All. Click Save to save the changes.
   
   
   
   Note: You may have different requirements for your source data files and applications,in which case, more complicated mapping rules might be required.
   
   
8. In the Dimensions list, select Source Type to define mappings for the Source Type dimension.
   
   
   
   There are currently no Like mappings for the Source Type dimension.
   
   
   
   Note: You can define the Source Type for Account Reconciliation Manager as “source system” or “sub-system” (subledger).  These options are a categorization that defines the source of the data. In this tutorial, you define the source type as “source system”. This option does not necessarily indicate that the source data is from the same source system, but does indicate that the data is not from a subledger, such as Accounts Receivable (AR), Accounts Payable (AP), and so on.
   
   
9. Click Add to add a blank mapping row in the grid.
   
   
   
   
10. For Source Value, enter *, for Target Value, select source system, and for Rule Name, enter a name for the rule, such as All. Click Save to save the changes.
    
    Note: When you map all members to “source system”, all imported rows default to the same source system.
    
    
    

Your next step in FDMEE is to create a data load rule for the location. When loading data from a source data file, the data load rule defines the file(s) that you want to import to the location for a specific category. After creating the data load rule, you execute it to import the data. You then verify that the data was imported and transformed correctly.

To define and execute the data load rule, and then verify that the data was imported and transformed, perform the following steps:

1. On the Workflow tab, under Data Load, select Data Load Rule.
   
   
   
   The Data Load Rule page is displayed with the current POV. In this tutorial, the POV is set to the ARM_BAL_Loc location, May 14 period, and the Functional category.
   
   
   
   Note: You define data load rules for a specific location and category, and can execute them for one or more periods.
   
   
2. Click Add to define a data load rule.
   
   
   
   A blank row is added to the Data Rule Summary grid, and the fields in the Details area are displayed.
   
   
   
   
3. In the Details area, enter the data load rule name, ARM_Load, select the Functional category and the ARM_Balances_Source_May14.csv source data file name, and click Save.
   
   
   
   The rule is displayed in the Data Rule Summary grid.
   
   
   
   
4. With the rule selected, click Execute to define the options for running the rule.
   
   
   
   The Execute Rule dialog box is displayed.
   
   
   
   Note: For an Account Reconciliation Manager target application, the only available rule options are “Import from Source” and “Recalculate”. The third option (Export), that you usually see in the Execute Rule dialog box, is not available because you cannot export data into Account Reconciliation Manager directly from FDMEE.
   
   
5. Select Import from Source. The Recalculate option is automatically selected for you. Verify that the selected start and end period is May 14, accept the Import Mode, Replace, and click Run.  
   
   
   
   Note: When you import source data, FDMEE imports and also transforms (validates) the data by using the data load mappings that you defined.
   
   An Information dialog box displays your process ID, 659.
   
   
   
   
6. Click OK.
   
   
7. On the Workflow tab, under Monitor, select Process Details to check the status of your process.
   
   
   
   The Process Details page displays a green check mark indicating success for your process ID (659). The process imported and validated the source data successfully.
   
   
   
   
8. On the Workflow tab, select Data Load Workbench to check the imported data in Data Load Workbench.
   
   
   
   The Load Data tab displays the imported data. At the top of the page, the Import and Validate tasks display a gold fish, which indicates a successful import and validation.
   
   
   
   Note: During validation, FDMEE applies your data load mappings to map source members to target members. In the above example, the Profile column displays the concatenated Company-Account profile, and the Source Type column displays “source system”.  Optionally, you can easily filter the imported data by selecting the following options from the Show list:
   
   
   
   For example, when you select “Invalid Data”, the rows with mapping errors are displayed. Selecting “Ignored Data” displays any row that has a target member of “IGNORE”. The default option, “All Data”, shows all rows.
   
   Important: When validation errors occur during the Validate task, a Validation tab displays the unmapped source members. You must review the unmapped source members and then, in the Data Load Mapping task, adjust the mappings to correct all errors. After correcting the errors, execute the data load rule again and check the results in Data Load Workbench. You must iterate between adjusting the data load mappings, importing the data, and viewing the results until you are satisfied that they include the rows that you want to load to Account Reconciliation Manager.
   

Now that you successfully imported and validated the data in FDMEE,  you are ready to return to Account Reconciliation Manager to load the data.

Reminder: In Account Reconciliation Manager, you have already copied the desired profiles to the period where you want to load the data.

To load data into Account Reconciliation Manager, perform the following steps:

1. In EPM Workspace, click the Reconciliation Manager tab to return to Reconciliation Manager.
   
   
   
   Reconciliation Manager is displayed.
   
   
   
   
2. Select Manage > Periods.
   
   
   
   The Manage Periods dialog box is displayed.
   
   
   
   
3. Select the row for May 2014, the period to which you want to load the data.
   Commands on the toolbar are now available.
   
   
   
   
4. In the toolbar, click Load Data .
   
   
   
   The “Data Load Execution [May 2014]” dialog box is displayed.
   In this example, there are no data loads for the selected period.
   
   
   
   
5. In the toolbar, click New to define the data load.  
   
   
   
   The New Data Load Execution dialog box displays the available locations from FDMEE.
   
   
   
   
6. In the Name field, enter a name for the data load execution, such as ARM_Balance. Clear all of the FDMEE location check boxes except for the ARM_BAL_Loc location, and click OK to run the data load process.
   
   
   
   Note: When executing a data load into Account Reconciliation Manager, you can load data from multiple locations. All data load rules for the selected locations are run during the data load process.  
   
   The “Data Load Execution [May 2014]” dialog box displays the status of the ARM_Balance data load. The status in the Staging, Data Load, and Post Process columns displays a clock, which indicates that the process is pending.
   
   
   
7. In the toolbar, click Refresh until the status displays a success check mark in each column.
   For example, maybe the first time you click Refresh, a success check mark displays in the Staging column and a green arrow displays in the Data Load column to indicate that the process started. 
   
   
   
   When the process completes, the success check mark is displayed in the three columns.
   
   
   
   Note: At this point, all data from FDMEE is loaded into Account Reconciliation Manager, including any records with unmapped accounts.  With a large number of unmapped rows, some of the tables in Account Reconciliation Manager grow and must be truncated in some cases to maintain performance. When Account Reconciliation Manager encounters unmapped accounts during the data load process, an “Unmapped Accounts” link is displayed in the Result column. You can follow this link to view the unmapped accounts. You can then return to FDMEE and update the data load mappings to fix the unmapped members. After that, you return to Account Reconciliation Manager to repeat the data load process. As much as possible, try to limit the number of unmapped accounts to keep the list in Account Reconciliation Manager small. For additional information, refer to the Account Reconciliation Manager chapters in the Oracle Hyperion Financial Close Management Administrator’s Guide.
   
   
8. Click Close twice to close the dialog boxes.
   
   
9. Select View > Reconciliation List to view the loaded data.
   
   
   
   The Reconciliations page is displayed with data for April 2014.
   
   
   
   
10. In the period list, select May 2014.
    
    
    
    The loaded data rows for May 2014 are displayed.
    
    
    
    
11. Right-click a row, and select View to review the details.
    
    
    
    The detail for the row is displayed.
    
    
    You have successfully loaded data into Account Reconciliation Manager.
    

In this tutorial, you learned to:

1. Review and set up profiles in Account Reconciliation Manager.
2. Perform the following tasks in FDMEE:
   1. Review the Account Reconciliation Manager target application.
   2. Create an import format.
   3. Define the location, period, and category.
   4. Set up the data load mappings.
   5. Create and execute a data load rule to import and validate the source data.
3. Load the data in Account Reconciliation Manager after importing and validating the data in FDMEE.

Resources

• To learn more about fixing data load mapping errors, refer to the “Understanding the Account Reconciliation Manager Data Loading Process” document that is available at the following Oracle support link:  https://mosemp.us.oracle.com/epmos/faces/DocumentDisplay?id=1662983.1.
• To learn more about FDMEE, attend the Hyperion Financial Data Quality Management EE Administration course.
• For additional information, refer to the FDMEE and Financial Close documentation: http://www.oracle.com/technetwork/middleware/epm/documentation/index.html