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

Exporting Metadata from a Planning Application to a Relational Data Source

You can use the Outline Load utility to export metadata to a relational data source.

Note:

You cannot export data to a relational data source using the Outline Load utility.

Oracle recommends that users employing this functionality are familiar with relational databases, the SQL query language, and JDBC connection properties. For Oracle Hyperion Planning applications, examples of these properties can be found in the HSPSYS_DATASOURCE table in the system database, which by default is located in the relational database that you create when selecting the Configure Database task under Planning in EPM System Configurator.

These command line parameters are available for users using the Outline Load utility to export to a relational data source:

  • /ER[:RDBConnectionPropertiesFileName]

  • /ERA

  • /REQ:exportQueryOrKey

  • /REC:catalog

  • /RED:driver

  • /RER:url

  • /REU:userName

  • /REP:password

Note:

The /ER and /REQ parameters are exclusive.

For descriptions of these parameters, see Command Line Parameters for the Outline Load Utility.

The export query takes the form: INSERT INTO<tableName>[ (column1, column2, ...)] VALUES (property1, property2,...)

Notes:

  • <tableName> is the name of the table into which the exported values will be inserted

  • (column1, column2, …) is an optional list of column names and the values (properties) will be inserted into the table

  • (property1, property2, …) are not strictly values, but member property names (as in the column headers)

    Note:

    Unlike header records, the member name is specified with ‘Member’, not the dimension name.

For example:

Assume Planning has two members in its Entity dimension ‘e1’ with description ‘e1’s description’ and ‘e11’ with description ‘e11’s description.’ E1 resides under entity, e11 resides under e1. So the planning dimension looks like:

Entity

   e1
      e11

Assume the destination table my_table has three columns: column1, column2, column3

Insert into my_table values (Member, Parent, Description) results in my_table having the following rows added: 

E1     e1's description     Entity
E11    e2's description     e1

Which is the same as:

Insert into my_table (column1, column2, column3) values (Member, Parent, Description)

Note:

The table must contain as many or more columns as properties specified.

There are two special operators that can be specified in the query: <columns> and <properties>. <columns> expands to all of the column names in the table. <properties> expands to all of the properties of the exported dimension, as shown with the /M switch. 

Insert into my_table values (<properties>)

Which loads all entity properties into my_table.

Which is the same as:

Insert into my_table (column1, column2, ….. column23) values (<properties>)

If my_table contained exactly the number of columns as properties, the following query would produce the same result: 

Insert into my_table (<columns>) values (<properties>)

You could also use the names of table columns to extract the member properties. For example, sample_table, with columns: Member, Parent, and Description. The following queries would produce the same result as the first example: 

Insert into sample_table values (<columns>)
Insert into sample_table (<columns>) values (<columns>)

Example 5-6 Example: Properties File for Exporting Metadata to a Relational Data Source

/A:App1
/U:admin

/ERA

/D:Entity

/REQ:ENTITY_EXPORT_QUERY1

ENTITY_EXPORT_QUERY1=INSERT INTO DataTable_Entity1 VALUES (Member, Parent, Description, Color) 

#ENTITY_EXPORT_QUERY1=Insert into PS3ORA.DataTable_Entity1 (Member, Parent, Description, Color) values (Member, Parent, Description, Color)

Example 5-7 Example: Properties File for Exporting with an Alias from an Outline to a Relational Data Source

/A:expe
/U:admin
/ERA
/D:Entity
/REQ:ENTITY_EXPORT_QUERY1
/C2A:AliasLabel
AliasLabel:(AliasTableName, Alias: reallyLongAliasTableName30Char)

ENTITY_EXPORT_QUERY1=Insert into Test(Member, Parent, AliasTableName) values (Member,
Parent, Alias: reallyLongAliasTableName30Char)

Assume the following with regards to the previous example:

  • The 'Test' table has three columns, Member, Parent, AliasTableName and the table is empty.

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

After running the previous query, the 'Test' table will have one entry:


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

Note:

The /C2A and/or Label switches may be used to work around the thirty character column name limit restriction in Oracle relational tables; for example, Alias: reallyLongAliasTableName30Char is longer than thirty characters and not allowed as a column name in Oracle relational tables.