What does Outline Load utility (OLU) offers?

As we all know that the Outline Load utility can be used to import metadata and data for standard & custom dimensions, attributes, UDAs, exchange rates, Smart Lists, and planning unit hierarchies from a flat file or a relational data source. 

You can also export metadata and data to a flat file or export metadata to a relational data source using the Outline Load utility

Here let me cover how to load metadata & data from a relational data source using the Outline Load utility

Loading the Metadata from relational data source to Hyperion Planning using OLU

1. Create a metedata.properties file

A sample .properties file is created as shown below which can connect to a external Relation data source along with the SQL to retrieve the needed metadata in the needed format with all the customized transformation you need, yes if you can't use ODI then this is awesome :-)

# /S:server where Planning is running

/S:Example-Planning-Server.com

# Application where you want to load the metadata

/A:Vision

# User used to load the metadata

/U:admin

# jdbc catalog - the Source Relational Schema

/RIC:HYPTEST

# jdbc url - the Connection URL to connect to the Relational Data-source  Schema

/RIR:jdbc:oracle:thin:@//database:1521/HYPTEST

# jdbc driver

/RID:oracle.jdbc.OracleDriver

# Relational Data-source Schema user 

/RIU:TDQPSPB

# rdbms password 

/RIP:vrX4PSFuHXyyBQGQ0o6yHFzY2T7KLyHgcp3WAqQuJJ/JlYRlIV

# ignore order of members

/-O

# sql queries for loading the Entities & Employee Dimension  

enth=select 'ENT_'||"Entity" "Entity",case when "Parent" like ' HR_Entities' then "Parent" ELSE 'ENT_'||"Parent" end as "Parent","Alias: Default"||'-'||"Entity" "Alias: Default", "Entity"||"Alias: German" "Alias: German" from HR_Entities_Dimension_01

emph=select \

'EMP_'||"Employee" "Employee" ,"Parent", "Alias:_Default" "Alias: Default", "Alias:_German" "Alias: German", \

"Data_Storage" "Data Storage" \

from employees_Dimension_01 \

union all \

select \

'EMP_'||edm."Employee" "Employee", \

(case \

     when emlp."Nationality"='56' then 'German' \

     else 'Non_German' \

end) "Parent", \

edm."Alias:_Default" "Alias: Default", edm."Alias:_German" "Alias: German", 'Shared' "Data Storage" \

from employees_Dimension_01 edm, Employee_Master_Level_01 emlp \

where (1=1) \

and edm."Employee"= emlp."Employee"

TIP: The / "Slash" is given at each line so all the lines are interpreted as a single SQL. 

Note: An RDB JDBC password for the input RDB connection. Enter the password in its unencrypted form when specifying it for the first time in the .properties file. When the Outline Load utility is run, the properties file will be rewritten with an encrypted value for the /RIP password. If this value is not specified in the properties file, a command line prompt will be issued to obtain the password. 

2. Load the Metadata into Hyperion Planning by calling the OLU 

 /Test/u01/Oracle/Middleware/user_projects/epmsystem1/Planning/planning1/OutlineLoad.sh -Calling the OLU with the complete path to it

-f:password.txt -Pass the password.txt containing to connect  to the application

/CP:QOCPSBP_metadata.properties -parameter in the command line to refer to that file when you execute the Outline Load utility.

/IR:QOCPSBP_metadata.properties  -DBConnectionPropertiesFileName

/D:Entity -For Loading to Entity Dimension

/RIQ:enth - inputQueryOrKey (You can either specify the complete SQL or the Key name that have the actual SQL. Enth is a KEY here.

/L:Entity_Dim.log - logFileName

/X:Entity_Dim.xcp - exceptionFileName

Loading the Entity Dimension

Loading the Employee Dimension

/Test/u01/Oracle/Middleware/user_projects/epmsystem1/Planning/planning1/OutlineLoad.sh 

-f:password.txt 

/CP:metadata.properties 

/IR:metadata.properties 

/D:Employee

/RIQ:emph

/L:Employee_Dim.log 

/X:Employee_Dim.xcp

If you have rejections which will be shown in the same window/session also in the logs and .xcp file

Loading the Data from relational data source to Hyperion Planning using OLU

1.      Create a data.properties file – Same as the one done for the metadata (you can also have one file for loading metadata & data)

# /S:server where Planning is running

/S:Example-Planning-Server.com

# Application where you want to load the Metadata

/A:Vision

# User used to load the Metadata

/U:admin

# jdbc catalog - the Source Relational Schema

/RIC:HYPTEST

# jdbc url - the Connection URL to connect to the Relational Data-source  Schema

/RIR:jdbc:oracle:thin:@//database:1521/HYPTEST

# jdbc driver

/RID:oracle.jdbc.OracleDriver

# Relational Data-source Schema user 

/RIU:TDQPSPB

# rdbms password

/RIP:vrX4PSFuHXyycVnlZN6WArrqEUBQGQ0o6yHFzY2T7KLyHgcp3WAqQuJJ/JlYRlIV

# sql query

ep_sal_alloc=SELECT \

  '1st Allocation' "Budget Item","Data Load Cube Name", \

  "Point-of-View"||','||'ENT_'||"ENTITY"||','||'POS_'||"POSITION"||','||'EMP_'||"Employee" "Point-of-View", \

  '_'||"Account Segment" "Account Segment",'_01'||"Entity Segment" "Entity Segment", \

  '_'||"Champion Segment" "Champion Segment",'_'||"Sub Account Segment" "Sub Account Segment",'_'||"Future Segment" "Future Segment", \

  TO_CHAR("Allocation Start Date",'MM-DD-YYYY') "Allocation Start Date", \

  TO_CHAR("Allocation End Date",'MM-DD-YYYY') "Allocation End Date", \

  "Percentage Allocation" \

FROM EMP_BY_POS_SAL_ALLOC_01

2. Load the Data into Hyperion Planning by calling the OLU 

/Test/u01/Oracle/Middleware/user_projects/epmsystem1/Planning/planning1/OutlineLoad.sh

-f:password.txt

/CP:data.properties

/IR:data.properties

/D:"Budget Item"

/RIQ:ep_sal_alloc

/L:EP_SAL_ALLOC.log

/X:EP_SAL_ALLOC.xcp

Loading Smart list from relational data source to Hyperion Planning using OLU

# sql query

ms_sl=select "SmartList Name","Operation","Entry ID","Entry Name","Entry Label" from marital_status_sl_01

  

Note: Same as loading data & metadata create the properties file and call them as below

/Test/u01/Oracle/Middleware/user_projects/epmsystem1/Planning/planning1/OutlineLoad.sh

-f:password.txt

/-H  Order input records in parent-child order, with the exception of UDAs (default). Use /-H to load input records as they appear in the load file; this option is faster and uses less memory.

/CP:metadata.properties

/IR:metadata.properties

/D:HSP_SMARTLISTS  - Since we are loading the smart list it has to be HSP_SMARTLISTS 

/RIQ:ms_sl

/L:ms_sl.log

/X:ms_sl.xcp

OLU is great tool with SQL it adds lot of flexibility to customize the load as needed and get loaded to the Hyperion Planning.

Happy Planning with OLU!!!!