Loading data for multiple time period into Essbase application using FDMEE

In this blog, I would be talking about loading data for multiple time period from a flat file into Essbase using FDMEE. In my earlier blog, I had talked about the push of data for a single time period into Essbase using FDMEE. Now, I have to be honest here. Loading multiple time periods data is very confusing in the start. This is because FDMEE does not allow a multi period file to be opened in the data load workbench. However, with some practice, it can be very easy to work with. 

Now this is the file that we want to load into the Essbase application.

The field are mapped as follows:-

Account, Entity, Version, Jan_data, Feb_data, Mar_data, Apr_data, May_data, Jun_data,…. Dec_data

(A lot of source systems send data extracts in this format. It makes sense to extract data in this way. A single file with data for a whole year rather than individual extracts for one month of a year.)

I now click on the “Add” button in the “Import Format” tab to add the new import format for the multi-period load in FDMEE.

I now define the name of the import format, description, source type and target as shown in the next snapshot.

Note that, I have set the file type as “Delimited” and set the delimiter as “comma”.

In the next snapshot, I am defining the mappings for the import file. So I have mapped the first field as Account, second field as Entity and third field as Version. 

Observe the mapping for the Amount target column. 

For field 4, I have defined an expression for the columns as “columns=4, 15”

It basically means that the amount column spans from column numbers 4 to 15.

Now, I create a data load rule using the import format that is defined above. The snapshot is as shown below.

Observe that the file type is set to “Multi-period text file contiguous period” and the import format is set to the one created above called “Sales_Yearly_Extract”

The mappings are one-to-one LIKE mappings that basically say, whatever comes from the source send it as is to the target. The mapping are defined for Account, Entity and Version. 

Now, we go to execute the data load rule. As I mentioned earlier, the rule cannot be opened in the data load workbench.

I click on the execute button in data load rule tab and the below popup shows up.

The options I choose are as follows:-

·         Import from Source, Recalculate, Export to Target and Execute Check.
·         The start period is set to Jan-15 and the end period is set to Dec-15.
·         Import Mode is Replace and the Export mode is set to “Store data”

Process details status is as shown in the below snapshot.

I now run a report script against the Sales database as shown in the below snapshot. If you check the values you will observe that the data matches from the file.

P.S :- Since multi period files cannot be opened in the data load workbench... I generally use divide-and-conquer to fix the problem. I map only the first data column as Amount and ensure that the data validates successfully...Once done, I update the import format. works like a charm...

I wanted to talk about the process log file from FDMEE as well...Another day, another time...