The Situation:
A client has purchased the Workforce for Planning module and would like to feed their HR related data directly into the system with as little amount of user intervention as possible. This information is primarily made up of text related items, such as Employee Name, Title, Job Code and other text and value data.
The Constraints:
- The client is using an EPMA Planning application 11.1.2.2.301
- The client can only receive a text file and is not using an ETL tool which could reference the data via a table or other data source mechanism.
- The client would like an automated process which will load these values on a monthly basis to account for any changes in their personnel.
The Solution:
Oracle has created an Outline Load utility which will process a source file and load all the text and numerical values to Planning. The text values will be assigned a numerical value which are then stored in Essbase.
- For Example: The job title of Sr. Business Person is displayed as text in Planning but as a value, say 12345, in Essbase. (Please note, the particulars the table which holds the translation of text to value is not covered in this blog.)
Note: There are various settings at the application level which define the input. We opted to not set Driver Members at the application but rather to define them in the source file. We felt this would require less maintenance and one less thing for the client to have to manage as their business and applications evolve.
Disclaimer: This blog is meant to be a real world example of using the Outline load utility. Oracle has documented the utility, its features and methodologies. This is not an exhaustive list of functionality but rather one method.
How:
The Outline load utility is found in the planning1 directory. In this case it was located at:
D:\oracle\Middleware\user_projects\epmsystem1\Planning\planning1\OutlineLoad
Source File Setup:
The text file is comma separated and must have the following header. The order of these items does not matter as long as both the header and the records coincide.
Point-of-View,Driver Member,Value,Data Load Cube Name
Where:
- Point-of-View = “Entity,BusinessUnit,CostCenter,Currency,Project,Scenario,Version,Year,Period,Employee”
Because the file is comma delimited, the quotation marks are required. They distinguish which items are part of the point-of-view and which are the other attributes within the file.
- Driver Member = Account dimension member
Though this can be any dimension member, ours was in the account dimension.
- Data = text value to be entered (e.g. Sr. Business Person)
- Data Load Cube Name = the name of the essbase cube the data will be loaded to (e.g. Wrkforce)
Source Data Load File Example:
Point-of-View,Driver Member,Value,Data Load Cube Name
“EN_10,BU_1100,CC_11599,USD,PR_00000,Budget,First Pass,FY11,BegBalance,EE_1158”,Hire Date,01-01-2000,Wrkforce
The above example would be two lines in the source file and is broken down as follows:
- Point-of-View =
“EN_10,BU_1100,CC_11599,USD,PR_00000,Budget,First Pass,FY11,BegBalance,EE_1158″
Again, the quotation marks are required and tell the utility that the items within the quotes make up the point of view for the following account member and data.
- Driver Member = Hire Date
We used several driver members in our load file (e.g. First Name, Last Name, Title, Job Code, Hire Date). These were various members of the Account dimension.
- Data = 01-01-2000
This particular example was for the Hire Date driver. The date is viewed as a text value and the date format must be defined in the load syntax, which is discussed further in the syntax portion.
- Data Load Cube Name = Wrkforce
Outline Load Utility Syntax:
The utility is executed manually via the command line. This syntax can be incorporated into a batch file which makes it easier to execute the same syntax or to setup a scheduled task to run the batch file at predefined time.
There are several attributes which can be utilized which are detailed in the Planning Admin Guide. I will go over the ones I used for our particular scenario.
To execute the utility you navigate to the folder containing the utility via the command prompt. This will require you to break out your old Dos commands (CD is change directory, D: would change the drive letter to D, etc.)
Outline Syntax
OutlineLoad -f:passwordfile /A:application /U:username /I:inputFileName /D:loadDimensionName /X:exceptionFileName /L:logfilename /M /TR /DF:dateformat
Remember not all of these attributes are required and can differ based upon your particular instance.
- -f = the location and name of the password file if you choose to use one. If you are calling the outline utility you will need a password file otherwise a password will have to be manually entered. (You can create a password file with the passwordencryption.cmd utility which is usually in the same folder as the outline load utility)
- /U = User name (user name must match the password in the password file)
- /I = Import file name and path to the file (I used a .txt file)
- /TR = Designates that the application does not have “Driver Members” defined in the application it self and that the Driver Members will be defined in the file. The file only has one data record per line
- /DF = Date Format (e.g. MM-DD-YYYY)
- /L = Log file name and location (can be anything)
- /X = Exceptions name and location (can be anything)
- /M = Generate fully qualified header records for loadable dimensions in the application.
- /N = Optional – used if you want to test the load and see what error may occur. Displays some error messages but others may only occur once the actual load is executed. (It is recommended to use the /N parameter when first executing the command to see what general errors you receive.)
Sample Syntax:
OutlineLoad -f:c:\EmployeeLoadFiles\passwordFile.txt /A:HypPlan2 /U:hypadmin /M I:c:\EmployeeLoadFiles\EmpDataLoad.txt /D:Employee /TR /DF:MM-DD-YYYY /L:c:\EmployeeLoadFiles\EmpDataLoadLog.txt /X:c:\EmployeeLoadFiles\EmpDataLoadExceptions.txt
The above was entered directly at the command line in the folder that holds the outlineload utility. Also, note the file locations for some of the files are on the c: drive. When defining a drive letter location do not capitalize it. I found that the capitalized letters should only be used with the attributes (e.g. /A or /TR).
The above sample command loaded the EmpDataLoad.txt (source input file as defined above) to the HypPlan2 application with the user name hypadmin and password held in the passwordfile.txt. Any dates located in the file are in the MM-DD-YY format, there are unique records in each row of the file (/M) and the Driver Members are defined in the file (/TR). Any exceptions and logging will be held in the EmpDataLoadExceptions.txt and EmpDataLoadLog.txt files respectively.
Creating A Batch File.
If you plan to execute the command on a regular basis it is quite easy to create a batch file. Simply adding the above syntax in conjunction with the Call command and the file location of the outline load utility will generate the file.
Sample ExecuteOutlineLoad.bat file
Open Notepad and paste the syntax.
call D:\oracle\Middleware\user_projects\epmsystem1\Planning\planning1\OutlineLoad
-f:c:\EmployeeLoadFiles\passwordFile.txt /A:HypPlan2 /U:hypadmin /M /I:c:\EmployeeLoadFiles\EmpDataLoad.txt /D:Employee /TR /DF:MM-DD-YYYY /L:c:\EmployeeLoadFiles\EmpDataLoadLog.txt /X:c:\EmployeeLoadFiles\EmpDataLoadExceptions.txt
The above syntax is the same syntax that we used from the command prompt but it also includes “call” and the file path for the outlineload utility followed by the appropriate attributes, file paths and file names.
call D:\oracle\Middleware\user_projects\epmsystem1\Planning\planning1\OutlineLoad
You can now instruct your users to execute the batch file as needed or add it to scheduled procedure application (e.g. Task Scheduler in windows)
