Direct Data Load - using outlineload utility to load data

Although I am not sure if this was available in Planning 11.1.2.1, but Hyperion Planning 11.1.2.2's outlineload utility does come with a functionality of loading data (You read it right !!)

There are two options Planning provides :
1. Either use the .csv data file to load the data using Outlineload.sh, this means that data will be loaded immediately and Administrators will have to ensure that Planning and Essbase metadata are in sync (refresh has been done)
2. Use the .csv file along with the utility which will create a .txt file of the same and a .rul (rule) file which can be loaded using the Essbase administration console. This means that you can create a data file when you have it ready and load it when the database has been refreshed at a fixed interval.

Needless to say, you can automate the whole damn thing !!!

Some basic points you should remember when using the data load utility

DIRECT_DATA_LOAD : This is the parameter you can either set to TRUE or FALSE in the System Properties of the application.
if TRUE :  .csv file is loaded directly, see option 1 written above
if FALSE : .csv file is converted to a text file and a corresponding rule file is created, the path where these two files are created is specified in the DATA_LOAD_FILE_PATH system property

DATA_LOAD_FILE_PATH  : This is the file location you need to specify if you choose set  DIRECT_DATA_LOAD to false


The csv file format : Imagine pushing data in Essbase using Excel addin having a Rows of a Dimension, Columns of another dimension and Point of view dimensions above it, the header of csv would be

DimensionInRows,MembersInColumnsSeparatedByComma,Point-of-View,Data Load Cube Name

Account,Jan,Point-of-View,Data Load Cube Name
acct1,12,”Local,ent1,Current,Ver1,FY08”,Plan1

or you can specify the Operation and data load cube name by putting it in between the first two column header

DimensionInRows,Operation,Data Load Cube Name, MemberNamesInColumns,Point-of-View

Entity,Operation,Data Load Cube Name,aUnspec,aSmart,aDate,aText,Point-of-View
e1, ,Plan1,77,smart1,12-22-2008,textValue,"USD,Jan,Current, BUVersion_1, FY07"

If you dont want the 'row' column at all, like a simple 1 dimensional table where you have 'value' column you will have to specify the /TR option in the OutlineLoad command, the Header would then be

Value,Driver Member,Point-of-View,Data Load Cube Name

Value,Driver Member,Point-of-View,Data Load Cube Name
14,a1,"Jan,Local,e1,Current,Version1,FY08",Plan1
sl1_value2,a2,"Jan,Local,e1,Current,Version1,FY08",Plan1 

and the command would be 

OutlineLoad /A:acpt1 /U:admin /M /I:c:\outline1data.csv /TR /L:c:/OutlineLogs/outlineLoad.log /X:c:/OutlineLogs/outlineLoad.exc 

 Note how PoV members are enclosed in double quotes and use exact same syntax when creating the file

How to do ??
For policy reasons, can't put the screenshots :(
Will see if I can do it locally on another machine.

For now I will paste the reference link
Search 'Loading Data' in it
http://docs.oracle.com/cd/E17236_01/epm.1112/hp_admin_11122/frameset.htm?launch.html.

Cheers !!

EPM 11.1.2.2 Installation : Hands on experiences

For Planning and Essbase learners, first things first, dont expect it run good on a 32 bit, 4 GB RAM laptop. Essbase Server and EAS will work just fine. Beginners who want to make their hands dirty with Planning on their basic laptops might want to install the good old 11.1.1.X.

While things have improved drastically on installation front, the new EPM system demands some good configuration server (as always). A basic installation including EPMA, CALC Manager, Planning, Essbase Server and EAS consumes around 5 gigs of RAM that too when no user except the admin is logged in.
This might tempt people to go for a 8 gb RAM, 64 bit dual core machine just to try it out. The best part is installation works smoothly but start creating a classic application on that and you will come to know your investment has gone wrong yet again. It works, but its slow.
Read the documentation carefully and just incase you want to learn Planning, I suggest you have two machines, one which hosts Essbase Server and EAS and the other hosting Planning.

Now, you will find most of those new features of EPM 11.1.2.2 all over the net, I will put down a few things I noticed while installing

- Unzipping the first 7 folders will suffice for basic installation of EPMA, CALC Manager, Planning, Essbase Server and EAS, but wont have OHS.
- No 'Install by tier' option, have to be clever enough to understand installation components now :)
- EPM instance name need not be different on different machines, use the same one
- If OHS is not used, you can deploy everything using the inbuilt Weblogic server on port 9000 and have one service for everything, real PITA for stopping individual services
- If IIS is used, it deploys everything on port 80 by default
- Import_export folder location specified on runtime, can be a shared folder on remote machine too
- Admin name can be changed, so it wont conflict with your company's active directory


Some important things
IIS component is needed for EPMA and make sure have it enabled and running or installation wont proceed.
How to do it ?
Go to Control Panel->Programs and Features -> Turn Windows features On or Off (on the left panel)
a window will open, in that search for 'Internet Information Services' and check it. You might want to check 'Microsoft .NET Framework' too just in case. This will start IIS, check if it is running by searching IIS in Start button -> search for programs and files, if it working you will be able to see IIS console and Website folder in it

The most important thing is to run the installer and configurator as Administrator, I had a bad experience where I missed it and got an error 'Service failed to deploy' while configuration, result ? uninstalling it failed, had to format the drive again.

To summarize, the installation and configuration is quite smooth now, and its easy to understand for novice too. The Web 2.0 thing is good to use in Workspace and Planning, the buttons and 'dockability' of some components are cool. The scrollable things scroll smoothly and dont feel as if there is some rust in the scroll bar. The overall feel is easy on the eyes, great going Oracle !!


Inside Story Though not confirmed yet, the configuration will undergo minor changes in upcoming 11.1.2.2 patch where you wont have to run the configurator again and again for seperate databases for each components. Like you select Foundation, Planning and Essbase while configuring and it will prompt you for new database schema for each component. Lets wait and watch !!   Sorry I am too lazy to post some snapshots here, why dont you try it out yourself ?? ;)   Cheers !!

The Nerdy stuff by Craig Stewart : Duplicating Model Structures of different technologies

Something interesting I found on one of those cool blogs of ODI, its goes deeper to explain some unexplored buttons and features of ODI11g.

What if you have reversed a model of MySQL with lots of tables and now you have a Oracle Schema in which you want the exact same model structure of MySQL to be replicated in it ?
Read how Craig Stewart does it here

http://odiexperts.com/using-one-of-odis-lesser-known-secrets-the-common-format-designer-cfd-by-craig-stewart/

Worked for me, and thats a cool thing to tell your clients just in case they want to migrate to a new database, aint it ?

Craig has done a brilliant job by recording his work and posting it on the net. Here is the link where you can get yourself some good videos on ODI
http://www.business-intelligence-quotient.com/?p=933

Cheers !!