Managing data load in FDMEE using Open Interface table

In this blog, I will be talking about loading data into an EPM application using the open interface table. The open interface table is basically used when you want to move data from a source system that is not traditionally supported by FDMEE. For example, if I have a source system which is say Postgres database and I want to push data into a Planning application, say FINREP using FDMEE, the open interface table would be the way to go about this. The only catch with using the open interface table is that it is the users’ responsibility to populate the table. This is basically the FDMEE asking users to take the Unix way. If you can populate it, you can load it.

Now in this demo, I am going to show it is within the FDMEE schema.

This is the table that I have created to represent my source data. Two dimension columns entity and account followed with 12 columns for January to December data.

Inserting data into the table is shown below snapshot and the next one shows the table populated with data. 

The target for the data load is a table in the FDM schema called AIF_OPEN_INTERFACE. The columns of the table is as shown in the below table.

The important columns in the AIF_OPEN_INTERFACE table are as follows: -

• Batch_Name(This is important when we define the data load rule file)
• Period,
• Period_Num
• Year
• Amount

Your metadata needs to be mapped to columns from COL01 to COL30. So if you have say 10 dimension, excluding period and year, then COL01 is mapped to first dimension, COL02 is mapped to second dimension and so on.

Since in this demo, I have two columns, the sample extract will look like something as shown in the next select statement run against the source table.

The next statement shows me selecting three months of data from the source table using a UNION query.

The next section shows me populating the AIF_OPEN_INTERFACE table using the UNION select query run against the source table.   

The contents of the AIF_OPEN_INTERFACE table after doing the insert is as shown in the next couple of snapshots.

So now we have populated the AIF_OPEN_INTERFACE table with data. Now let us look at moving this data into the Planning application using FDMEE.

The first thing that we have to do is define the Source system for the Interface table. This is shown in the below snapshot where I have created a source system of the type “Others” with the name as “FinRep_Open_InterfaceTable”. The ODI context code is set to GLOBAL since I am using a default install.

 

The next thing that you need to do is define the Source adapter for this source system. If you go to the “Source adaptor” tab you will see that there are two source adaptors defined. One for JDEGL and another is the Open Interface Adaptor. Click on the Open Interface Adaptor and hit the copy button to duplicate the Open Interface Adaptor.

Give a name to the adaptor as shown in the next snapshot. I have given a name called “Finrep_OI” for the new source adaptor.

Once the new adaptor has been created, update the adaptor name to replace the default value of the Open Interface Adaptor. I have updated the adaptor name called “FINREP_OPEN_SOURCE” as shown in the next snapshot.

Once this is done, I am going to create a new import format to import data from the AIF_OPEN_INTERFACE table into the Planning application FINREP. This is shown in the next snapshot.

Observe the values of Source and Source Adaptor in the above snapshot. This is the source and source adaptor that we have created above.

The next snapshots show me mapping the dimensions from the AIF_OPEN_INTERFACE table to the Planning target dimension. My first column is mapped to entity and the second is mapped account. The version is not mapped at all.

 

Once you have created the import format and saved it, you need to click on the Regenerate ODI Scenario as shown in the next snapshot. 

The next snapshot shows that the ODI scenario has been successfully generated.

Next thing that we need to define the Source Period mappings. This basically maps the period for the source system. This is shown in the next snapshot.

Observe that there is a Calendar name defined for the Source system.

Most of the setup is now complete. We now move to the data load workbench. We define a data load rule for importing the data into the application. This is shown in the next couple of snapshots.

 

By default the Period Mapping Type would be “Explicit” and you would have to choose a Calendar for the mapping type. 

Since we have defined a mapping for the Source system I will choose it from the Calendar selection.

The Data Load rule after defining the calendars and period mapping type is as shown in the next snapshot. Also observe the “Batch Name” and “Delete data After Import” field in the below snapshot.

Batch name is “FINREP_OPEN_SOURCE” which we used for populating the data when we moved from the source table to AIF_OPEN_INTERFACE table. The Delete data after import flag is a Boolean value that decides if the data remains in the AIF_OPEN_INTERFACE table or is deleted.

Now, I define default mappings for moving data from Source to Target system in data mapping. On clicking Import, you will observe that the data is successfully imported and validated for Mar-16 as shown in the below snapshot.

The next snapshot shows me exporting the data into the Planning application for FINREP for March 2016.

 The above snapshot shows that the data has been exported to the Planning application successfully.

The next snapshot shows the Planning form with data 150 shown for March 2016. This indicates that the data was pushed successfully into the application from the Open Interface Adaptor.

A look at the AIF_OPEN_INTERFACE table after pushing the data for March 2016 is shown below. Observe that the data for March has been deleted whereas the data for January 2016 and February 2016 is still as is.