In past blogs I have covered in detail, automation in EPM Cloud using the REST API. Recently I have blogged comprehensively on the Essbase REST API in OAC, so I thought I would combine these and go through an example of automating the process of moving data between EPM Cloud and OAC Essbase.
The example will be based on extracting forecast data from PBCS using Data Management, downloading the data and then loading this to an OAC Essbase database. I will provide an option of downloading data directly to OAC from PBCS for those who have a customer managed OAC instance, alternatively for autonomous OAC the data can be downloaded from PBCS to a client/server before loading to Essbase.
I am going to break this into two parts, with the first part covering the setup and manual steps to the process, then the second part gets into the detail of automating the full process with the REST API and scripting.
Before I start I would like to point out this is not the only way to achieve the objective and I am not stating that this is the way it should be done, it is just an example to provide an idea of what is possible.
To start out with I am going to want to extract forecast data from PBCS and here is a sample of the data that will be extracted:
To extract the data, I am going to use Data Management, once the integration has been defined I can add automation to extract the data using the REST API.
As it is EPM Cloud, I will need to extract the data to a file and this can be achieved by creating a custom target application in Data Management.
The dimensions have been created to match those of the OAC Essbase database, I could have included scenario but that is always going to be static so can be handled on the Essbase side.
There are slight differences between the format of the Year in PBCS
to that in the Essbase database.
Aliases could be used but I want to provide an example of how the difference can be handled with period mappings in Data Management.
This will mean any data against, say FY19, in PBCS will be mapped to 2019 in the target output file.
If there are any differences between other members these can be handled in data load mappings in DM.
In the DM data load rule, source filters are created to define the data that will be extracted
In the target options of the file a fixed filename has been added, this is just to make the process of downloading the file easier. If this is not done, you would need to either capture the process ID from the REST response to generate the filename or read the filename from the jobs REST response, both methods produce the same outcome but, in this example, I am going for the simpler option.
Before running the integration, I will need to know which start and end period to select.
For the automated process I am going to pick this up from a substitution variable in Essbase, it would be the same concept if the variable is held in PBCS as both have a REST resource available to extract the information.
The data will be extracted for a full year, so based on the above sub var, the start period would be Jan-19 and the end period Dec-19
Running the rule will extract the data from PBCS, map and then produce an output file.
The rule ran successfully so the exported file will be available in the inbox/outbox explorer.
If I download the file you can see the format of the exported data.
When I cover the automation in the next part I will provide two options, the first one will download the data file directly to OAC from PBCS and then load the data, the second will download the file from PBCS to a machine running the automation script and then stream load it to Essbase.
As this post is about manually going through the process, I have downloaded and the file from PBCS and uploaded to OAC Essbase.
The file has been uploaded to the Essbase database directory.
Now an Essbase data load rule is required to load the above file.
A new rule was created, and the uploaded data file selected.
The columns in the data file were mapped to the corresponding dimensions.
The data is always being loaded to the forecast scenario member and not contained in the file, so this was added to the datasource information.
As I mentioned earlier I could have easily included scenario in the data export file by adding the dimension to the target application in Data Management, it is up to you to decide which method you prefer.
Once created it will be available from the scripts tab and under rules.
To run the rule, head over to jobs in the user interface and select “Load Data”
The application, database, rule and data file can then be selected.
The status of the data load can then be checked.
This is a hybrid database there is no need to run a calculation script to aggregate the data, if aggregations or calcs were required to be run then you could simply add this into the process.
A retrieve on the data confirms the process from extracting data from PBCS to OAC Essbase has been successful.
You could apply this process to extracting data from OAC and loading to EPM Cloud, one way to do this could be to run an Essbase data export script, the export file could then be uploaded to EPM Cloud, and a Data Management rule run to map and load to the target application.
We have a process in place, but nobody wants to live in a manual world, so it is time to streamline with automation which I will cover in detail in the next part. Stay tuned!
The example will be based on extracting forecast data from PBCS using Data Management, downloading the data and then loading this to an OAC Essbase database. I will provide an option of downloading data directly to OAC from PBCS for those who have a customer managed OAC instance, alternatively for autonomous OAC the data can be downloaded from PBCS to a client/server before loading to Essbase.
I am going to break this into two parts, with the first part covering the setup and manual steps to the process, then the second part gets into the detail of automating the full process with the REST API and scripting.
Before I start I would like to point out this is not the only way to achieve the objective and I am not stating that this is the way it should be done, it is just an example to provide an idea of what is possible.
To start out with I am going to want to extract forecast data from PBCS and here is a sample of the data that will be extracted:
To extract the data, I am going to use Data Management, once the integration has been defined I can add automation to extract the data using the REST API.
As it is EPM Cloud, I will need to extract the data to a file and this can be achieved by creating a custom target application in Data Management.
The dimensions have been created to match those of the OAC Essbase database, I could have included scenario but that is always going to be static so can be handled on the Essbase side.
There are slight differences between the format of the Year in PBCS
to that in the Essbase database.
Aliases could be used but I want to provide an example of how the difference can be handled with period mappings in Data Management.
This will mean any data against, say FY19, in PBCS will be mapped to 2019 in the target output file.
If there are any differences between other members these can be handled in data load mappings in DM.
In the DM data load rule, source filters are created to define the data that will be extracted
In the target options of the file a fixed filename has been added, this is just to make the process of downloading the file easier. If this is not done, you would need to either capture the process ID from the REST response to generate the filename or read the filename from the jobs REST response, both methods produce the same outcome but, in this example, I am going for the simpler option.
Before running the integration, I will need to know which start and end period to select.
For the automated process I am going to pick this up from a substitution variable in Essbase, it would be the same concept if the variable is held in PBCS as both have a REST resource available to extract the information.
The data will be extracted for a full year, so based on the above sub var, the start period would be Jan-19 and the end period Dec-19
Running the rule will extract the data from PBCS, map and then produce an output file.
The rule ran successfully so the exported file will be available in the inbox/outbox explorer.
If I download the file you can see the format of the exported data.
When I cover the automation in the next part I will provide two options, the first one will download the data file directly to OAC from PBCS and then load the data, the second will download the file from PBCS to a machine running the automation script and then stream load it to Essbase.
As this post is about manually going through the process, I have downloaded and the file from PBCS and uploaded to OAC Essbase.
The file has been uploaded to the Essbase database directory.
Now an Essbase data load rule is required to load the above file.
A new rule was created, and the uploaded data file selected.
The columns in the data file were mapped to the corresponding dimensions.
The data is always being loaded to the forecast scenario member and not contained in the file, so this was added to the datasource information.
As I mentioned earlier I could have easily included scenario in the data export file by adding the dimension to the target application in Data Management, it is up to you to decide which method you prefer.
Once created it will be available from the scripts tab and under rules.
To run the rule, head over to jobs in the user interface and select “Load Data”
The application, database, rule and data file can then be selected.
The status of the data load can then be checked.
This is a hybrid database there is no need to run a calculation script to aggregate the data, if aggregations or calcs were required to be run then you could simply add this into the process.
A retrieve on the data confirms the process from extracting data from PBCS to OAC Essbase has been successful.
You could apply this process to extracting data from OAC and loading to EPM Cloud, one way to do this could be to run an Essbase data export script, the export file could then be uploaded to EPM Cloud, and a Data Management rule run to map and load to the target application.
We have a process in place, but nobody wants to live in a manual world, so it is time to streamline with automation which I will cover in detail in the next part. Stay tuned!
No comments:
Post a Comment
Note: only a member of this blog may post a comment.