Defining Interface Tables

Interface tables in Performance Management Architect are grouped into two categories: system tables and user-defined tables.

System tables are created when the interface tables are configured. They contain information used by the import module during profile creation and execution.

System Table	Description
IM_Load_Info	The use of this table is optional. It allows for grouping subsets of the records in the IM_ tables. For example, metadata from different sources can be staged into the same interface tables instance, but each source could be identified with its own Load_ID. The IM_Load_Info table can be used to keep track of the different Load_IDs and their corresponding sources. Note: During import execution, you can choose to only import metadata tagged with a specific set of Load_IDs. See Entering Load ID Information When Importing Dimensions. Columns are: i_Load_ID—Number representing the Load_ID. c_Source_System—Source system this Load_ID represents. This field is optional. c_User_Last_Updated—This field is optional. c_Date_Last_Updated—This field is optional. c_Last_Update_Login—This field is optional.
IM_Dimension	Contains information about the dimensions to be processed, their types, and references to all the tables/views containing metadata for those dimensions. Note: This table is pre-populated with the names of sample dimensions, sample dimension classes, and HS_ sample tables. (1 row per c_Dimension_Name) Columns are: i_Load_ID—Load_ID tag for this dimension. c_Dimension_Name—Name of the dimension. c_Dimension_Class_Name—Dimension type. c_Member_Table_Name—Name of the table or view that contains the Members information for this dimension. See Members Section. Populating and specifying this table/view is optional. c_Hierarchy_Table_Name—Name of the table or view that contains the Hierarchy information for this dimension. See Hierarchies Section. This table/view needs to be specified and defined when performing a replace mode import. c_Property_Array_Table_Name—Name of the table or view that contains the PropertyArray information for this dimension. See Property Array Section. Populating and specifying this table/view is optional. c_Dim_Property_Table_Name—This table partly corresponds to the !Dimensions section of the flat file, except that the dimension properties are not defined here. See Dimensions Section.
IM_Dimension_Association	This table directly corresponds to the !DimensionAssociation section of the flat file. See Dimension Associations Section. Note: The sample table is not pre-populated. Columns are: i_Load_ID—Load_ID tag for this dimension. c_Base_Dimension—Name of the base dimension whose member property will be associated with another dimension. This is a required column. c_Property—The name of the associated property, for example 'Alias.' This is a required column. c_Target_Dimension—Name of the dimension with which the associated property is associated. This is a required column.

System Table

Description

IM_Load_Info

The use of this table is optional. It allows for grouping subsets of the records in the IM_ tables. For example, metadata from different sources can be staged into the same interface tables instance, but each source could be identified with its own Load_ID. The IM_Load_Info table can be used to keep track of the different Load_IDs and their corresponding sources.

Note:

During import execution, you can choose to only import metadata tagged with a specific set of Load_IDs. See Entering Load ID Information When Importing Dimensions.

Columns are:

i_Load_ID—Number representing the Load_ID.
c_Source_System—Source system this Load_ID represents. This field is optional.
c_User_Last_Updated—This field is optional.
c_Date_Last_Updated—This field is optional.
c_Last_Update_Login—This field is optional.

IM_Dimension

Contains information about the dimensions to be processed, their types, and references to all the tables/views containing metadata for those dimensions.

Note:

This table is pre-populated with the names of sample dimensions, sample dimension classes, and HS_ sample tables. (1 row per c_Dimension_Name)

Columns are:

i_Load_ID—Load_ID tag for this dimension.
c_Dimension_Name—Name of the dimension.
c_Dimension_Class_Name—Dimension type.
c_Member_Table_Name—Name of the table or view that contains the Members information for this dimension. See Members Section. Populating and specifying this table/view is optional.
c_Hierarchy_Table_Name—Name of the table or view that contains the Hierarchy information for this dimension. See Hierarchies Section. This table/view needs to be specified and defined when performing a replace mode import.
c_Property_Array_Table_Name—Name of the table or view that contains the PropertyArray information for this dimension. See Property Array Section. Populating and specifying this table/view is optional.
c_Dim_Property_Table_Name—This table partly corresponds to the !Dimensions section of the flat file, except that the dimension properties are not defined here. See Dimensions Section.

IM_Dimension_Association

This table directly corresponds to the !DimensionAssociation section of the flat file. See Dimension Associations Section.

Note:

The sample table is not pre-populated.

Columns are:

i_Load_ID—Load_ID tag for this dimension.
c_Base_Dimension—Name of the base dimension whose member property will be associated with another dimension. This is a required column.
c_Property—The name of the associated property, for example 'Alias.' This is a required column.
c_Target_Dimension—Name of the dimension with which the associated property is associated. This is a required column.

User-defined tables or views can be used to represent the Members, Hierarchies, PropertyArray, and dimension property content for a dimension. Both tables and views can be used as long as they are correctly referenced in the IM_Dimension table. The sample tables follow the naming convention, HS_Dimension_Section, where Dimension represents the dimension name, such as HS_Accounts_Hierarchy.

Note:

The table names in the following table are samples and any names can be used for user-defined tables or views.

System Table	Description
HS_Dimension_Member	This table directly corresponds to the !Member section of the flat file. See Members Section.
HS_Dimension_Hierarchy	This table directly corresponds to the !Hierarchies section of the flat file. See Hierarchies Section. You can specify an optional SortOrder column. If present, Performance Management Architect uses the values defined to determine the row order when it retrieves the children of a parent. Both Alias and UDA properties can be specified in the HS_Dimension_Hierarchy table even though the sample tables do not have columns for these properties. You can add columns for the Alias and UDA properties in the sample tables if desired. For example: For an Oracle database: ALTER TABLE HS_Account_Hierarchy ADD "Alias=English" NVARCHAR2(255);/ ALTER TABLE HS_Account_Hierarchy ADD "Alias=French" NVARCHAR2(255);/ For a SQL Server database: ALTER TABLE HS_Account_Hierarchy ADD "Alias=English" nvarchar(255)GO ALTER TABLE HS_Account_Hierarchy ADD "Alias=French" nvarchar(255)GO For an IBM DB2 database: ALTER TABLE HS_Account_Hierarchy ADD "Alias=English" VARCHAR(255); ALTER TABLE HS_Account_Hierarchy ADD "Alias=French" VARCHAR(255); An equal sign [=] the column name can be used to represent each Alias dimension member as a separate column.
HS_Dimension_PropertyArray	This table directly corresponds to the !PropertyArray section of the flat file. See Property Array Section.
HS_Dimension_Property	This table directly corresponds to the !Dimension section of the flat file. See Dimensions Section.

Note:

The following sample tables are created when creating the interface table instance. They can be replaced with user-created tables. These tables are not pre-populated with actual data.

If any import interface data table (dimension, hierarchy, member, or property array data) contains a SortOrder column, the data in that table will be loaded into the import engine in that order. The SortOrder column must be an integer data type and the data will be loaded in ascending order. If the SortOrder column data type is not an integer, the import engine ignores the column, does not add an ORDER BY clause to query, and generates a warning in the import results.

Sample Tables
HS_Account_Member HS_Account_Hierarchy HS_Account_Property HS_Account_PropertyArray HS_Alias_Hierarchy HS_Alias_Member HS_Alias_Property HS_Attribute_Hierarchy HS_Attribute_Member HS_Attribute_Property HS_Attribute_PropertyArray HS_ConsolidationMethod_Hierarchy HS_ConsolidationMethod_Member HS_ConsolidationMethod_Property HS_ConsolidationMethod_PropertyArray HS_Country_Hierarchy HS_Country_Member HS_Country_Property HS_Country_PropertyArray HS_Currency_Hierarchy HS_Currency_Member HS_Currency_Property HS_Currency_PropertyArray HS_Data_CapExTemplate HS_Data_Consolidation	HS_Data_Sales HS_Entity_Hierarchy HS_Entity_Member HS_Entity_Property HS_Entity_PropertyArray HS_Generic_Hierarchy HS_Generic_Member HS_Generic_Property HS_Generic_PropertyArray HS_ICP_Hierarchy HS_ICP_Member HS_ICP_Property HS_ICP_PropertyArray HS_Period_Hierarchy HS_Period_Member HS_Period_Property HS_Period_PropertyArray HS_Scenario_Hierarchy HS_Scenario_Member HS_Scenario_Property HS_Scenario_PropertyArray HS_SecurityClass_Hierarchy HS_SecurityClass_Member HS_SecurityClass_Property HS_SecurityClass_PropertyArray HS_SmartList_Hierarchy HS_SmartList_Member	HS_SmartList_Property HS_SmartList_PropertyArray HS_Time_Hierarchy HS_Time_Member HS_Time_Property HS_Time_PropertyArray HS_UDA_Hierarchy HS_UDA_Member HS_UDA_Property HS_Value_Hierarchy HS_Value_Member HS_Value_Property HS_Value_PropertyArray HS_Version_Hierarchy HS_Version_Member HS_Version_Property HS_Version_PropertyArray HS_View_Hierarchy HS_View_Member HS_View_Property HS_View_PropertyArray HS_Year_Hierarchy HS_Year_Member HS_Year_Property HS_Year_PropertyArray

Sample Tables

HS_Account_Member

HS_Account_Hierarchy

HS_Account_Property

HS_Account_PropertyArray

HS_Alias_Hierarchy

HS_Alias_Member

HS_Alias_Property

HS_Attribute_Hierarchy

HS_Attribute_Member

HS_Attribute_Property

HS_Attribute_PropertyArray

HS_ConsolidationMethod_Hierarchy

HS_ConsolidationMethod_Member

HS_ConsolidationMethod_Property

HS_ConsolidationMethod_PropertyArray

HS_Country_Hierarchy

HS_Country_Member

HS_Country_Property

HS_Country_PropertyArray

HS_Currency_Hierarchy

HS_Currency_Member

HS_Currency_Property

HS_Currency_PropertyArray

HS_Data_CapExTemplate

HS_Data_Consolidation

HS_Data_Sales

HS_Entity_Hierarchy

HS_Entity_Member

HS_Entity_Property

HS_Entity_PropertyArray

HS_Generic_Hierarchy

HS_Generic_Member

HS_Generic_Property

HS_Generic_PropertyArray

HS_ICP_Hierarchy

HS_ICP_Member

HS_ICP_Property

HS_ICP_PropertyArray

HS_Period_Hierarchy

HS_Period_Member

HS_Period_Property

HS_Period_PropertyArray

HS_Scenario_Hierarchy

HS_Scenario_Member

HS_Scenario_Property

HS_Scenario_PropertyArray

HS_SecurityClass_Hierarchy

HS_SecurityClass_Member

HS_SecurityClass_Property

HS_SecurityClass_PropertyArray

HS_SmartList_Hierarchy

HS_SmartList_Member

HS_SmartList_Property

HS_SmartList_PropertyArray

HS_Time_Hierarchy

HS_Time_Member

HS_Time_Property

HS_Time_PropertyArray

HS_UDA_Hierarchy

HS_UDA_Member

HS_UDA_Property

HS_Value_Hierarchy

HS_Value_Member

HS_Value_Property

HS_Value_PropertyArray

HS_Version_Hierarchy

HS_Version_Member

HS_Version_Property

HS_Version_PropertyArray

HS_View_Hierarchy

HS_View_Member

HS_View_Property

HS_View_PropertyArray

HS_Year_Hierarchy

HS_Year_Member

HS_Year_Property

HS_Year_PropertyArray

In addition to the dimension interface tables created by the template script, you can add interface tables for additional dimensions. For example, the template script contains one set of tables for the Entity dimension. You can add more Entity dimensions as needed. For each dimension added to the interface tables, you must also include the dimension in the IM_Dimension system table so that the dimension is available during profile creation. You can find the scripts for creating sample tables in <EPM_ORACLE_INSTANCE>\Products\Foundation\BPMA\Server\Conf. The scripts are named for each database type: Oracle_Create_Interface_Tables.sql, DB2_Create_Interface_Tables.sql, and SQL_Server_Create_Interface_Tables.sql.