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 TableDescription

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.

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

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.