In this blog, I would be talking about using the Planning load utility to load text data into a Hyperion Planning application. Now, in one of my previous blogs, I had explored how to manipulate the backend tables to do this. Using the outline load utility is a simpler way since once you get the format up and running, you can basically generate all the extracts as you want. (My requirement was that we were trying to create a spreadsheet view of the data and since the intersections/POV remain the same and only the Account member and associated data keeps on changing, using an ETL tool to generate and load this file is a simpler and cleaner approach)
The below form shows the data/intersection where I want to load the data.
Now, rather than preparing a flat file and loading the data, I prefer to use reverse engineering. Reasoning being that if the extract comes out in a specific format, probability supports that the same engine should load a suitably modified file import back into the system.
The next snapshot shows me extracting data from the planning application using the utility.
As shown in the above snapshot, I specify the Plan Type, Accounts in Rows and Columns should have Entity. Then I specify the POV that I want to extract…
When I run the extract, I get a warning and fails as shown in the below snapshot…
The error says that “The density for Driver dimension for a specified plan type must be dense.” Since Entity is sparse, it errors out. Driver dimension is the one that comes along in the columns.
I now update the format as shown in the below snapshot, by putting Period in columns and moving Entity to the POV.
When I run the utility, it runs the extract correctly as shown in the below snapshot.
The format of the file is as shown in the below snapshot.
The extract is made of up four logical columns:
I now run the utility data import utility with parameters as shown in the below snapshot.
Observe that “Dimension to Import” is equivalent to the member you specified on Rows and the “Driver Member Specification” is equal to the member you specified on column.
The value is reflected correctly as shown in the below user form.
Thus, a simple way to load data into Planning, especially text data since once you get the format right, it is just a matter of manipulating the fields you require.
The below form shows the data/intersection where I want to load the data.
Now, rather than preparing a flat file and loading the data, I prefer to use reverse engineering. Reasoning being that if the extract comes out in a specific format, probability supports that the same engine should load a suitably modified file import back into the system.
The next snapshot shows me extracting data from the planning application using the utility.
As shown in the above snapshot, I specify the Plan Type, Accounts in Rows and Columns should have Entity. Then I specify the POV that I want to extract…
When I run the extract, I get a warning and fails as shown in the below snapshot…
The error says that “The density for Driver dimension for a specified plan type must be dense.” Since Entity is sparse, it errors out. Driver dimension is the one that comes along in the columns.
I now update the format as shown in the below snapshot, by putting Period in columns and moving Entity to the POV.
When I run the utility, it runs the extract correctly as shown in the below snapshot.
The format of the file is as shown in the below snapshot.
- The column 1 specifies the members you specified on the Row.
- The column 2 specifies the members you specified on the column
- Column 3 represents the POV of the data.
- Column 4 represents the Plan type from which data was extracted.
I now update the comments for BegBalance as shown in the below snapshot.
I now run the utility data import utility with parameters as shown in the below snapshot.
Observe that “Dimension to Import” is equivalent to the member you specified on Rows and the “Driver Member Specification” is equal to the member you specified on column.
On running the utility, the file is successfully loaded.
The value is reflected correctly as shown in the below user form.
Thus, a simple way to load data into Planning, especially text data since once you get the format right, it is just a matter of manipulating the fields you require.
P.S. : I used Oracle Data Integrator to generate the file from a relational system. It makes for a very clean solution and once you have a sample format ready, it is just a job of mapping the fields and getting the remaining columns using plain old SQL. As they say, beauty lies in simplicity…
No comments:
Post a Comment