Sunday, 13 May 2018

FDMEE/Data Management - Managing period mappings - Part 2

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.


Before uploading the file, it is worth pointing out the following rules with the Excel interface:
  • Data is only inserted. It cannot be updated or deleted.
  • Data is not validated.
  • When FDMEE encounters a duplicate row, the row is skipped.
Once you are happy, the Excel file can be uploaded.


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.


Well that concludes the two-part look into managing FDMEE/Data Management period mappings.

Sunday, 6 May 2018

EPM Cloud – Limiting the use of an application through automation - Update

Back with a very quick update, recently I wrote a post about the different options for limiting the use of an EPM Cloud application to administrators and then returning it to all users.

At the time of writing the post, the only option to schedule this process was by creating a refresh database job and setting the enable use of application settings.


Moving forward to the 18.05 cloud release and there is now a new schedule job option called “Administration Mode”.


So if you know the exact times you want to limit access to the application, you can create a new job.


It is possible to run the job straight away which would really be the same as going to “System Settings and Defaults” from the navigator, this then provides the options to set the application maintenance mode.


Anyway, once you have defined the frequency you then have the option to enable the use of the application to administrators or all users.


You can then create another job to return the application back to all users.


This time you would select the use to “All users”.


When the scheduled job to limit the application to administrators runs, non-admins that are logged into the application will be automatically logged out, any new logins will be greeted with the following message:


This will be the case until the job to return the application to all users runs or the system setting is changed in the UI. The next time the schedule jobs are set to run will be available under pending jobs.


So what if you don’t know the timings of when you want to limit access to the application (as you may want to include it as part of a scripted process)? Well unfortunately it does not look like Oracle has decided to provide the option to do this using EPM Automate or the REST API yet. In this case you can look at the possibility of trying out the method I provided in my recent post.

I am hoping Oracle will include this at some point in the future and if they do I will provide a further update.


Monday, 23 April 2018

FDMEE/Data Management - Managing period mappings - Part 1

In today’s post I am going to start off by going back to basics as I have recently seen similar questions raised around period mapping, these usually go along the lines of: “Do you have to manually enter period mappings?”.

If you are new to on-premise FDMEE or the cloud based Data Management then you will probably be wondering where the import option is for period mappings, unfortunately at the time of writing there is still not an option to do this in the user interface.

Adding mappings manually is a cumbersome task and is not helped by the error messages you can be hit with if you don’t follow the correct process exactly.


All is not lost though as there are ways to handle the importing of period mapping and I am going to cover some possible solutions. If you have been around FDMEE or Data Management for a while then you will probably have your own solution.

In this post I am going to concentrate on Data Management but the same concept can be used with FDMEE, then in the next part I will focus on a possible method which will only be available with on-premise FDMEE.

To be able to demonstrate this first method I have manually added a couple of global mappings and replicated these against a single target in application mappings.
 

The next step is to extract these mappings, this can be achieved by using Migration or Lifecycle Management (LCM) in the on-premise world.


Once migration has been accessed then click “Data Management” to allow the selection of artifacts to be exported.


If you expand “Global Setup Artifacts” you will see “Period Mapping”, this artifact relates to the Global Period Mappings.

You will also see “File” which is part of the Source Period Mappings, this relates to the following in period mappings in the UI.
 

One reason why you might use file mappings is to load data where the period and years are in the rows of the source file, I covered this in a previous post which you can read about here.

Back to migration, under “Application Data” you will see “Application Period Mapping” and “Explicit Source Period Mapping
 

The “Application Period Mapping” artifact will export any period mappings which have been added for the target application, an example in the UI would be:


For the “Explicit Source Period Mapping” these are taken from the Source Mapping tab in the UI, an example would be if you select the source system as “EPM” and then select a source and target application:


For this post I am going to concentrate on the Global and Application Mapping, though it will be the same concept if you want to expand the solution to the other types of mappings.

Once the artifacts have been selected in migration and the export run, a snapshot will be generated.
 

The snapshot can be downloaded as a zip file and then extracted.

Once extracted, in the directory “\FDMEE-FDM Enterprise Edition\resource\Global Setup Artifacts” there will be an XML file which contains the global period mappings.
 

Opening the XML reveals the mappings which I entered into the Data Management UI earlier.


If you don’t really know much about XML then I can imagine the format of the file can be a little bewildering but don’t worry about that at the moment.

Hopefully you understand how the elements in the file map back to the UI in data management, for example, “Periodkey” in the file maps to the “Period Key” column in Data Management.

Under the directory “\FDMEE-FDM Enterprise Edition\resource\Application Data\<app type>\<app name>” you will see another XML file which holds the application period mappings.
 

The format of this file is pretty much the same as the global mapping file except for the element:

<Intsystemkey>Target App Name</Intsystemkey>


The order of the elements is slightly different from the global file but actually the order does not matter.

So you know the mappings can be exported, this means they also can be imported, all that would be required is to create the XML files in the correct format.

Having to go through the XML file manually and add new period mappings would be a tedious task, so how about an automated solution to make life easier.

In this first solution I am going to generate the XML from a simpler format which could be first defined in say an Excel file, for Global Mappings I created the following file with two entries for period mappings, the file could contain as many mappings as you like.
 

Alternatively, it could be produced directly into a text based file.


I have made the headings match those in the XML file, they don’t have to match, the only requirement is they are in the same order, so “Period Key” is first and “Year Target” last.

I considered different possible ways to generate the target XML file from the above text file. I tested with the XML functionality in Excel which I wasn’t overly impressed with.  I looked into VBA in Excel which was certainly possible but it required adding a reference to be able to work with XML objects or it was too messy.

I finally decided on PowerShell because it is easily accessible on any Windows machine, there is also a decent XML writer available which is not overcomplicated.

I am certainly not saying this is correct solution and you should pick the one that works out best for you, an Excel based solution could be perfectly acceptable, in the end PowerShell was my preferred option. Don’t worry if you don’t know PowerShell as you should be able to reuse the script by just updating the variables in it.

Let me briefly go over the script, it does contain comments so I am not going to go into too much detail.

The first section of the script contains the variables, some of these are constants like the snapshot directory structure and the period mapping file, the ones that would require updating are the base directory of the extracted snapshot and text file contain the mappings.

There are the elements which map to the XML file, the order of these should match the order in the text file.
 

The remaining section of the script basically creates a new XML document, cycles through the lines from the source text file containing the period mappings and writes then in XML format.


After running the script, the source text file with the mappings has been transformed into the correct XML format in the snapshots period mapping file.


The snapshot directory structure can be compressed again into a zip file.


Then uploaded to the EPM cloud snapshot area.


Next, the snapshot is imported.


I know the process to compress, upload and import could be automated but as they are simple tasks that would not require repeating too often I left them as manual.

After the import has completed, the new mappings are available as Global Mappings in Data Management.
 

This solution works well and can be adapted to work across any of the period mapping types, the downside is you still have to produce the text file with the mappings, how about taking that a step further and letting the script do all the work.

Based on the same format for the mappings as I have used above I came up with a new script, it takes user input to enter a start month and period and the number of months to generate in the period mapping file.

The first section is nearly identical to the previous script, the only difference is the reading in of the variables for the start month/period and number of months.
 

The main section of the script operates in a similar way to the previous script, except this time the period key, prior period key, period name, target period month and year target are all calculated.


The script generates period keys based on the last day of the month but could easily be updated to suite any range for the key.

An example of running the script to produce a mapping file that starts from Jan 2020 and produces two months of output would be:
 

I am only selecting two months for demo purposes, the file can be generated with any number of months.

Once the script has been run, the file is generated in the defined snapshot location and in the required XML format.
 

The snapshot can be compressed to a zip again, uploaded and imported.

The new mappings are available after the import has completed.
 

With a few changes and additions, the script can generate application period mappings.

I am not going to show the full script, only the differences to the previous one.
 

The variable for the directory location for application period mappings is different than the global one, the mapping file is also different.

The elements array includes “Intsystemkey” and the value is the target application name which is read from user input.

The only difference in the main section of the script is due to the extra element, so the check goes from 4-6 to 5-7
 

An example to generate an application mapping file for the application “Vision” for two months, starting from Jan 2020 would be:


The application period mapping file is then generated based on the input.


Again, zip the snapshot, upload and import then the application mappings will be available in Data Management.


This type of solution can be implemented across EPM Cloud or on-premise and can be used again and again.

In the next part I will look at a possible solution that is only available for on-premise as it based around FDMEE custom scripting and direct updates to the repository database tables.