Loading to EPMA planning applications using interface tables – Part 8

Just when you thought the end of the interface series had come about I return with another instalment. Today I thought I would quickly go over loading data from interface tables as it is an area I have not covered and I want the series to be complete.

Within EPMA there is built in functionality known as data synchronization which allows the synchronizing of data between EPM products, flat files and interface tables which I will be covering.

The objective of today is as usual to go back to basics and load numeric, date and text data from an interface table into a planning application.

A simple form was created with three account members, TextMember1 which is a text data type, DateMember1 which is a date member type and a standard account member Acc1.

A relational interface table was created to hold the data, there are not really any special requirements on the format of the table and the one created basically has column for each dimension and one for the data.

The table was populated with three different data type records, I am not going to be covering populating the table as the data could have come from practically any source and it could have gone through numerous transformations using the tool or method of your choice before being loaded to the interface  table.

To be able to load the data there are a few steps that need to be carried out within the Data Synchronization area accessed through workspace.

The first step is to create the Interface Area definition which basically means which relational table and columns will be used to load the data from.

Select Navigate > Administer > Data Synchronization in workspace.

In the Data Synchronization area select New > Data Interface Table Definition.

The interface area that is going to be is selected, if you have followed this blog series you should already know or have set up an interface area, if you are unclear then have a read here.

The table which holds the data and the data column are selected from the drop down boxes.

The dimension definition section allows you to add the columns that are going to be used in the synchronization and provide a friendly display name for them.

Finally a name is provided for the Interface area.

The interface area now appears in the data synchronizer, you are not confined to just one interface area and can create as many are required.

The next step is to create the Synchronization which defines which interface area to associate with, the application to load data to and the dimension mappings between the source interface table and the target application.

To create the Synchronization select New > Synchronization.

The source type is then defined which can either be a Hyperion application, external source such as a flat file or in my case an interface area.

A list of available interface areas will then be displayed.

A list of available target applications are then displayed.

As the destination is a planning application a list of available plan types to load the data to are displayed and one can be selected.

The next screen defines the dimension mapping from the source interface columns to the target planning application dimensions, to create a link between source and target then it is as simple as dragging the source to the target.

There is not always match between source and target and in the example above you can see there is no mapping for the dimension HSP_Rates in the source, in this case you can force the target dimension to a member.

If you right click the dimension there is an option “Assign Default Member”

A member can then selected and the data will be loaded to this member each time the synchronization is run.

It is also possible to filter the data from the source, so say for example you only wanted to load data for one account then a filter can be applied.

If you right click the source dimension the option to “Create Filter” will be available.

 As the source is an interface table then there two types of filter available EQUAL or LIKE, if the source was an application there are also a number of functions available.

Wildcards such as * and ? are allowed when using the LIKE filter, to be honest it is pretty basic functionality and if you are using interface tables then  it is probably best to transform the data into a format ready to be loaded before using the data synchronizer.

There is also the option to create mapping tables if the source is a Hyperion application.

 If you create a filter an icon will be displayed against the source.

Once all the mappings are complete the synchronization can be saved.

The synchronization should now appear in the Data Synchronizer window and then right click “Execute Synchronization” to load the data, check the Job Console to see if the synchronization was successful.

The synchronization was successful and the web form displayed the desired results.

If you don’t want to have to run the synchronization each time from workspace and want to automate the process then you can use the EPMA batch client, I covered the client in part 5 of the series so if have not used it then it might be worth having a read.

The syntax to execute synchronization using the batch client is -

Execute DataSynchronization Parameters(DataSynchronizationName, DataTransformationOperator,DataTransformationValue, FileName, ValidateOnly, WaitForCompletion, dataSyncLoadOptionHpMode) Values('', '', '', '', '', ‘',’’);

DataSynchronizationName—Name of the Data Synchronization profile to execute.

DataTransformationOperator—Valid values are:

•    None
•    '*' (Multiply)
•    '/' (Divide)
•    '+' (Add)
•    '-' (Subtract)

DataTransformationValue—Value to use in conjunction with the DataTransformationOperator to modify the data values.

FileName—If the synchronization uses an external source file for the source of the synchronization, the location of the external source file.

ValidateOnly—Validates the data synchronization without executing it.

WaitForCompletion—If set to true, the Batch Client waits for the job to finish. If set to false, the Batch Client submits the job and continues. Allowed values:  True or  False

dataSyncLoadOptionHpMode. Allowed values: ADD, SUBTRACT, OVERWRITE (which is the default)

So in the examples I have been using the syntax would be.

Execute DataSynchronization Parameters(DataSynchronizationName, DataTransformationOperator,DataTransformationValue, FileName, ValidateOnly, WaitForCompletion, dataSyncLoadOptionHpMode) Values('INT_2_EPMASAMP', 'None', '', '', 'false', ‘true',OVERWRITE’);

This can then be added to a script that can be called from command line, an example of the command line would be

epma-batch-client.bat -C"F:\Scripts\ExcDataSync.txt" -R"F:\Scripts\ExcDataSync.log" -Uadmin –Ppassword   

 

The output is written to the command window and if specified to a log file, the output includes a link to the job console to view a summary of the synchronization.

If you are interested in using ODI to automate the process of using the batch client then have a read of part 6 of the series.

If you are experiencing any issues with the synchronization then it is possible to enable additional logging.

There is a file called dme.properties located at
<MIDDLEWARE_HOME>\user_projects\<instancename>\config\EPMA\DataSync

If you edit the file and remove the # from the beginning of the following lines.

preTranslationProcessingClass=com.hyperion.awb.datasync.custom.FileBasedRowLogger
preTranslationProcessingClass.outputFile=preTransOut.txt

postTranslationProcessingClass=com.hyperion.awb.datasync.custom.FileBasedRowLogger
postTranslationProcessingClass.outputFile=postTransOut.txt

createDebugFiles=true
debugSampleSize=1000

Save the file and then restart Data Synchronization web application service web application

The additional logs will be available from
<MIDDLEWARE_HOME>\user_projects\epmsystem1\tmp\oracle\temp
 

There is a log available displaying the data before it is loaded to planning, the filename is prefixed with a unique ID.

 

and another log with the data has the filters and mappings applied which is the format that is then loaded to the planning application.

There is also an additional folder created each time a synchronization is executed with the same unique ID and contains further diagnostic logging information to investigate.

To turn off the addition logging edit dme.properties again and place a hash in front of the lines that were originally edited and restart Data Synchronization web application.

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.