Loading to EPMA planning applications using interface tables – Part 2

In the first part I went through an introduction to what this series is about and covered getting the core planning application created in EPMA.

In this part I want to look at interface tables and end up with them built ready to load metadata into.

Interface tables are basically just a set of relational staging area tables that are populated before pushing into EPMA and these can be populated from any designated source, the ultimate end game in this series of blogs is to use ODI to populate the tables and then execute the process of moving them into EPMA.

The first step that needs to be undertaken is to create the interface data source and this is done in the EPM system configurator, I am not sure why this functionality is not yet available through workspace though I am sure it will be there one day.

Setting up the interface data source is basically configuring the connection to the RDBMS where the tables will be held, you will need to have created a schema/database first.

Start up the EPM System Configurator, uncheck all, expand Foundation > Performance Management Architect and select “Configure Interface Data Source”

As this is the first time configuration “Create a New Datasource Link” is selected.

Select the RDBMS type that is going to be used for the interface tables.

A name for the data source is entered and this name that will be used in EPMA.
The connection details to database are entered.

Now there is the option to “Create interface tables”, I have selected this option and what it does is create a set of sample interface tables.

Once the interface tables have been created this is where the fear can set in.

As you can see a hell of a lot of tables have been created and you start to wonder whether this interface table route is going to be worth it, if you select one of the tables and view the amount of columns the fear gets worse but in the words of the great The Hitchhiker's Guide to the Galaxy “Don’t Panic!”

These are a set of sample tables and are not required, the columns in the tables relate to all the possible properties across Essbase, Planning and HFM.

The fact that I am initially only looking at planning and the approach I am taking is to only load metadata to dimensions which have been already created plus associations applied then honestly it is not as bad as it first looks.

The set of tables that are of more value are prefixed with IM_ which are not in the list above.

The rest of the tables that will be required are related to the metadata to be loaded for each dimension which I am going to manually create and will go through shortly.

IM_LOAD_INFO

This table basically allows grouping of metadata to be loaded, when importing dimensions in EPMA there is an option to select the interface load ID, the load ID is a numerical value.

Each dimension hierarchy table (not created yet) will have a column called LOADID that will relate back to the IM_LOAD_INFO table, it will become much clearer as we progress.

I have manually added a record to the table, it is not actually that important that the table keeps getting updated it could easily be set up once and then left alone.

I_LOAD_ID – I have started out with the value of 1

C_SOURCE_SYSTEM – Anything can be entered here but as I am initially going to be loading from files I have just set it to “FLAT_FILE”, it could be there are multiple metadata loads and you could have load ID 1 loading from flat files and Load ID 2 loading from a different source such as a warehouse.

C_USER_LAST_UPDATED – Once again anything can go here it all depends if you want to keep the user details.

D_DATE_LAST_UPDATED – Timestamp field.

C_LAST_UPDATE_LOGIN – Again anything can go here

IM_DIMENSION_ASSOCIATION – I am not going to be using this table as the dimension associations have already been applied, I may cover this at a later stage.

 
IM_DIMENSION

This table holds the information about the dimensions that are going to be loaded.

I_LOAD_ID – The load ID links back to the previous table IM_LOAD_INFO, so a corresponding value would be entered for the interface load.

C_DIMENSION_NAME – This is the name of the dimension in EPMA that is going to be loaded to.

C_DIMENSION_CLASS_NAME – This is the dimension type e.g. Account, Entity, Generic.

C_MEMBER_TABLE_NAME – This is the name of the table that will hold the member information. I am not going to be using this column as it can be all covered in the next column.

C_HIERARCHY_TABLE_NAME – This is the name of the table which will hold all the hierarchy and member information.

C_PROPERTY_ARRAY_TABLE_NAME – This is the name of the table which contains property information. I am not going to be using this column.

C_DIM_PROPERTY_TABLE_NAME – This is the name of the table containing all the dimension property information. I am not going to be using this column.

So out of all the columns I only need to populate four of them.

I have set the I_LOAD_ID to 1 to match what was set in the IM_LOAD_INFO table, so if a dimension import from interface tables is executed in EPMA against load ID 1 the required dimensions to be loaded and the tables holding the information are known.

For the records in column C_HIERARCHY_TABLE_NAME I have entered table names that will hold all the hierarchy/member properties, I am going to go through the process of creating them now.

It is possible to look at the HS_<DIMNAME>_HIERARCHY AND HS_<DIMNAME>_MEMBER sample tables and pick out the columns relating to Planning and then create a table based on them.

To make it easier I have gone through each dimension that I will be loading metadata to and generated the full list of columns that can be populated, many of them don’t have to be populated just like when using other methods to load metadata to a planning application

VERSION  

ENTITY

SCENARIO

ACCOUNT

SEGMENTS (custom dimension = Generic dimension type in EPMA terms)

The columns in each of the tables do not include any attribute related information yet, I will cover attributes and UDAs at a later stage.

In the next part I was going to look at introducing ODI to populate the interface tables but I think it is worth covering off import profiles first.