File-based Data Load Rules - loading data to range of periods using multiple files

Before starting with this new post I would like to share with you my thoughts.
I have been thinking about the blog in the last weeks. I noticed that I was only focusing on Classic FDM users and not in new ones. For this reason I will try to keep two different streams that may be useful for both.
For new users we will start with file-based data load into Hyperion Financial Management (HFM). In that way we will go through FDMEE basics.

So going back to the current post... how many of you had the requirement of loading historical data from any source (Hyperion Enterprise, old FDM application, etc.)? or loading multiple periods for Budget process? or simply load range of periods using multiple files? Although there are different solutions based on requirements, the common objective is to keep solution simple. That happened in FDM, and therefore does in FDMEE.
We must have into consideration many factors before deciding the solution:

• Are we going to load multiple periods for single or multiple locations?
• Are we going to load multiple periods for single or multiple categories?
• Do we have one data file with multiple periods or multiple files with single periods?
• Do we want to schedule the process?
• Do we want full automation?
• ...

 Let´s suppose our requirements are already defined as:

1. We will be loading data for one single location
2. We will be loading data for one single category
3. We will have multiple files with single periods
4. Process will be executed by the end user

With these requirements, most of you are thinking in the following solutions:

• Using Batch Loader ... new batch type "Open Batch" is no available in FDMEE 11.1.2.3.100
• Using Multi-load files ... have not been implemented yet. Hopefully will be in next PSU

Is that all? No. Luckily we have another option: using Data Load Rules for loading data to period of ranges from multiple files. And this post will be focused on that solution.

Note: This post will be focused on file-based data loads (some of the information and options presented may be different when importing data from other source systems like ERPs and Open Interface Adapter)

Let´s start with brief introduction to Data Load Rules (DLRs)

Data Load Rules
Data Load Rules are defined to extract data from source systems (files in our case) and push it into the target application.

• DLRs are defined at location level. Each location has its own set of DLRs.
• A DLR is defined for one specific Category. You cannot change it once the DLR is executed.
• ODI will process all DLRs. Therefore DLRs can be executed in two modes: online and offline (background). With this feature we don´t have to wait until a DLR ends and can run other tasks in parallel even executing another DLR :-)
• In Classic FDM, we had 3 components in the POV (Location, Period, and Category. In FDMEE, we have an extra one: DLR.

Running Data Load Rules
There are different ways of executing a DLR:

1. From the Data Load Rule screen (Execute Button)
2. From Data Load Workbench (Import Link)
3. Executing a Batch
4. Running a Batch Script

When the user wants to load data he can chose to load it to a single period or a range of periods. 

• Data Load Workbench (option 2)
• DLR is executed for the current POV (so single period)
• Data Load Rule screen (option 1)
• Similar to Batch Loader but for current POV (no user interaction if you wish)
• DLR can be executed for single period (Start Period = End Period) or range of periods
• Can configure the process level (import only, export only, recalculate, import + export...)
• Can configure import mode (append/replace) and export mode (depends on the target application type)

If you take a look to title of this post you will notice that we are interested in loading multiple periods in one shot. Therefore we will go through option 1.

Import from Source: import data into staging table TDATASEG_T, run Logic Accounts (optional), and run Mappings. Converted data is stored in table TDATASEG. This option should be selected if data is imported first time or if source data has changed. When this option is selected Recalculate is automatically checked in order to recalculate logic accounts and mappings.

Recalculate: if you change logic accounts and/or mappings but source data remains the same then you don't need to import data again. You can check recalculate option to perform these tasks.

Export to Source: exports data from table TDATASEG to the target application. You can check this option together with import so data is imported and exported directly to the target application in one shot.

Configuring Data Load Rules for File-based data load (Multiple Periods)

Before starting, we need to have some points clear:

• This feature is not the same as multi-load files which will be available in next PSUs.
• We will have one file for each period being loaded.
• All files will contain data for the same category.
• We can only load full ranges. Therefore if we define Start Period to be January 2006 and End Period to be March 2006, files for the full range (January, February, and March) must exist. Otherwise the data load will fail.

When we create a new DLR we have to set the following fields:

Name: name of the data load rule. I suggest to put something meaningful.

Description: description of the data load rule.

Category: as we already said, a DLR is defined for one specific category which is previously defined in Category Mapping screen.

File Name and Directory: we have three options to setup these fields.

• Enter or select file name from application root folder. If file is selected field Directory will be automatically set as the relative path to the application root folder. If this option is used then users will not have to select the source file when the DLR is executed (This option was not available in Classic FDM)
• Leave File Name and Directory as blank. If this option is used then users will have to select the source file when the DLR is executed (Same as Classic FDM)
• Use File Name, Directory, File Name Suffix Type, and Period Key Date Format (optional) in combination. If this option is used then FDMEE will construct the file name at run-time. This option is the one we have to use if we want to execute the DLR so multiple files for different periods are processed.

To load multiple periods, we create one file for each single period and append the period name or period key to the file name. Prefix of the file name will be the same for each file and period names or period keys must be valid. We can see all period names defined in the Period Mapping screen:

Our prefix will be "EastSales_" so source files will be as follows when using Period Name as suffix:

When the DLR is executed for a range of periods, the process constructs the file name for each period and uploads data to the current POV.

For that purpose, we have to setup the DLR with prefix and suffix values:

File names will be constructed as: EastSales_PeriodName.txt

Assuming our Location is correctly configured with import format we just have to run the DLR from Data Load Rule screen. If we run it from Data Load Workbench only the period in the current POV will be processed.

We select range January 2006 - December 2006:

We get two identifiers every time we execute a DLR:

Process Id: will identify the process. All files generated by FDMEE (log, .DAT, etc.) will be identified by the process Id.

Extract Id: it corresponds with the ODI session executed by FDMEE.

How can we see execution details? We can navigate to Process Details screen or use ODI Operator/Console to see all steps executed. We can use Query by Search functionality to filter by any process field. In the image below we search by Process Id:

The green tick shows the process was executed successfully :-) and we can see all details when we select the row:

From image above we can work out the following:

• 1st Step: import all files (period by period... January 2006, February 2006..., December 2006)
• 2nd Step: Transform all data (period by period... January 2006, February 2006..., December 2006). Data mappings are validated.
• 3rd Step: Validate intersections. Intersection Validation Check is executed if it is enabled.
• 4th Step: Export all data (all periods at once). One .DAT file is generated with all periods.

• 5th Step: Load the .DAT file generated.
• 6th Step: Create Drill Regions (a .DRL file is generated and loaded)

• 7th Step: Consolidate HFM Data. There must be a bug as we don't have "Enable Consolidation" set to Yes. The message is shown even if consolidation was not performed.

Loading Multiple Periods using Period Keys

Period keys can be used as suffix instead of Period Names. You can setup Period key format as needed (y,M,d) 

Now it is your turn to give a try...

  

Notes:

• All files for the range selected must exist as import step are executed file by file before validating and exporting all data. If one file does not exist the process will fail.
• File are imported following period range order even if process steps shows periods in different order. You can check the process log (outbox\log)

That´s all my folks!

Share this post