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.
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.
No comments:
Post a Comment
Note: only a member of this blog may post a comment.