Planning 11.1.2.2.300 Outline Load Utility Enhancements Part 3

In the previous two parts I went through the majority of the new functionality available with the Outline Load Utility, there is one remaining piece which I will quickly go through today and that is extracting data to a flat file.

The loading of meta/data can now be achieved from a relational source but unfortunately the new functionality of extracting data can only be to a target flat file, I am sure in future releases a relational target option will be added.

In most cases you will still probably extract data directly from Essbase as it is the optimal solution but if you want to extract text, Smart List and date data then it is now possible using the utility.

I am not going to cover any of the new Outline Load Utility properties again so if you have not done so it is probably worth having a read of the last couple of blogs.

In the last part I loaded the following data set to planning.

and the data form design:

The account member data types are:
Full Name – text member
Role – Smart List
Start Date – Date
Salary – Currency
Bonus – percentage

Now I will reverse the process and extract the data to a flat file.

There are a couple of parameters that I have not covered previously and will be required to extract data.

/ED: – specifies the output path and filename to export to
e.g. /ED:E:/PLAN_SCRIPTS/Extract/dataExport

Note: data into files of the form output_file.1-1.csv through output_file.n-n.csv where n is the number of files generated.

/EDD: - specifies the data that is to be exported and is in the format of
“Data Load member(s)”, “Driver Dimension member(s)”, “Point of View”, “Plan Type name”

So from my example form above the format would be
/EDD: EMP01,"Full Name, Role, Start Date, Salary, Bonus","FY12,Actual,Working,Local,Ignore,Begbalance",Consol

These parameters can be added to a properties file

Now all that is left is to run the utility from command line passing in the required parameters.

E:\Oracle\Middleware\user_projects\epmsystem1\Planning\planning1\OutlineLoad.cmd -f:E:\PLAN_SCRIPTS\password.txt  /CP:E:\PLAN_SCRIPTS\dataextract.properties

In my example I only required the password file and the property file as all the parameters were set in the property file.

Executing the command line produces the following output log

Property file arguments: /DF:DD-MM-YYYY /X:E:/PLAN_SCRIPTS/Logs/dataExp.err /A:PLANDEMO /ED:E:/PLAN_SCRIPTS/Extract/dataExport /S:FUSION11 /L:E:/PLAN_SCRIPTS/Logs/dataExp.log /U:epmadmin /EDD:EMP01,"Full Name, Role, Start Date, Salary, Bonus","FY12,Actual,Working,Local,Ignore,Begbalance",Consol

Command line arguments: /CP:E:\PLAN_SCRIPTS\dataextract.properties

Submitted (merged) command line: /DF:DD-MM-YYYY /X:E:/PLAN_SCRIPTS/Logs/dataExp.err /A:PLANDEMO /ED:E:/PLAN_SCRIPTS/Extract/dataExport /S:FUSION11 /L:E:/PLAN_SCRIPTS/Logs/dataExp.log /U:epmadmin /EDD:EMP01,"Full Name, Role, Start Date, Salary, Bonus","FY12,Actual,Working,Local,Ignore,Begbalance",Consol

Successfully logged into "PLANDEMO" application, Release 11.122, Adapter Interface Version 5, Workforce supported and not enabled, CapEx not supported and not enabled, CSS Version 3

[Sun Oct 28 14:47:50 GMT 2012]A cube refresh operation will not be performed.

[Sun Oct 28 14:47:50 GMT 2012]Create security filters operation will not be performed.

[Sun Oct 28 14:47:50 GMT 2012]Examine the Essbase log files for status if Essbase data was loaded.

[Sun Oct 28 14:47:50 GMT 2012]Planning data export operation finished. 1 data export file was written: E:/PLAN_SCRIPTS/Extract/dataExport.1-1.csv, 1 data export record was written (total all files).

As you can see from the log one export file was created called dataExport.1-1.csv and one record was exported.

Just like with loading data it is possible to use planning member functions in the /EDD definition which means you can include
Ilvl0Descendants(member), Children(member),Ancestors(member) etc

Running the utility again should now export the child members of Employees and Descendants of Year.

Well that finally covers all the new functionality with 11.1.1.2.300 release of the Outline Load utility, apologies for taking so long to get it completed but time has been very limited lately.