In the last blog I went through some of the new functionality with the Outline Load Utility and today I am going to continue and go through loading data from a relational source.
I am not going to cover any of the properties again so if you have not done so it is probably worth having a read of the last blog.
Loading data through the planning layer is great if you want to load text, Smart List and date data, if loading numeric data then personally I would stick to loading direct to essbase as it is much more efficient and has a lot of additional functionality.
In an earlier blog I went through the process of loading text data from a flat file using the Outline Load utility and to be honest the concept is nearly identical for loading from a relational source.
Anyway no need for anymore waffle so let’s get on with the process.
In my example I have created simple form to show employee information by entity and instead of entering the information manually I am going to load it from a relational table.
The account member data types are:
Full Name– text member
Role – Smart List
Start Date – Date
Salary – Currency
Bonus – Percentage
The evaluation order has been set in planning so the correct data types are displayed in the form.
The source data is by entity, year and account, the rest of the data is static so can be fixed when writing the query to load to the planning application.
Now if you have ever loaded data to planning then you have probably come across the data load settings that apply the data load dimension and the driver dimension and members
In my example the data load dimension will be the entity dimension and the driver dimension will be Account with the members “Full Name”, “Role”, “Start Date”, “Salary” and “Bonus”.
You can still use the data load settings method for loading from a relational source or use a new parameter /SDM which allows the information to be set from command line or using a property file.
So for my example instead of using the data load settings functionality in planning I can use
/SDM:Entity, “Full Name, Role, Start Date, Salary, Bonus”, Consol
This sets the data load dimension as Entity and the driver members as “Full Name”, “Role”, “Start Date”, “Salary”, “Bonus” and the third property is the plan type to apply these to.
Besides the data load dimension and driver members the “Point-of-View” and “Data Load Cube Name” also need to be provided in the relational query.
The “Point-of-View” is a comma separated list of all the remaining dimension members in the plan type and this is required so the intersection on where to load the data to is fully defined.
The remaining dimensions in my example application are Scenario, Version, Currency, Period, Segments and the members are all fixed as displayed in the form design:
The “Data Load Cube Name” is pretty obvious and is the plan type/essbase database name to load the data to which in my case is “Consol”.
A simple SQL query can be created to return the data in the correct format though it is worth noting the column names have to match the properties required for planning.
The query can be added to the command line or to a properties file which I will be using.
I covered most of the properties in the last blog so hopefully they should all make sense.
As there is a date member being loaded /DF should be used to define the date format and the available values are:
/DF:MM-DD-YYYY The date data type values on the source data load are assumed to be month-day-year. For example, 12-25-2011
/DF:DD-MM-YYYY The date data type values on the source data load are assumed to be day-month-year. For example, 25-12-2011
/DF:YYYY-MM-DD The date data type values on the source data load are assumed to be year-month-day. For example, 2011-12-25
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\dataload.properties
In my example I only required the password file and the property file as all the parameters were set in the property file.
The output log generated was:
No errors were generated in the log and all records loaded successfully.
Running the form again confirmed that all the data has been loaded in the correct format.
There is another parameter available which I have not mentioned:
/ICB:blockSpecification
This allows you to define an intersection in the essbase database to clear before the data is imported, this functionality has been included because the utility does not currently load #missing values and if values already exist they will not be overwritten.
So say the same data was loaded again but this time there was no Bonus data.
The Bonus member data will not be set to #missing and will still contain 10%
The format for the ICB parameter is similar to how the data is loaded so it requires the data load dimension member, the driver members, the point of view and the data load cube name.
/ICB:EMP01,"Full Name, Role, Start Date, Salary, Bonus","FY12,Working,Version,Actual,Local,BegBalance,Ignore",Consol
The /ICB values that I have used will clear out all the data in the form before it is loaded.
As the data is now cleared before the import this time the Bonus member contains the correct data.
From examining the essbase application log you will be able to view the clear data command that has been executed.
It is also possible to use planning member functions in the /ICB definition which means you can include Ilvl0Descendants(member), Children(member),Ancestors(member) etc
Well that about covers loading data from a relational source, there is one more new piece of functionality with the Outline Load utility and that is extracting data to a csv file which I will try and go over whenever I find the time.
I am not going to cover any of the properties again so if you have not done so it is probably worth having a read of the last blog.
Loading data through the planning layer is great if you want to load text, Smart List and date data, if loading numeric data then personally I would stick to loading direct to essbase as it is much more efficient and has a lot of additional functionality.
In an earlier blog I went through the process of loading text data from a flat file using the Outline Load utility and to be honest the concept is nearly identical for loading from a relational source.
Anyway no need for anymore waffle so let’s get on with the process.
In my example I have created simple form to show employee information by entity and instead of entering the information manually I am going to load it from a relational table.
The account member data types are:
Full Name– text member
Role – Smart List
Start Date – Date
Salary – Currency
Bonus – Percentage
The evaluation order has been set in planning so the correct data types are displayed in the form.
The source data is by entity, year and account, the rest of the data is static so can be fixed when writing the query to load to the planning application.
Now if you have ever loaded data to planning then you have probably come across the data load settings that apply the data load dimension and the driver dimension and members
In my example the data load dimension will be the entity dimension and the driver dimension will be Account with the members “Full Name”, “Role”, “Start Date”, “Salary” and “Bonus”.
You can still use the data load settings method for loading from a relational source or use a new parameter /SDM which allows the information to be set from command line or using a property file.
So for my example instead of using the data load settings functionality in planning I can use
/SDM:Entity, “Full Name, Role, Start Date, Salary, Bonus”, Consol
This sets the data load dimension as Entity and the driver members as “Full Name”, “Role”, “Start Date”, “Salary”, “Bonus” and the third property is the plan type to apply these to.
Besides the data load dimension and driver members the “Point-of-View” and “Data Load Cube Name” also need to be provided in the relational query.
The “Point-of-View” is a comma separated list of all the remaining dimension members in the plan type and this is required so the intersection on where to load the data to is fully defined.
The remaining dimensions in my example application are Scenario, Version, Currency, Period, Segments and the members are all fixed as displayed in the form design:
The “Data Load Cube Name” is pretty obvious and is the plan type/essbase database name to load the data to which in my case is “Consol”.
A simple SQL query can be created to return the data in the correct format though it is worth noting the column names have to match the properties required for planning.
The query can be added to the command line or to a properties file which I will be using.
I covered most of the properties in the last blog so hopefully they should all make sense.
As there is a date member being loaded /DF should be used to define the date format and the available values are:
/DF:MM-DD-YYYY The date data type values on the source data load are assumed to be month-day-year. For example, 12-25-2011
/DF:DD-MM-YYYY The date data type values on the source data load are assumed to be day-month-year. For example, 25-12-2011
/DF:YYYY-MM-DD The date data type values on the source data load are assumed to be year-month-day. For example, 2011-12-25
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\dataload.properties
In my example I only required the password file and the property file as all the parameters were set in the property file.
The output log generated was:
Property file arguments: /RIU:PLANSTAGE /D:Entity /RIR:jdbc:oracle:thin:@[fusion11]:1521:FUSION /DF:DD-MM-YYYY /RIP:TOzyauMwe2gtUQ9tjidf1Zq2pCA8iroN4i7HQxssFKUaogr16fi+WKmHFTD8NIIs /X:E:/PLAN_SCRIPTS/Logs/datald.err /RIQ:DATA_SQL /A:PLANDEMO /S:FUSION11 /L:E:/PLAN_SCRIPTS/Logs/datald.log /U:epmadmin /RIC:FUSION_CONN /SDM:Entity,"Full Name, Role, Start Date, Salary, Bonus", Consol /RID:oracle.jdbc.OracleDriver /IR:E:/PLAN_SCRIPTS/metaload.properties Command line arguments: /CP:E:\PLAN_SCRIPTS\dataload.properties Submitted (merged) command line: /RIU:PLANSTAGE /D:Entity /RIR:jdbc:oracle:thin:@[fusion11]:1521:FUSION /DF:DD-MM-YYYY /RIP:TOzyauMwe2gtUQ9tjidf1Zq2pCA8iroN4i7HQxssFKUaogr16fi+WKmHFTD8NIIs /X:E:/PLAN_SCRIPTS/Logs/datald.err /RIQ:DATA_SQL /A:PLANDEMO /S:FUSION11 /L:E:/PLAN_SCRIPTS/Logs/datald.log /U:epmadmin /RIC:FUSION_CONN /SDM:Entity,"Full Name, Role, Start Date, Salary, Bonus", Consol /RID:oracle.jdbc.OracleDriver /IR:E:/PLAN_SCRIPTS/metaload.properties 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 Sep 30 15:56:38 BST 2012]Setting Driver Members as specified with the /SDM switch. A query was located in the Command Properties File "E:\PLAN_SCRIPTS\dataload.properties" that corresponded to the key passed on the Input Query switch (/RIQ) "DATA_SQL" so it's corresponding value will be executed as a query: "SELECT Entity, full_name as "Full Name",role,to_char(start_date, 'dd-mm-yyyy') as "Start Date",Salary,Bonus/100 as "Bonus", 'FY' || substr(Year,3,2) || ',Actual,Working,Local,Ignore,BegBalance' as "Point-of-View",'Consol' as "Data Load Cube Name" FROM PLANDATA" Attempting to make input rdb connection with the following properties: catalog: FUSION_CONN, driver: oracle.jdbc.OracleDriver, url: jdbc:oracle:thin:@[fusion11]:1521:FUSION, userName: PLANSTAGE Source RDB "FUSION_CONN" on jdbc:oracle:thin:@[fusion11]:1521:FUSION connected to successfully. Connection to input RDB made successfully. [Sun Sep 30 15:56:38 BST 2012]Date format pattern "DD-MM-YYYY" specified for date data type loading. [Sun Sep 30 15:56:38 BST 2012]Header record fields: ENTITY, Full Name, ROLE, Start Date, SALARY, Bonus, Point-of-View, Data Load Cube Name [Sun Sep 30 15:56:38 BST 2012]Located and using "Entity" dimension for loading data in "PLANDEMO" application. [Sun Sep 30 15:56:39 BST 2012]Load dimension "Entity" has been unlocked successfully. [Sun Sep 30 15:56:39 BST 2012]A cube refresh operation will not be performed. [Sun Sep 30 15:56:39 BST 2012]Create security filters operation will not be performed. [Sun Sep 30 15:56:39 BST 2012]Examine the Essbase log files for status if Essbase data was loaded. [Sun Sep 30 15:56:39 BST 2012]Planning Outline data store load process finished. 2 data records were read, 2 data records were processed, 2 were accepted for loading (verify actual load with Essbase log files), 0 were rejected.
No errors were generated in the log and all records loaded successfully.
Running the form again confirmed that all the data has been loaded in the correct format.
There is another parameter available which I have not mentioned:
/ICB:blockSpecification
This allows you to define an intersection in the essbase database to clear before the data is imported, this functionality has been included because the utility does not currently load #missing values and if values already exist they will not be overwritten.
So say the same data was loaded again but this time there was no Bonus data.
The Bonus member data will not be set to #missing and will still contain 10%
The format for the ICB parameter is similar to how the data is loaded so it requires the data load dimension member, the driver members, the point of view and the data load cube name.
/ICB:EMP01,"Full Name, Role, Start Date, Salary, Bonus","FY12,Working,Version,Actual,Local,BegBalance,Ignore",Consol
The /ICB values that I have used will clear out all the data in the form before it is loaded.
As the data is now cleared before the import this time the Bonus member contains the correct data.
From examining the essbase application log you will be able to view the clear data command that has been executed.
It is also possible to use planning member functions in the /ICB definition which means you can include Ilvl0Descendants(member), Children(member),Ancestors(member) etc
Well that about covers loading data from a relational source, there is one more new piece of functionality with the Outline Load utility and that is extracting data to a csv file which I will try and go over whenever I find the time.
Interesting , i need to check this out.
ReplyDeleteDid you had a chance to see what is the performance impact between loading a file from flat file and relational data source?