As you are probably aware FDMEE is great at processing and loading data but not so good when it comes to metadata, currently the only way to load metadata without customisation is with a supported ERP source system and even then, the functionality is pretty limited.
In the past I wrote about a way to handle Planning metadata through FDMEE using a custom jython script, so I thought it was time to turn to Essbase and look at a potential solution to building dimensions.
In the last post I demonstrated how easy it is in FDMEE to interact with the Essbase Java API using jython, continuing that theme the method I will go through in this post will also use the Java API.
I am going to take a different approach than I did with loading Planning metadata where it was all controlled by a custom script, this time I am going to create a custom application which will allow the metadata to be loaded into FDMEE before loading to an Essbase database.
In summary, the process flow will be to load a text file containing the Essbase dimension information to FDMEE, map the metadata, export to a text file and then build the dimension using an Essbase load rule.
As usual I am going to try and keep it as simple as possible, the aim here is not to provide a complex step by step guide but to plant ideas and then the rest is up to you.
So let us get on with it, I have put together a source comma separated file which is in parent/child format, the idea is to load it to FDMEE, map, export and then perform a dimension build to the existing product dimension in everybody’s favourite Sample Basic database.
In FDMEE a new custom target application is created.
New dimensions are added to the custom application to match the source file, I understand that in this scenario they are not dimension names and they are dimension build properties but usually you would be loading data by dimension to a target application, as this is a custom application and solution the concept of dimension can be ignored and thought more of as a property.
The source file is in the format of parent member, child member, alias, data storage and consolidation operator so the dimensions are added to reflect this, if there were additional columns in the source file they could easily be added into the custom application, even if there are properties that are required in the dimension build that are not in the source file they could be generated in FDMEE.
One of the properties needs to be assigned a target class of Account for the solution to work, it is not important which one and the remaining can be set a generic.
It is important to note that when working with a custom application the order of the data that is written to the output file will be defined by the order of “Data Table Column Name”, the order is defined as Account, Entity, UD1, UD2 to UD20, AMOUNT.
So in my example the output file will be in the order of ACCOUNT, UD1, UD2, UD3, UD4 which maps to Parent, Child, Alias, DataStorage, Consolidation.
On to the import format, the source is set as a comma delimited file and the target is the custom application that has just been created.
The source columns and column number from the file are mapped to the target, you will notice that there is a target amount column which is added by default, I am not interested in this target column and it is not present in the source but it needs to exist, I just map the source field to 1 and the value to 1 which will become apparent later.
There is nothing to really to say about the location as it is just the default values with the import format is selected.
A new data load rule is created, the import format and source file name are selected and I uploaded the source file to the FDMEE inbox.
In the rule target options the property value has been set to enable the export to a file and the column delimiter will be comma, the export file is required as this will be then used for the dimension build using an Essbase load rule.
In the custom options for the rule I have added some integration options, they basically define the Essbase application, database, dimension and rule name, it will be clearer how they are used later when I go through the jython script.
I have kept the data load mappings extremely simple and in the main they are like for like mappings, though this is where you could get as complex and creative as you like depending how your source file differs from the target dimension build file.
I did add explicit mappings for the data storage member property as the source file contains a more meaningful name than the property values required for an Essbase dimension build.
The Essbase administrator documentation has a table containing all the property codes and the description.
At this point I can run a data load to import the source file, map and then export.
From the workbench, you can see the full import to export process has been successful.
The source to target mappings can be viewed and you will also notice there is an amount column which I fixed to a value of 1 back in the import format.
The output data file name will be generated based on <target_application_name>_<process_id>.dat and will be written to <application_root_folder>\outbox directory.
The output file is ready for a dimension build using an Essbase load rule.
I am not going to go through the process of how to build a load rule in the EAS console but here is the completed version.
As the file has a header record this has been set to be skipped in the load rule, the amount column has been ignored in the field properties of the rule.
The rule is named the same as the integration option value which was defined earlier in the FDMEE load rule.
The dimension could now be built using the rule and file but we are going to get FDMEE to do that using a jython script.
If you look in the FDMEE log for the process that was just executed you will see reference to jython event scripts that are called at different stages throughout the process.
For example, after the export file has been created there will be the following in the log
INFO [AIF]: Executing the following script: <application_root_folder>/data/scripts/event/AftExportToDat.py
The scripts are not there by default so you may get a warning saying the script does not exist, if they don’t exist it is just a matter of creating the script and it will be executed next time the process is run.
Please be aware that if event scripts have been enable and the script exists it will always be executed so you need to code it so it triggers only the section of the script you are interested in for this process.
I am going to use the above event script to carry out the dimension build using the Essbase Java API.
Now I am not going to go through every single line of the jython script I have wrote and only stick to the important sections, the script does contain comments so hopefully it provides you enough information.
In summary, the Essbase classes that are required to perform a dimension build are imported.
The target application name and process ID are stored in variables.
The values from the integration options in the FDMEE load rule are stored using the API method “getRuleDetails”, these are held in “RULE_ATTRx”
The target Essbase application and database name are then generated from the retrieved values.
The full path to the exported text file and dimension build error file are generated.
The next section is where the Essbase JAVA API comes into play, a login to the Essbase server is made using a single sign-on token so no clear text passwords are stored.
A custom function is called which adds some additionally logging to the process logs which I will show later, it is not actually necessary to do this.
The dimension build is run using the “buildDimension” method passing in the stored rule name, load and error file.
If an error file is generated it is read and the errors are added to the process log.
Now that the jython is in place the export stage of the FDMEE load rule can be run again.
The process details confirm that the export and dimension build were successful, the dimension build file can also be downloaded.
The process steps include the additional custom logging I was referring to earlier.
Opening the outline in the EAS console shows the new members and properties have been successfully created in the product dimension.
Let me demonstrate what happens when dimension build errors occur.
This time I have added an invalid record to the source file which is highlighted above, the full data load process is then executed again.
Instead of a green tick, process details displays a warning icon which was generated using the custom logging function in the jython event script.
The process log contains the full location to the dimension build error file and includes the rejections in the log.
Now we have the option to load data and metadata to a target Essbase database.
You don’t have to use the custom application method, if the source file does not need any kind of mapping or require visibility of what is being loaded through the dimension build, then it could all be done with a single FDMEE custom script which would be practically along the same lines as the code in the event script.
So what if your source is not a file and is a relational database, what if you want to be able to run incremental builds for multiple dimensions, what if you don’t want to create an export file and instead have an Essbase SQL dimension build? Well look out for part 2 where all of these questions will be answered.
In the past I wrote about a way to handle Planning metadata through FDMEE using a custom jython script, so I thought it was time to turn to Essbase and look at a potential solution to building dimensions.
In the last post I demonstrated how easy it is in FDMEE to interact with the Essbase Java API using jython, continuing that theme the method I will go through in this post will also use the Java API.
I am going to take a different approach than I did with loading Planning metadata where it was all controlled by a custom script, this time I am going to create a custom application which will allow the metadata to be loaded into FDMEE before loading to an Essbase database.
In summary, the process flow will be to load a text file containing the Essbase dimension information to FDMEE, map the metadata, export to a text file and then build the dimension using an Essbase load rule.
As usual I am going to try and keep it as simple as possible, the aim here is not to provide a complex step by step guide but to plant ideas and then the rest is up to you.
So let us get on with it, I have put together a source comma separated file which is in parent/child format, the idea is to load it to FDMEE, map, export and then perform a dimension build to the existing product dimension in everybody’s favourite Sample Basic database.
In FDMEE a new custom target application is created.
New dimensions are added to the custom application to match the source file, I understand that in this scenario they are not dimension names and they are dimension build properties but usually you would be loading data by dimension to a target application, as this is a custom application and solution the concept of dimension can be ignored and thought more of as a property.
The source file is in the format of parent member, child member, alias, data storage and consolidation operator so the dimensions are added to reflect this, if there were additional columns in the source file they could easily be added into the custom application, even if there are properties that are required in the dimension build that are not in the source file they could be generated in FDMEE.
One of the properties needs to be assigned a target class of Account for the solution to work, it is not important which one and the remaining can be set a generic.
It is important to note that when working with a custom application the order of the data that is written to the output file will be defined by the order of “Data Table Column Name”, the order is defined as Account, Entity, UD1, UD2 to UD20, AMOUNT.
So in my example the output file will be in the order of ACCOUNT, UD1, UD2, UD3, UD4 which maps to Parent, Child, Alias, DataStorage, Consolidation.
On to the import format, the source is set as a comma delimited file and the target is the custom application that has just been created.
The source columns and column number from the file are mapped to the target, you will notice that there is a target amount column which is added by default, I am not interested in this target column and it is not present in the source but it needs to exist, I just map the source field to 1 and the value to 1 which will become apparent later.
There is nothing to really to say about the location as it is just the default values with the import format is selected.
A new data load rule is created, the import format and source file name are selected and I uploaded the source file to the FDMEE inbox.
In the rule target options the property value has been set to enable the export to a file and the column delimiter will be comma, the export file is required as this will be then used for the dimension build using an Essbase load rule.
In the custom options for the rule I have added some integration options, they basically define the Essbase application, database, dimension and rule name, it will be clearer how they are used later when I go through the jython script.
I have kept the data load mappings extremely simple and in the main they are like for like mappings, though this is where you could get as complex and creative as you like depending how your source file differs from the target dimension build file.
I did add explicit mappings for the data storage member property as the source file contains a more meaningful name than the property values required for an Essbase dimension build.
The Essbase administrator documentation has a table containing all the property codes and the description.
At this point I can run a data load to import the source file, map and then export.
From the workbench, you can see the full import to export process has been successful.
The source to target mappings can be viewed and you will also notice there is an amount column which I fixed to a value of 1 back in the import format.
The output data file name will be generated based on <target_application_name>_<process_id>.dat and will be written to <application_root_folder>\outbox directory.
The output file is ready for a dimension build using an Essbase load rule.
I am not going to go through the process of how to build a load rule in the EAS console but here is the completed version.
As the file has a header record this has been set to be skipped in the load rule, the amount column has been ignored in the field properties of the rule.
The rule is named the same as the integration option value which was defined earlier in the FDMEE load rule.
The dimension could now be built using the rule and file but we are going to get FDMEE to do that using a jython script.
If you look in the FDMEE log for the process that was just executed you will see reference to jython event scripts that are called at different stages throughout the process.
For example, after the export file has been created there will be the following in the log
INFO [AIF]: Executing the following script: <application_root_folder>/data/scripts/event/AftExportToDat.py
The scripts are not there by default so you may get a warning saying the script does not exist, if they don’t exist it is just a matter of creating the script and it will be executed next time the process is run.
Please be aware that if event scripts have been enable and the script exists it will always be executed so you need to code it so it triggers only the section of the script you are interested in for this process.
I am going to use the above event script to carry out the dimension build using the Essbase Java API.
Now I am not going to go through every single line of the jython script I have wrote and only stick to the important sections, the script does contain comments so hopefully it provides you enough information.
In summary, the Essbase classes that are required to perform a dimension build are imported.
The target application name and process ID are stored in variables.
The values from the integration options in the FDMEE load rule are stored using the API method “getRuleDetails”, these are held in “RULE_ATTRx”
The target Essbase application and database name are then generated from the retrieved values.
The full path to the exported text file and dimension build error file are generated.
The next section is where the Essbase JAVA API comes into play, a login to the Essbase server is made using a single sign-on token so no clear text passwords are stored.
A custom function is called which adds some additionally logging to the process logs which I will show later, it is not actually necessary to do this.
The dimension build is run using the “buildDimension” method passing in the stored rule name, load and error file.
If an error file is generated it is read and the errors are added to the process log.
Now that the jython is in place the export stage of the FDMEE load rule can be run again.
The process details confirm that the export and dimension build were successful, the dimension build file can also be downloaded.
The process steps include the additional custom logging I was referring to earlier.
Opening the outline in the EAS console shows the new members and properties have been successfully created in the product dimension.
Let me demonstrate what happens when dimension build errors occur.
This time I have added an invalid record to the source file which is highlighted above, the full data load process is then executed again.
Instead of a green tick, process details displays a warning icon which was generated using the custom logging function in the jython event script.
The process log contains the full location to the dimension build error file and includes the rejections in the log.
Now we have the option to load data and metadata to a target Essbase database.
You don’t have to use the custom application method, if the source file does not need any kind of mapping or require visibility of what is being loaded through the dimension build, then it could all be done with a single FDMEE custom script which would be practically along the same lines as the code in the event script.
So what if your source is not a file and is a relational database, what if you want to be able to run incremental builds for multiple dimensions, what if you don’t want to create an export file and instead have an Essbase SQL dimension build? Well look out for part 2 where all of these questions will be answered.
John - Thanks for the post. Would you be able to share the custom function you wrote to update process details. I am looking to add additional info for HFM
ReplyDelete