导入元数据

下面是使用 myprop_relational.properties 文件(下面详细讲述)作为输入运行大纲加载实用程序的命令行示例:

OutlineLoad /CP:c:/myprop_relational.properties

该文件将“帐户”成员从 JDBC 连接参数中显示的 PS2ORAU 应用程序导入到名为 Test_300 的应用程序。属性文件可以包含多个关系查询。/RIQ 参数确定大纲加载实用程序将执行的查询。

有关 /RIQ 的说明,请参阅“大纲加载实用程序的命令行参数”。

示例 5-1 示例:myprop_relational.properties 文件

/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

在以下示例中,源和目标实用程序中的属性维具有超过 30 个字符的名称 Size012345678901234567890123456789,该名称超出了 Oracle 列标题限制。以下示例中的查询用于从要导入到目标应用程序的源创建结果集。较长维名称必须设置别名到列 O.OBJECT_NAME,以允许 OLU 使用此列作为属性维名称。

示例 5-2 示例:用于从关系数据源导入 Planning 应用程序的属性文件

/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

示例 5-3 示例:用于使用别名从关系数据源导入 Planning 应用程序的属性文件

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

/RIQ:select anotherReallyLongAliasForAColumnNameItsForEntityThisTimeDifferentFromExport, Parent from test

关于上个示例假设以下内容:

  • 'Test' 表具有两列(Member 和 Parent),并包含一个条目:


    该图显示运行上面的查询后 'Test' 表的输出。
  • Entity 维具有一个成员 e1,位于根 (Entity) 下。

运行上面的查询后,成员 Ex 将添加到 Entity 维中或在其中更新。

在导入时,列的别名将作为直接字符串替换进行处理,这就是在这种情况下可以使用 "as" 子句的原因。如果 Member 列被命名为 Entity,您将删除 'Member as'。

示例 5-4 示例:使用 /C2A 参数将别名导入别名表

/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)

注:

在上个示例中,目标 Oracle Hyperion Planning 应用程序和源包含较长别名表名称。

示例 5-5 示例:使用 /C2A 参数将列名称映射到属性名称

/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))