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.
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.
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’);
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 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.
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.
Great article, as usual.
ReplyDeleteThank you John.
Thanks for sharing John!
ReplyDeleteQuestion on performance. Is there a way to increase performance on these? a 7 million records table takes around 6-7 minutes. Synchronization log file shows the read and transalation process taking the most time, writing to Essbase was a lot lower.
Thank you for article!
ReplyDeleteI faced with error when used all parameters of DataSynchronization in batch client. For me works this:
Execute Datasynchronization Parameters(DataSynchronizationName, ValidateOnly, WaitForCompletion) Values('Sync_name', 'false', 'true');
Hi John. Useful information in this post, thanks. Particularly the stuff about the logging. I am using batch script to execute syncs from an HP to an HFM application. I have question, is there any way to programatically alter the filters being used by a data sync? For example, I want to change the entity being synchronized.
ReplyDelete