A delve into the disturbing world of EPM, uncovering the mysteries and facts beneath the shiny surface, yawn your way through yet another blog, let's hope this one is interesting.
In the last part, I went through Data Management/FDMEE period mappings and possible methods to automate populating them. The solution concentrated on the cloud but could still be used with on-premise, the idea was to update the XML period mapping files generated by snapshots and then import them back to generate the new mappings.
With Data Management in the cloud there is currently no custom Jython scripting allowed, so this pushes you to develop some solutions outside of the cloud. With on-premise FDMEE it is a different story, as you are able to build the solutions into the product using custom scripting. Also with on-premise FDMEE you have the option of the Excel interface which allows you to directly load files to the FDMEE database tables.
In this post I am going to go through a couple of possible methods to update period mappings directly from FDMEE.
In the FDMEE, all the period mapping in the UI are populated by reading the information from a database table.
For global period mappings, these are populated from a table named “TPOVPERIOD”
To be able to update the mapping table directly you can go to the Excel interface in the UI and select the entity type as “Period Mapping”. Select a file and location and download.
You can then populate the Excel template.
The FDMEE database table has had information from the Excel file inserted.
Back in the UI, the global period mappings now contain the two new mappings.
This is all fine, but what if you want something more dynamic where you don’t have to be bothered with populating Excel files? Well this is where a bit of custom scripting could help.
I am going to go through an example of updating the global and application period mapping tables by using a custom script, this will have parameters to define the start month/year and number of months to generate.
The application period mappings are stored in a table named “TPOVPERIODADAPTOR”, the only difference from the global mapping table is the “intsystemkey” column which holds the target application name.
First a new script is registered in FDMEE, I will get on to the details of the Jython script later.
There are four parameters, one which defines which defines the start month, instead of allowing direct input to minimise errors, a query type has been used to generate the periods (based on Oracle database).
Another query has been created to generate years where a start year can be selected.
The number of months parameter has been set as static so they are manually entered.
The target application name is defined by a SQL query, there is one already by default with FDMEE.
On to executing the script, the group and script are selected.
When the script is executed a window is displayed with the available parameters.
If start month is selected, a list of months is displayed for selection.
If start year is selected, a list of years is displayed for selection.
For this example, I am going to generate four months of period mappings, any number can be entered.
I have left the target application blank as I just want to update the global mapping period table.
After executing the script, a message is displayed to inform how many rows have been inserted into the period mapping table.
The database table has been populated with the 4 months of mappings.
These are available in the UI.
If I run the script again and this time select the application, a list of target applications is available for selection.
I selected the Vision application and to generate 3 months of mappings.
Once executed, a message confirms 3 rows have been inserted into the mapping table.
The application period mapping database table has been updated with the 3 new mappings.
These are then available under application mappings in the UI.
If I run the script again with the same parameter values, the message this time informs that no rows were inserted as the period keys already exist.
On to the Jython script that does all the work to populate the mapping tables.
I am not going to go through it in detail as the script is already commented so should give you a good idea what is happening.
In summary, the start month, year, number of months and target application parameter values are retrieved and stored.
There are similar SQL statements depending on whether a target application has been selected or not. There is a query to count if there are duplicate period mappings and an insert statement to the relevant period mapping table.
The start month and year are converted into a valid date.
A loop cycles through the number of months that need to be populated into the mapping table.
The period key is generated by calculating the last day of the month for the current date and appended to the SQL parameters.
The last period key, period description and year target are generated and appended to the SQL parameters.
The target period quarter, year and day are not used in this example so nulls are generated in the SQL parameters.
The query is executed to check if a period key already exists for the current period key that will be inserted into the mapping table.
If there is already an existing period key, store the duplicate key.
If there is not an existing period key, insert the period information into the mapping table.
Then the date moves forward one month and the process is repeated until all months have been looped through.
Finally, a message is displayed to inform how many rows were inserted and if there were any period key duplicates.
If you are running FDMEE 11.1.2.4.210+ then you can take advantage of the REST API to execute the custom script, I have covered the FDMEE REST API in detail and you can read about it starting here
An example using a REST client to generate 12 months of mapping starting from January 2021 would be:
Once the script has completed the period mappings will be available in the UI.
This could be converted to a script where input is taken, the REST API is called to run the custom script which then generates the period mappings.
The period mappings will then be available under application mapping.
