FDMEE Tutorial: Using a Source Adapter Parameter to Filter Data

Financial Data Quality Management Enterprise Edition (FDMEE) and Oracle Data Integrator (ODI) are useful products with lots of features. The various menus, options, and setup screens within FDMEE and ODI can be overwhelming.  Which 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 really helped me gain a better understanding of the relationship between FDMEE and ODI objects.

NOTE: The steps below will not walk through installing or setting up FDMEE.  The assumption is that the environment is already setup and running with working source and targets.  

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. On the Setup tab, click on Source Adapter

B. Select the desired Source Adapter (SOURCEEX in this example)

C. On the Parameters tab, click on Add

D. Parameter Name: p_incl_inc_stmnt

E. Parameter Data Type: Char

F. Parameter Prompt: Include Income Statement

1sourceadapter

Step 2: Make Note of Source Adapter Details (FDMEE)

A. ODI Package Name: ODI_Package_Ex

B. ODI Project Code: OEX

2sourceadapter

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

3dataloadrule

Step 4: Locate the Appropriate ODI Project (ODI)

 A. 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

4projectcode

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)

5variable1

Step 6: Link ODI Project Variable to FDMEE Source Adapter Parameter (ODI)

A. 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

6variable2

Step 7: Locate the Appropriate ODI Package (ODI)

A. 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

7packageoverview

Step 8: Add the ODI Project Variable to the Appropriate ODI Package (ODI)

A. Click on the Diagram tab of the ODI Package identified in Step 7.

B. Add the ODI Project Variable p_incl_inc_stmnt from Step 5 & 6

C. Variable Type: Refresh Variable

D. Order and link variable to other package objects as desired

8packagediagram

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:

9filtercode

– 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)

9interface

Step 10: Regenerate ODI Scenario

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 setup 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 cool feature!

DRM Tutorial: Bulk Inserting DRM Users

Disclaimer: Not recommended / endorsed by Oracle.  Follow these steps at your own risk.

Note: The below steps were used for an environment using MS SQL Server.

Recently, I was tasked with adding Users to DRM. The list contained almost TWO HUNDRED users!  Using the below three windows to input the users one by one was not a viable option in my opinion, instead I was able to bulk insert the users into DRM. Work smart whenever possible!

DRM_Users01

DRM_Users02

DRM_Users03

Bulk inserting DRM Users involves modifying / writing to back-end database tables. In addition to READ access to the DRM relational tables, you will also need WRITE access to the following 6 tables:

  • RM_User
  • RM_User_Role
  • RM_Category_User
  • RM_Object_Access_Group
  • RM_Object_Access_Group_User
  • DAL_Sequence

In the following example, users will be added to DRM and assigned the “Workflow User” role.

 

Step A: Backup DB Tables

As a general rule of thumb, it is always a good idea to backup database tables before modifying them.

 

Step B: Acquire Pertinent Pieces of Information

Because we will be inserting records into tables that contain primary keys and foreign keys, it will be important to do a little detective work.

  1. Determine the i_role_id key for the role you will be assigning to the users.
    • Execute the following query:
      SELECT * FROM RM_Role
    • Results:
      RM_Role
    • i_role_id for “Workflow User” –> 102
  2. Determine the beginning number to assign to the i_user_id key for input into the RM_User table
    • Execute the following query:
      SELECT MAX(i_user_id)+1 FROM RM_User
    •  Results:
      DRM_2B
    • MAX(i_user_id) + 1 –> 362
  3. Determine the beginning number to assign to the i_access_group_id key for input into the RM_Object_Access_Group table
    • Execute the following query:
      SELECT MAX(i_access_group_id)+1 FROM RM_Object_Access_Group
    •  Results:
      DRM_2C
    • MAX(i_access_group_id) + 1 –> 316

 

 Step C: Create data files for import into the following 5 tables

DRM_Users04I chose to create the data files for each table with specific fields in separate tabs within Excel, as shown below:

DRM_Users05

  1. Populating RM_User dataDRM_4A
    • Not ALL fields in the RM_User table are required for each user added. I got away with only inputting data into the above 9 fields.
    • The number sequence for the i_user_id field will begin with the value derived in Step B2 –> MAX(i_user_id) + 1 –> 362
  2. Populating RM_Category_User data
    DRM_4B

    • In the environment I was working with, i_category_id = -1 represents System and the access level required for the users is Read. If different categories and access levels are required for your purposes, adjust the data to suit your needs.
    • The number sequence for the i_user_id field will begin with the value derived in Step B2 –> MAX(i_user_id) + 1 –> 362
  3. Populating RM_Object_Access_Group data
    RM_Object_Access_Group

    • The number sequence for the i_access_group_id field will begin with the value derived in Step B3 –> MAX(i_access_group_id) + 1 –> 316.
  4. Populating RM_Object_Access_Group_User data
    RM_Object_Access_Group_User 

    • The number sequence for the i_access_group_id field will begin with the value derived in Step B3 –> MAX(i_access_group_id) + 1 –> 316
    • The number sequence for the i_user_id field will begin with the value derived in Step B2 –> MAX(i_user_id) + 1 –> 362
  5. Populating RM_User_Role data
    RM_User_Role

    • The number sequence for the i_user_id field will begin with the value derived in Step B2 –> MAX(i_user_id) + 1 –> 362
    • The i_role_id will be set for the value derived in Step B1 –> i_role_id for “Workflow User” –> 102

Step D: Import / Insert the prepared user data from prior Step into the DRM tables

I leveraged the Microsoft SQL Server Management Studio Import and Export Wizard.

  1. Import the data for the RM_User and RM_Object_Access_Group tables first.
  2. Import the data for the remaining 3 tables:
    • RM_User_Role
    • RM_Category_User
    • RM_Object_Access_Group_User

 

Step E: Reset Database Sequence Numbers  

  1. Determine the max i_user_id key in the RM_User table
    • Execute the following query:
      SELECT MAX(i_user_id) FROM RM_User
    • Results:
      371
    • MAX(i_user_id) –> 371
  2. Determine the max i_access_group_id key in the RM_Object_Access_Group table
    • Execute the following query:
      SELECT MAX(i_access_group_id) FROM RM_Object_Access_Group
    • Results:
      371 (2)
    • MAX(i_access_group_id) –> 325
  3. Execute the following update statements:
    • UPDATE DAL_SEQUENCE
      SET i_id = 371
      WHERE c_key = ‘i_user_id’
      and c_primary_table = ‘RM_User’
    • UPDATE DAL_SEQUENCE
      SET i_id = 325
      WHERE c_key = ‘i_access_group_id’
      and c_primary_table = ‘RM_Object_Access_Group’

 

Step F: Restart Oracle DRM Service
DRMService

 

Step G: Verify Users Were Added Successfully
UserRoleConfirmation


Step H: Confirm Users Roles Associated to Users Are Correct
 RoleConfir