Loading to EPMA planning applications using interface tables – Part 7

Back once again with another instalment of the EPMA interface series even though I thought the last one would have been the final part, today’s blog is about loading attributes, Smart Lists and UDAs and is mainly due to the number of requests I have had on how to go about it.

I am going to start off with attributes and run through the steps to load an attribute hierarchy and apply an attribute to a base member using interface tables. Once again I am not going to overcomplicate the matter so I am going manually create the dimension and associations first, it only takes a couple of minutes to do and is probably quicker than trying to achieve it through the interface route and considering the creation of an attribute dimensions don’t happen that often it makes sense to use this method.

I will also assume you have been following the series and that you are up to speed on the concept of how interface tables work.

Anyway on to the steps, once in the dimension library select File > New Dimension

I am going to create an unimaginative attribute dimension called ProductType which will be used to analyze products in the Segments dimension of the sample planning application.

Once the dimension has been created the association between the segments and the ProductType dimension has to be created, this is achieved by right clicking the Segments dimension in the dimension library and choosing “Create Association”. If I didn’t create this association then I would not be able to apply attribute members against base level members in the Segments dimension.

I also created an association between the Alias and ProductType dimension to allow aliases to be added to the attribute member names.

The ProductType dimension was added to the sample planning application and all the associations activated.

If a member in the Segments dimension is selected then the ProdTypAtt property is now available and members from the ProductType dimension can be assigned to it.

This is where the interface tables can perform the rest of the work such as loading the attribute dimension hierarchy and assign attribute members to base level members in the Segments dimension.

The first step is to return the interface table IM_DIMENSION, I went through the details of this table in part 2 of the series so if you are unsure about the table have another read of that part of the series.

A new record was added to the table with the following details

C_DIMENSION_NAME – this defines the name of the dimension so ProductType is used.
C_DIMENSION_CLASS_NAME – this defines the type of dimension which is Attribute.
C_HIERARCHY_TABLE_NAME – this provides the table name which holds the attribute dimension hierarchy, the table name is going to be PLAN_PRODUCTTYPE_HIER

I created the table PLAN_PRODUCTTYPE_HIER which will hold the hierarchy metadata for the ProductType attribute dimension, the ISPRIMARY shouldn’t really be necessary but if you don’t include it then warnings are generated in EPMA when an import from the interface tables takes place.

The table was populated with a simple one level attribute hierarchy.

As attribute members are going to be assigned to base level members in the Segments dimension a column was added to the Segments hierarchy table “PLAN_SEGMENTS_HIER”, the column was named the same as the attribute property “PRODTYPEATT” defined in EPMA.

The import profile requires editing to take account of the new attribute dimension otherwise if an import was run the new information would not be picked up and loaded to EPMA.

In the Map Dimensions section the Interface table was mapped to the ProductType dimension in the shared dimension library.

In the Dimension mapping section the ProductType dimension Alias column was mapped to the Alias property in EPMA, an attribute member only has the Alias property to map.

The Segments dimension was updated to map the attribute column from the source interface table to the property in EPMA, usually I keep the source/target with the same naming convention so then it is much easier to map.

The import profile was then executed to load the metadata from the interface tables to EPMA.

The ProductType attribute dimension hierarchy was successfully created and the Segment members were assigned with an attribute member.

Now say you didn’t create the associations manually as I did in EPMA and you wanted to achieve this using the interface tables, I still think it is just as quick to manually do it but here is how you would go about it.

When the sample interface tables are first created there is a table available called IM_DIMENSION_ASSOCIATION, the table name basically explains what it is about and it provides the ability to map dimensions and properties.

IM_DIMENSION_ASSOCIATION

There are four columns to populate

I_LOAD_ID which is the same as the load ID used in pretty much all the interface tables
C_BASE_DIMESION - Name of the base dimension whose member property will be associated with another dimension
C_PROPERTY - The name of the associated property.
C_TARGET_DIMENSION - Name of the dimension with which the associated property is associated.

The table has been populated to associate the Segments dimension with the attribute dimension and its property, also the alias property is associated between the attribute and alias dimensions.
If the import profile is run again and the associations did not exist they would be created replicating what I originally did manually.

If you want to also created the dimension in EPMA from the information in the interface tables then you need to populate an extra column in the IM_DIMENSION table

IM_DIMENSION

 

The C_DIM_PROPERTY_TABLE_NAME column holds a table name to define the properties of the dimension, so in this case the properties for the ProductType attribute dimension will be held in table PLAN_PRODUCTTYPE_PROPERTY.

PLAN_PRODUCTTYPE_PROPERTY

For an attribute dimension there are only a few property columns.
DIMENIONALIAS – Alias of the dimension.
ATTRIBUTEDATATYPE – Defines the attribute dimension type which can be Text, Boolean, Date, Numeric

By populating this information the dimension and its properties will be created when the import profile is executed.

Let’s move on to UDAs.

Once again I am going to create the dimension first in the EPMA dimension library and set the Type to UDA.

When a UDA dimension is created two members are added by default, these are specific to planning and if you are unclear what HSP_NOLINK and HSP_UDF do then have a read of the documentation here

I am going to assign UDAs to the Segments dimension so an association between the two dimensions was created using the existing UDA property.

Once again the IM_DIMENSION table requires an addition row with the UDA information.

Dimension name = UDA_Shared
Dimension class = UDA

Hierarchy table name will be PLAN_UDA_HIER

PLAN_UDA_HIER

The table was created and populated with UDA members in the child column.

As the objective is to apply UDAs to members in the Segments dimension the Segments hierarchy table requires updating.

PLAN_SEGMENTS_HIER

I am going to apply multiple UDAs to one member and to do this you need one column per UDA, so this case an extra UDA column was added.

The import profile required editing to map the newly added UDA dimension.

The Segments dimension mapping was also updated to map to the new UDA columns from source to target.

The import profile was executed and the UDA dimension was built and multiple UDAs were assigned to Segment dimension members.

On to the final topic and that is Smart Lists, this time the objective is to create and build the Smart List dimension and then assign a Smart List to a member in the accounts dimension, the logic is pretty much the same to that of the attribute and UDA dimensions.

IM_DIMENSION

A Smart List dimension is going to be created called GradeSL which will basically just define different employee grades.

A new record is added to the IM_DIMENSION table, the Smart List members will be in table PLAN_GRADESL_HIER and the properties for the Smart List dimension will be in table PLAN_GRADESL_PROPERTY.

If you created a Smart List dimension in EPMA then there are a number of properties than can be set, most of these can be applied from an interface table.

PLAN_GRADESL_PROPERTY

The table to hold the Smart List dimension property definitions was created and populated, if you don’t understand what the properties mean then it is worth consulting either the EPMA or Planning administrator documentation as they contain detailed information, the above screenshots should provide enough detail to map the EPMA property to the interface table property.

PLAN_GRADESL_HIER

The table to hold the Smart List member information was created and populated.

ITEMVALUE – when a Smart List member is added it requires an integer value
SMARTLISTENTRYLABEL – each Smart List member requires a label which will be used to populate a drop down menu in planning.

As the Smart List is going to be applied to an account member the account hierarchy interface table will require updating.

PLAN_ACCOUNT_HIER

The Smart List GradeSL is applied to account member GradeType and the Data Type is set as a Smart List.

No associations are required between the Smart List dimension and the dimension the Smart List will be applied to.

The import profile is edited again to define the new Smart List dimension.

The Smart List dimension does not exist yet in the Shared Library so “Create dimensions for the non-mapped dimensions with the source dimension name” is enabled, this creates the dimension in EPMA with the same name as defined in the C_DIMENSION_NAME column in the IM_DIMENSION table.

The interface property columns for the Smart List members are mapped to their corresponding properties in EPMA.

The import profile was then executed to create and populate the Smart List dimension and assign the Smart List to an account member.

The Smart List dimension has been created and properties set.

The Smart List GradeSL has been assigned to account member GradeType.

Hopefully you should now have a grasp on how to handle Attributes, UDAs and Smart Lists dimensions using EPMA tables.