Saturday, 31 December 2011

Planning - loading text member data update

A while back there was a post on the planning forum about loading text member data to a planning application using the Outline load utility, there was a reply on the post saying it was not possible in 11.1.2.x and an SR was raised and it had been confirmed as a bug. I knew it was possible in previous versions so I was going to test it out but never got round to it.

Then recently I received an email asking if it was possible to load text data using ODI to planning as once again an SR had been raised and it had been confirmed as a bug. I knew I had definitely loaded text data when working on a previous project but that was with planning

I did write a blog on using an alternative method to loading text data because that was before it was possible directly with the ODI planning adaptor, I think it wasn’t possible in the first releases of 11 but it certainly was possible in

I thought I had best test out both the Outline load utility and ODI to see if it is possible to load text member data to a planning application.

I first created an account member called TextMember1 with a data type of text.

I constructed a simple form so that I could view the results of load text data to the selected POV.

I set up the Data Load Settings in planning to match how I had set up the form with the Data Load Dimension as Period, Driver Dimension as Account with the member TextMember1

I made sure the Data Load Settings had definitely been correctly saved by running a SQL query against the planning applications relational tables.

I created a CSV file in the format required for the Outline load utility and populated it with the same POV details as the planning web form.

OutlineLoad /A:PLANSAMP /U:admin /M /I:textload.csv /D:Period /L:dataload.log /X:dataload.exc

The Outline load utility was run from command line using the load dimension set as Period to match the Data Load settings and CSV file.

The output log from the utility showed that 1 record was successfully processed and loaded.

The planning web form was run again and the text data was displayed successfully.

I know that you can also use the Outline utility to load data without having to set the Data Load setting in planning and specify the driver information directly in the source file so I thought I would give that a try as well.

I created and populated a new file using the Driver Member and Value column headings.

OutlineLoad /A:PLANSAMP /U:admin /M /I:textload2.csv /TR /L:dataload.log /X:dataload.exc

I successfully ran the utility again but this time removing the load dimension /D and used /TR which means the driver information is specified in the file.

The form displayed the newly loaded text data.

So no problems loading text data using the Outline load utility so time to move on to ODI, the email I received specified ODI with planning so that is what my first test would be with.

I reversed the planning application and checked the columns “Data Load Cube Name”, “TextMember1” and “Point-of-View” had been added to the Period Datastore.

I created a file Datastore against the CSV file I used with the Outline load utility.

I created a new interface with the CSV file as the source and the Period dimension as the target, the target columns were then mapped to the source.

The flow was set as LKM File to SQL, the memory engine as the staging area and then “IKM SQL to Hyperion Planning”

The interface executed successfully and the web form was reloaded to show the correct text data.

So no problems using ODI 10g how about ODI

I replicated everything I had created in ODI 10g but this time used Oracle as the staging area due a bug using the memory engine with planning.

The interface ran with no problems and once again the correct text data was shown in the planning web form.

All testing was successful so at least I can put my mind at rest on this subject.

1 comment:

aditya said...

Hi John,
Thanks for your blog!
I did this and it worked for me when i loaded for members with small maember name in the POV. But it is not loading if the POV text length is big... any workaround for this.. Thanks in Advance