There have been a few new additions to the EPM Cloud 17.07 release that I thought might be worth quickly running through.
There is a new version of the EPM Automate utility which includes a new command for clearing data, the new features and announcements document provides the following insight:
“A new version of the EPM Automate Utility is available with this update. This version includes the clearcube command, which uses the settings specified in a Planning job of type clear cube to delete specific data from input and reporting cubes.”
To coincide with this there is also the option to clear data using the REST API which I will get on to later.
There have also been some changes for clearing data in the UI, the documentation states:
“The Clear Cube job options have been updated to enable you to clear both Essbase and relational data. Previously you could only clear Essbase data. To view the updated Clear Cube job options, click Application, then Overview. From the Actions menu, select Clear Cube. On the Clear Cube page, click Create, complete the job name, select the cube, then select from the clear options displayed. Oracle has also provided two modes for clearing partial data for aggregate storage cubes. Selecting Partial Data (Basic Mode) enables you to use the member selector to specify the regions to be cleared. Selecting Partial Data (Advanced Mode) enables you to clear Essbase data by using an MDX expression in the text box provided.”
So basically relational data can now be cleared as part of the clear cube job and the options for clearing ASO data have been split into a basic and advanced mode.
Before the July release the options in clear cube for BSO were:
The options for an ASO cube were:
If Partial Data was selected then you could choose whether it was going to be a logical clear:
“In which the input cells in the specified region are written to a new data slice with negative, compensating values that result in a value of zero for the cells you want to clear. The process for logically clearing data completes in a length of time that is proportional to the size of the data being cleared. Because compensating cells are created, this option increases the size of the database.”
or a physical clear:
“In which the input cells in the specified region are physically removed from the aggregate storage database. The process for physically clearing data completes in a length of time that is proportional to the size of the input data, not the size of the data being cleared. Therefore, you might typically use this method only when you need to remove large slices of data.”
By using the member select you could define the area of the cube which you wanted to clear.
This would automatically generate MDX which was not always the best but at least it could be manually updated.
One of the problems with the clear cube data jobs was that if you had any relational type data like comments, supporting detail or attachments there was no option to clear them.
For example if I had the following data in a form which has a comment attached to a cell.
If the clear cube job was run the data would be removed but the comments would still remain.
Ok, so you could go to clear cell details from the navigator but that would mean defining the POV twice.
Moving swiftly on to the July release, let us see what changes have happened.
If you create a clear cube job you will see there is now the clear options where you can select from Supporting Details, Comments, Attachments and Essbase Data.
For ASO cubes, if Partial Data is selected then you have the new basic or advanced mode for defining the area of the database you would like to clear.
Using the Basic mode you can use the member selector to define where you want to clear data.
In the advanced mode you have to paste the MDX directly into the input box, if you select Basic mode first, define the POV and then switch to Advanced you will see the MDX that is going to be executed.
If you do switch to Advanced mode the relational type clear options will be deselected and unavailable, so keep watch out for that.
If you start with Advanced mode and enter an MDX statement:
Then switch to Basic Mode the dimension and members section will be blank, so it works going from Basic to Advanced but not the other way around which is understandable because it could be a complex MDX statement and that would be asking a little too much for it to be converted into the basic format.
It is possible to select any of the clear options, so for example if you only wanted to clear comments and not Essbase data.
Let us take a quick example with a form that has data and a comment.
After only selecting comments in the clear options and running the job only the comments are deleted.
Before the July release once you had created a clear cube job the only way to run it was by submitting it from the UI or setting up a schedule, what was missing was the ability to run the clear from outside of the UI or predefined schedule.
At last this is now possible and starting with the 17.07 EPM Automate utility there is a new command called “clearcube” available.
The new command is extremely basic and will only run a clear job already defined in the UI, when I heard there was going to be a new command I was hoping that it would be possible to set up the various clear options and even pass in the MDX using command parameters but unfortunately that is not the case, maybe that will happen in the future or maybe I am just asking for too much.
The command usage is:
epmautomate clearcube
Time for another example and back to the same form with data and a comment.
The clear data job has been named “Clear Demo” and created with the following options:
Using the EPM Automate utility is as simple as issuing the command:
epmautomate clearcube “Clear Demo”
The command completed successfully and checking the jobs section in the UI we can see the job has been run.
Clicking on the job provides details on the options that have been executed as part of the clear.
Going back to the form and the Essbase data and comments have been cleared.
As the command to clear data is available in the EPM Automate utility this means it will also be available through the REST API,
If you have ever used the REST API to execute a job then it will be very familiar because it is the same resource just a slightly different payload.
The REST resource requires a POST to the URL format:
https://<cloud_instance>/HyperionPlanning/rest/{api_version}/applications/{application}/jobs
The payload will be in JSON and should include
jobType = CLEAR_CUBE
jobName = Clear cube job name
In my example this would equate to:
{
"jobType":"CLEAR_CUBE",
"jobName":"Clear Demo"
}
Before testing out the REST resource I updated the form to include supporting detail.
The clear cube job was updated to include supporting details.
Using a REST client I can make a POST to the resource with a payload to run the “Clear Demo” clear cube job.
The response includes much more information than working with the EPM Automate utility, it includes the details on the clear options that have been run as part of the job.
The response also includes a URL so a GET request can be made to return information about the job, this is useful if the job is in a running state so it can be repeatedly checked until it has completed.
The response contains status information and the job was completed successfully, a check of the form and the data, comments and supporting detail have been removed.
Finally, we can turn this to the scripting world, before creating a script I updated the form to include an attachment.
The clear options for the job were updated to include attachments.
I created a simple script to make a POST to the jobs REST resource including in the payload the job type and job name.
The response confirms the job was successfully completed and a check of the form shows the data, comment, supporting details and attachment have been cleared.
Well that completes a look at the new clear cube functionality, hopefully you have found it useful, until next time.
There is a new version of the EPM Automate utility which includes a new command for clearing data, the new features and announcements document provides the following insight:
“A new version of the EPM Automate Utility is available with this update. This version includes the clearcube command, which uses the settings specified in a Planning job of type clear cube to delete specific data from input and reporting cubes.”
To coincide with this there is also the option to clear data using the REST API which I will get on to later.
There have also been some changes for clearing data in the UI, the documentation states:
“The Clear Cube job options have been updated to enable you to clear both Essbase and relational data. Previously you could only clear Essbase data. To view the updated Clear Cube job options, click Application, then Overview. From the Actions menu, select Clear Cube. On the Clear Cube page, click Create, complete the job name, select the cube, then select from the clear options displayed. Oracle has also provided two modes for clearing partial data for aggregate storage cubes. Selecting Partial Data (Basic Mode) enables you to use the member selector to specify the regions to be cleared. Selecting Partial Data (Advanced Mode) enables you to clear Essbase data by using an MDX expression in the text box provided.”
So basically relational data can now be cleared as part of the clear cube job and the options for clearing ASO data have been split into a basic and advanced mode.
Before the July release the options in clear cube for BSO were:
The options for an ASO cube were:
If Partial Data was selected then you could choose whether it was going to be a logical clear:
“In which the input cells in the specified region are written to a new data slice with negative, compensating values that result in a value of zero for the cells you want to clear. The process for logically clearing data completes in a length of time that is proportional to the size of the data being cleared. Because compensating cells are created, this option increases the size of the database.”
or a physical clear:
“In which the input cells in the specified region are physically removed from the aggregate storage database. The process for physically clearing data completes in a length of time that is proportional to the size of the input data, not the size of the data being cleared. Therefore, you might typically use this method only when you need to remove large slices of data.”
By using the member select you could define the area of the cube which you wanted to clear.
This would automatically generate MDX which was not always the best but at least it could be manually updated.
One of the problems with the clear cube data jobs was that if you had any relational type data like comments, supporting detail or attachments there was no option to clear them.
For example if I had the following data in a form which has a comment attached to a cell.
If the clear cube job was run the data would be removed but the comments would still remain.
Ok, so you could go to clear cell details from the navigator but that would mean defining the POV twice.
Moving swiftly on to the July release, let us see what changes have happened.
If you create a clear cube job you will see there is now the clear options where you can select from Supporting Details, Comments, Attachments and Essbase Data.
For ASO cubes, if Partial Data is selected then you have the new basic or advanced mode for defining the area of the database you would like to clear.
Using the Basic mode you can use the member selector to define where you want to clear data.
In the advanced mode you have to paste the MDX directly into the input box, if you select Basic mode first, define the POV and then switch to Advanced you will see the MDX that is going to be executed.
If you do switch to Advanced mode the relational type clear options will be deselected and unavailable, so keep watch out for that.
If you start with Advanced mode and enter an MDX statement:
Then switch to Basic Mode the dimension and members section will be blank, so it works going from Basic to Advanced but not the other way around which is understandable because it could be a complex MDX statement and that would be asking a little too much for it to be converted into the basic format.
It is possible to select any of the clear options, so for example if you only wanted to clear comments and not Essbase data.
Let us take a quick example with a form that has data and a comment.
After only selecting comments in the clear options and running the job only the comments are deleted.
Before the July release once you had created a clear cube job the only way to run it was by submitting it from the UI or setting up a schedule, what was missing was the ability to run the clear from outside of the UI or predefined schedule.
At last this is now possible and starting with the 17.07 EPM Automate utility there is a new command called “clearcube” available.
The new command is extremely basic and will only run a clear job already defined in the UI, when I heard there was going to be a new command I was hoping that it would be possible to set up the various clear options and even pass in the MDX using command parameters but unfortunately that is not the case, maybe that will happen in the future or maybe I am just asking for too much.
The command usage is:
epmautomate clearcube
Time for another example and back to the same form with data and a comment.
The clear data job has been named “Clear Demo” and created with the following options:
Using the EPM Automate utility is as simple as issuing the command:
epmautomate clearcube “Clear Demo”
The command completed successfully and checking the jobs section in the UI we can see the job has been run.
Clicking on the job provides details on the options that have been executed as part of the clear.
Going back to the form and the Essbase data and comments have been cleared.
As the command to clear data is available in the EPM Automate utility this means it will also be available through the REST API,
If you have ever used the REST API to execute a job then it will be very familiar because it is the same resource just a slightly different payload.
The REST resource requires a POST to the URL format:
https://<cloud_instance>/HyperionPlanning/rest/{api_version}/applications/{application}/jobs
The payload will be in JSON and should include
jobType = CLEAR_CUBE
jobName = Clear cube job name
In my example this would equate to:
{
"jobType":"CLEAR_CUBE",
"jobName":"Clear Demo"
}
Before testing out the REST resource I updated the form to include supporting detail.
The clear cube job was updated to include supporting details.
Using a REST client I can make a POST to the resource with a payload to run the “Clear Demo” clear cube job.
The response includes much more information than working with the EPM Automate utility, it includes the details on the clear options that have been run as part of the job.
The response also includes a URL so a GET request can be made to return information about the job, this is useful if the job is in a running state so it can be repeatedly checked until it has completed.
The response contains status information and the job was completed successfully, a check of the form and the data, comments and supporting detail have been removed.
Finally, we can turn this to the scripting world, before creating a script I updated the form to include an attachment.
The clear options for the job were updated to include attachments.
I created a simple script to make a POST to the jobs REST resource including in the payload the job type and job name.
The response confirms the job was successfully completed and a check of the form shows the data, comment, supporting details and attachment have been cleared.
Well that completes a look at the new clear cube functionality, hopefully you have found it useful, until next time.
