1. Administrator's Guide
  2. Importing and Exporting Data and Metadata
  3. Working with the Outline Load Utility
  4. Importing Data and Metadata from a Relational Data Source
  5. Importing Metadata

Importing Metadata

The following is an example command line for running the Outline Load utility using the myprop_relational.properties file (detailed below) as input: 

OutlineLoad /CP:c:/myprop_relational.properties

The file will import Account members from the PS2ORAU application shown in the JDBC connection parameters into an application named Test_300. The properties file can contain several relational queries. The /RIQ parameter determines which query will be executed by the Outline Load utility.

For a description of /RIQ, see Command Line Parameters for the Outline Load Utility.

Example 5-1 Example: The myprop_relational.properties File 

/A:Test_300
/U:admin
/RIQ: ACCOUNT_QUERY

/D:Account
#
ACCOUNT_QUERY=SELECT O.OBJECT_NAME as Account, (select object_name from hsp_object where object_id = O.PARENT_ID) as Parent from HSP_ACCOUNT A, HSP_MEMBER M, HSP_OBJECT O LEFT OUTER JOIN HSP_STRINGS S ON O.DESCRIPTION = S.STRING_SEQ  WHERE O.OBJECT_ID=M.MEMBER_ID AND M.MEMBER_ID = A.ACCOUNT_ID AND M.MEMBER_ID <> M.DIM_ID ORDER BY O.POSITION
#
ENTITY_QUERY=SELECT O.OBJECT_NAME as Entity, (select object_name from hsp_object where object_id = O.PARENT_ID) as Parent from HSP_ENTITY E, HSP_MEMBER M, HSP_OBJECT O LEFT OUTER JOIN HSP_STRINGS S ON O.DESCRIPTION = S.STRING_SEQ  WHERE O.OBJECT_ID=M.MEMBER_ID AND M.MEMBER_ID = E.ENTITY_ID AND M.MEMBER_ID <> M.DIM_ID ORDER BY O.POSITION

## jdbc connection
/RIC:PS2ORAU
/RIR:jdbc:oracle:thin:@[scl34390]:1521:orcl
/RID:oracle.jdbc.OracleDriver
/RIU:PS2ORAU
/RIP:password

#end myprop_relational.properties file

In the following example, an attribute dimension in both source and target applications has a name with more than 30 characters, Size012345678901234567890123456789, which exceeds the Oracle column header limit. The query in the following example is used to create a result set from the source to be imported into the target application. The long dimension name must be aliased to column O.OBJECT_NAME to allow OLU to use this column as the attribute dimension name.

Example 5-2 Example: Properties File for Importing from a Relational Data Source to a Planning Application 

/A:TARGET
/U:admin
/IR

/DA:Size012345678901234567890123456789:Entity

/C2A:(OBJECT_NAME,Size012345678901234567890123456789)

ATTRIB_DIM_VAL_QUERY_ORACLE1=SELECT O.OBJECT_NAME, (select object_name from PS3ORA.hsp_object where object_id = O.PARENT_ID) as Parent from PS3ORA.HSP_OBJECT O WHERE O.OBJECT_ID IN (select AM.ATTR_MEM_ID from PS3ORA.hsp_attribute_member AM, PS3ORA.HSP_OBJECT O where AM.ATTR_ID =(select OBJECT_ID from PS3ORA.hsp_object where OBJECT_NAME='Size012345678901234567890123456789')) ORDER BY O.POSITION

Example 5-3 Example: Properties File for Importing with Alias from a Relational Data Source to a Planning Application 

/A:expe
/U:admin
/IRA
/D:Entity
/C2A:(Member as Entity,anotherReallyLongAliasForAColumnNameItsForEntityThisTimeDifferentFromExport), (Parent, anotherAliasForParent)

/RIQ:select anotherReallyLongAliasForAColumnNameItsForEntityThisTimeDifferentFromExport, Parent from test

Assume the following with regards to the previous example:

  • The 'Test' table has two columns, Member and Parent, and it has one entry:


    This graphic shows the output of the 'Test' table after running the previous query.

  • The Entity dimension has one member, e1, under the root (Entity).

After running the previous query, the member Ex is added to or updated in the Entity dimension.

On import, the aliases for columns are handled as a straight string replacement which is why the "as" clause can work in this case. If the Member column was named Entity, you would drop the 'Member as.'

Example 5-4 Example: Using the /C2A Parameter for Importing Aliases into an Alias Table 

/A:TARGET
/U:admin
/IR
/D:Entity

/RIQ:ENT_ALIAS_ASSIGNMENT_QUERY

/C2A:(AliasTableName, Alias: LongAliasTableName012345678901234567890123)

ENT_ALIAS_ASSIGNMENT_QUERY=SELECT O.OBJECT_NAME as Entity, (select object_name from hsp_object where OBJECT_ID=O.PARENT_ID) as Parent, (select OBJECT_NAME from HSP_OBJECT where OBJECT_ID = AL.ALIAS_ID) as AliasTableName  from hsp_object O, HSP_ALIAS AL where (AL.ALIASTBL_ID = (select OBJECT_ID from HSP_OBJECT where OBJECT_NAME =' LongAliasTableName012345678901234567890123') AND O.OBJECT_ID=AL.MEMBER_ID)

Note:

In the previous example, both the target Oracle Hyperion Planning application and the source contain a long alias table name.

Example 5-5 Example: Using the/C2A Parameter to Map Column Names to Property Names 

/RIQ:select c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17,c18,c19,c20,c21,c22,c23,c24,c25,c26,c27 from table28

/c2a:(c1, Account), \
(c2, Parent), \
(c3, Alias: Default), \
(c4, Valid For Consolidations), \
(c5, Data Storage), \
(c6, Two Pass Calculation), \
(c7, Description), \
(c8, Formula), \
(c9, UDA), \
(c10, Smart List), \
(c11, Data Type), \
(c12, Hierarchy Type), \
(c13, Enable for Dynamic Children), \
(c14, Number of Possible Dynamic Children), \
(c15, Access Granted to Member Creator), \
(c16, Account Type), \
(c17, Time Balance), \
(c18, Skip Value), \
(c19, Exchange Rate Type), \
(c20, Variance Reporting), \
(c21, Source Plan Type), \
(c22, Plan Type (Plan1)), \
(c23, Aggregation (Plan1)), \
(c24, Plan Type (Plan2)), \
(c25, Aggregation (Plan2)), \
(c26, Plan Type (Plan3)), \
(c27, Aggregation (Plan3))