On to the final part of the series looking at Oracle Rest Data Services (ORDS) and EPM, a quick recap of the previous posts:
Part 1 – Overview of ORDS and process to get up and running with it.
Part 2 – ORDS administration using SQL Developer and “AutoREST” functionality.
Part 3 – Developing REST resources using SQL Developer and protecting resources with security.
In this final instalment I will go through a few REST examples using ORDS with EPM and cover some of the functionality that has not been discussed up to now.
I am going to assume you have read through Parts 1 to 3 so have a good understanding of what ORDS is all about.
In the last part I went through an example of creating a resource against a planning applications audit database table, the audit table is probably one of the most common tables you would want to access directly as currently with on-premise planning there is no access to the data through the user interface.
So say you have provided one or more REST resources to the table and delivered this in a structured format, up to now I have only used JSON as the data format but it is also possible to change this to deliver the content in CSV format.
To do this I am going to take my existing “audit” module and select “Add Template”
I am going to name this resource “expcsv”
In the “Data Format” drop-down list there are the options of JSON or CSV so for this example I have chosen CSV
I don’t believe the pagination size is honoured when using CSV so be careful to restrict what is returned using SQL if the results contain a large amount of rows.
I copied the same SQL from the example in Part 2 and uploaded the module.
If you enter the resource URL into a browser or REST client the response will be outputted in CSV format.
In scripting terms, it is extremely simple to generate a CSV file from the REST resource.
Now you have a locally generated CSV file, in the desired format and can be delivered whenever required.
I know this would be beneficial for many planning on-premise users out there.
Let us take another example that could be useful in the planning community and that is dimension metadata.
There are a number of standard ways to get dimension information out of planning but it is not so customisable and this is where SQL and ORDS can deliver.
I have created a new module and template in exactly the same way as all my examples.
As for the SQL to generate if you have a search on the internet there are some resources out there which will get you on your way.
I have created a SQL query to return product member information from the demo planning application.
The query generates the following output:
I have kept it simple and the SQL can be enhanced to output any of the member property information.
Once the module has been uploaded the dimension metadata will be available in JSON or CSV format depending on the data format type chosen.
Once again this is very easy to translate into a script and with a few lines of code you have access to the metadata which you can then further process if required.
Right, time to take it one step further and look at changing settings in a planning application, please be aware this is just an example and I am only demonstrating it to show what is possible with ORDS.
A common setting change in a planning application is maintenance mode, it is certainly possible to do this via command line but wouldn’t it be nice to have a REST resource to achieve this functionality.
I am going to create a new module called “maint”
In the template I am going to use a bind variable called “type” using the format {varname}
When using the resource, you put a value into the URL for the variable and then this is passed in and the value can be accessed which I will demonstrate shortly.
For the first time I am going to be using “PL/SQL” as the source type as this allows us to get a little more creative than just using SQL.
With the resource created the PL/SQL can be created in exactly the same way as when using SQL.
The table HSP_SYSTEMCFG has a column called LOGIN_LEVEL which basically relates to the maintenance mode.
If the value is 0 then all users can access the planning application, if 2 then only administrators can access and if 3 then just the owner.
There is a possible value of 1 which means interactive but that is not part of the functionality in the user interface or through the command line so I am leaving it out.
In the script there is the variable “:type” and the value in the REST resource URL will be passed into this variable, I am using friendly names for the variable instead of a number.
As planning uses a caching system it is not enough to just update the table, to get around the caching the HSP_ACTION table can be populated to update the cache, if you want to understand more about updating the cache then you can read all about it here.
To put this into action couldn’t be easier and can be achieved in a browser or a one-line script.
To put the application into administrator maintenance mode the URL includes “admin”
If a success response is returned the maintenance mode should have been updated
A quick check in the planning application confirms the setting has been applied.
The application will not be accessible for planning users.
By entering “all” into the request the application be can be returned to all user access.
The maintenance mode setting has been successfully updated.
Users can now access the application.
So this solution provides an effective alternative to using the command line utility without the hassle of having to run it from the planning server.
I know that the focus up to now has been around planning so let us look at a few other EPM areas.
The Shared Services database has a lot of useful content, take LCM for example and migration status information.
The migration status report can be accessed through Shared Services but there is no option to automatically generate this information for download and no way to customise it.
ORDS can come to the rescue again, I created a new module and two resources, one for returning LCM status information and the other for LCM failures based on passing in the migration ID.
The SQL is based on tables starting with "LCM_*" and the output provides more information than is currently available through the Shared Services report.
In scripting terms, it is doesn’t take much effort to produce an enhanced migration status report.
Filtering the returned rows to show only failures could be achieved using parameters in the URL or in a script.
The response provides the migration ID which then can be used in the second resource to return detailed information about a failure.
This provides quick access to details on why the migration failed without the requirement to log into Shared Services.
A report could be automatically generated and distributed which is currently not possible through Shared Services.
Moving on to FDMEE, a mass of information to choose from but how about batch scheduler details which is currently not easily obtained without having to log into the ODI studio or console.
A bit of custom SQL and batch schedule details can be returned with ease.
The same goes for scripting the output.
Next on to Calculation Manager, wouldn’t it be nice to be able to get a report of all the rules and the script content.
This can be achieved by either going to the calc manager database tables or the planning applications, I found it easier to parse the entries in the planning application tables.
The following example filters on a specific rule, if you wanted to return all the rules then just remove the filter parameters in the URL.
To replicate this with a script I created the following.
I then redirected this to a CSV file.
Now I have list of rules and script content which can be opened and viewed in Excel.
Not a bad way to document business rules and can be run whenever required without any convoluted processes
With all the examples I have been using the GET method so how about inserting data using the POST method, well say I have an automated process that loads member information from a database table, an easy way to populate this table would be to put it into the hands of ORDS to manage.
One way of doing this could by using the “AutoREST” functionality and all that would be required would to enable the database table for REST services and create an alias.
I created an alias of “prod” for table “VISPROD”, to insert a row into the table I could POST the following JSON.
The table will then have the row inserted.
This is fine for small inserts but what if I want an easier method to insert multiple rows using CSV format.
The good news is “AutoREST” also comes with a batch load option and is available to any table that has been REST enabled.
I can insert multiple rows using CSV format with the following operation.
The batch load functionality has many parameters available and in the above example I am truncating the table before inserting.
The response will include the number of records processed and failed.
The rows should then be visible in the table.
If I wanted to load data directly from a file, then this is pretty simple to do as well.
The post to the REST resource would just need the CSV file to be included.
To delete a row from a table can be achieved using the DELETE method and including filter parameters to specify the row.
The response will confirm the number of rows deleted.
Well I am going to leave it there for the series on ORDS as I have covered a hell of a lot, as you probably gathered I am a big fan of the product and if you are an Oracle database customer then I really don’t see why you wouldn’t consider using ORDS as there is no extra cost involved, there is so potential to assist in delivering beneficial solutions without a big hassle.
If you are interested in understanding more about ORDS and how it could help you then please feel free to get in touch.
Part 1 – Overview of ORDS and process to get up and running with it.
Part 2 – ORDS administration using SQL Developer and “AutoREST” functionality.
Part 3 – Developing REST resources using SQL Developer and protecting resources with security.
In this final instalment I will go through a few REST examples using ORDS with EPM and cover some of the functionality that has not been discussed up to now.
I am going to assume you have read through Parts 1 to 3 so have a good understanding of what ORDS is all about.
In the last part I went through an example of creating a resource against a planning applications audit database table, the audit table is probably one of the most common tables you would want to access directly as currently with on-premise planning there is no access to the data through the user interface.
So say you have provided one or more REST resources to the table and delivered this in a structured format, up to now I have only used JSON as the data format but it is also possible to change this to deliver the content in CSV format.
To do this I am going to take my existing “audit” module and select “Add Template”
I am going to name this resource “expcsv”
In the “Data Format” drop-down list there are the options of JSON or CSV so for this example I have chosen CSV
I don’t believe the pagination size is honoured when using CSV so be careful to restrict what is returned using SQL if the results contain a large amount of rows.
I copied the same SQL from the example in Part 2 and uploaded the module.
If you enter the resource URL into a browser or REST client the response will be outputted in CSV format.
In scripting terms, it is extremely simple to generate a CSV file from the REST resource.
Now you have a locally generated CSV file, in the desired format and can be delivered whenever required.
I know this would be beneficial for many planning on-premise users out there.
Let us take another example that could be useful in the planning community and that is dimension metadata.
There are a number of standard ways to get dimension information out of planning but it is not so customisable and this is where SQL and ORDS can deliver.
I have created a new module and template in exactly the same way as all my examples.
As for the SQL to generate if you have a search on the internet there are some resources out there which will get you on your way.
I have created a SQL query to return product member information from the demo planning application.
The query generates the following output:
I have kept it simple and the SQL can be enhanced to output any of the member property information.
Once the module has been uploaded the dimension metadata will be available in JSON or CSV format depending on the data format type chosen.
Once again this is very easy to translate into a script and with a few lines of code you have access to the metadata which you can then further process if required.
Right, time to take it one step further and look at changing settings in a planning application, please be aware this is just an example and I am only demonstrating it to show what is possible with ORDS.
A common setting change in a planning application is maintenance mode, it is certainly possible to do this via command line but wouldn’t it be nice to have a REST resource to achieve this functionality.
I am going to create a new module called “maint”
In the template I am going to use a bind variable called “type” using the format {varname}
When using the resource, you put a value into the URL for the variable and then this is passed in and the value can be accessed which I will demonstrate shortly.
For the first time I am going to be using “PL/SQL” as the source type as this allows us to get a little more creative than just using SQL.
With the resource created the PL/SQL can be created in exactly the same way as when using SQL.
The table HSP_SYSTEMCFG has a column called LOGIN_LEVEL which basically relates to the maintenance mode.
If the value is 0 then all users can access the planning application, if 2 then only administrators can access and if 3 then just the owner.
There is a possible value of 1 which means interactive but that is not part of the functionality in the user interface or through the command line so I am leaving it out.
In the script there is the variable “:type” and the value in the REST resource URL will be passed into this variable, I am using friendly names for the variable instead of a number.
As planning uses a caching system it is not enough to just update the table, to get around the caching the HSP_ACTION table can be populated to update the cache, if you want to understand more about updating the cache then you can read all about it here.
To put this into action couldn’t be easier and can be achieved in a browser or a one-line script.
To put the application into administrator maintenance mode the URL includes “admin”
If a success response is returned the maintenance mode should have been updated
A quick check in the planning application confirms the setting has been applied.
The application will not be accessible for planning users.
By entering “all” into the request the application be can be returned to all user access.
The maintenance mode setting has been successfully updated.
Users can now access the application.
So this solution provides an effective alternative to using the command line utility without the hassle of having to run it from the planning server.
I know that the focus up to now has been around planning so let us look at a few other EPM areas.
The Shared Services database has a lot of useful content, take LCM for example and migration status information.
The migration status report can be accessed through Shared Services but there is no option to automatically generate this information for download and no way to customise it.
ORDS can come to the rescue again, I created a new module and two resources, one for returning LCM status information and the other for LCM failures based on passing in the migration ID.
The SQL is based on tables starting with "LCM_*" and the output provides more information than is currently available through the Shared Services report.
In scripting terms, it is doesn’t take much effort to produce an enhanced migration status report.
Filtering the returned rows to show only failures could be achieved using parameters in the URL or in a script.
The response provides the migration ID which then can be used in the second resource to return detailed information about a failure.
This provides quick access to details on why the migration failed without the requirement to log into Shared Services.
A report could be automatically generated and distributed which is currently not possible through Shared Services.
Moving on to FDMEE, a mass of information to choose from but how about batch scheduler details which is currently not easily obtained without having to log into the ODI studio or console.
A bit of custom SQL and batch schedule details can be returned with ease.
The same goes for scripting the output.
Next on to Calculation Manager, wouldn’t it be nice to be able to get a report of all the rules and the script content.
This can be achieved by either going to the calc manager database tables or the planning applications, I found it easier to parse the entries in the planning application tables.
The following example filters on a specific rule, if you wanted to return all the rules then just remove the filter parameters in the URL.
To replicate this with a script I created the following.
I then redirected this to a CSV file.
Now I have list of rules and script content which can be opened and viewed in Excel.
Not a bad way to document business rules and can be run whenever required without any convoluted processes
With all the examples I have been using the GET method so how about inserting data using the POST method, well say I have an automated process that loads member information from a database table, an easy way to populate this table would be to put it into the hands of ORDS to manage.
One way of doing this could by using the “AutoREST” functionality and all that would be required would to enable the database table for REST services and create an alias.
I created an alias of “prod” for table “VISPROD”, to insert a row into the table I could POST the following JSON.
The table will then have the row inserted.
This is fine for small inserts but what if I want an easier method to insert multiple rows using CSV format.
The good news is “AutoREST” also comes with a batch load option and is available to any table that has been REST enabled.
I can insert multiple rows using CSV format with the following operation.
The batch load functionality has many parameters available and in the above example I am truncating the table before inserting.
The response will include the number of records processed and failed.
The rows should then be visible in the table.
If I wanted to load data directly from a file, then this is pretty simple to do as well.
The post to the REST resource would just need the CSV file to be included.
To delete a row from a table can be achieved using the DELETE method and including filter parameters to specify the row.
The response will confirm the number of rows deleted.
Well I am going to leave it there for the series on ORDS as I have covered a hell of a lot, as you probably gathered I am a big fan of the product and if you are an Oracle database customer then I really don’t see why you wouldn’t consider using ORDS as there is no extra cost involved, there is so potential to assist in delivering beneficial solutions without a big hassle.
If you are interested in understanding more about ORDS and how it could help you then please feel free to get in touch.
Awesome coding it is working thank you for sharing
ReplyDelete