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.

ODI 11.1.1.6 – Extracting Essbase attribute members bug + fix

Just another really quick update from me today, I have seen a few posts on the issue in the past and noticed there is an actual fix available now so I thought I would go through the problem and the fix.

The issue definitely exists on 11.1.1.6 and I believe it exists in unpatched 11.1.1.5 and 10g.

Say you want to extract the hierarchy from an attribute dimension from an essbase outline it certainly should be possible using the “LKM Hyperion Essbase METADATA to SQL”

In this example I will try and extract the population attribute hierarchy from everybody’s favourite Sample Basic.

If you reverse the Sample Basic database there will be a Datastore created for each attribute dimension with a number of property columns.

I created a simple interface which will extract the parent, child and alias members from the population attribute dimension and write these to a flat file.

Unfortunately when you run the interface it fails at the “Begin Essbase Metadata Extract” step with the following error:

org.apache.bsf.BSFException: exception from Jython:
Traceback (most recent call last):
  File "<string>", line 91, in <module>
    at com.hyperion.odi.essbase.ODIEssbaseMetaReader.validateSourceMetadata(Unknown Source)
    at com.hyperion.odi.essbase.ODIEssbaseMetaReader.validateExtractOptions(Unknown Source)
    at com.hyperion.odi.essbase.AbstractEssbaseReader.beginExtract(Unknown Source)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)

So that was the end of the road on 11.1.1.6 until a recent patch was made available

You can get more information and download patch 13093442 here.

Once download the patch can easily be applied using Opatch just make sure none of the ODI components are running before applying.

Basically the patch copies a new version of odihapp_essbase.jar to

\oracledi\client\jdev\extensions\oracle.odi.navigator\lib\
\oracledi.sdk\lib\

After applying the patch the interface can executed again.

This time it is much more promising as the interface complete successfully.

The target flat file is populated with the full attribute hierarchy even if it is in its usual unhelpful order.