Sunday, 25 June 2017

EPM Cloud – Managing applications with Smart View continued

In the last post I went through in detail the ability to manage a PBCS application using Excel and Smart View, I wanted to cover what happens behind the scenes with Smart View and try to replicate the functionality but I ran out of time so here is a follow up post.

I understand going into detail might not be for everybody but in many ways it helps me to write down what I have discovered as I can refer back to it, I know it is a bit sad but I do find myself looking back on posts I have written, what is worse is when I can’t even remember writing them :)

There are currently three main tasks that are available in Smart View for managing applications:
  • Create Application
  • Update Application
  • Delete Application
In order to achieve the create and update functionality the Excel workbook containing the template information is posted to the planning web application.

If you don’t already know Smart View mainly operates by generating XML code, the XML is compressed using GZIP and is then posted to the web server, a compressed response is received which once again is in XML format.

So let us take a look at what happens when the create new application option is selected in Smart View, a post is made to:


The important part is the XML that is included in the body of the post, an example being:

The root node of the XML defines that it is a request to create an application, the “sID” node is an identifier that is always posted with planning Smart View requests, it is basically way of validating the request is being made from an authenticated user, the SID is first generated when connecting to the planning SV provider which I go into more detail later.

I think you will have already guessed what the “xlsFile” node is being used for but the value probably doesn’t make much sense, I have not included the full value as it is large because it contains the full excel template file, the value is a base64 encode of the original excel file.

If I decode the value then the binary version of the excel file is displayed.

The decoded text can be copied and then saved as a .xlsx fle.

If the file is opened it is the exact same template file that is open when creating the application.

On a side note an excel file is just a set of XML documents that have been saved as an archive file, they can be opened like any zip file, so if I open the excel file using 7zip it is possible to view the structure and XML documents in the file.

Anyway, once the post has been made from Smart View to the planning web application server to create the application then after a while a response will be returned in XML format.

Smart View will then convert this XML and display a message to the user.

Using the update application functionality operates in a similar fashion, a post is made to the same URL.

This time the XML that is posted contains a root node that defines it is a request to edit the application.

The XML includes the application name and once again the SID and Excel template file.

After the post has been successfully made and the application updated an XML response will be returned.

The XML contains the message that is then displayed to the user.

You will not be shocked to find out that the delete application operates in the same way, a post is made to the same URL with XML in the body of the post.

The XML defines that it is a request to delete an application, this time there is no need to include the excel file so only the application name and SID are included in the XML.

Once the application has been deleted an XML response will be returned which includes the message that is displayed to the end user.

Now we know what Smart View is doing behind the scenes then it should be possible to replicate this functionality outside of Smart View.

The first problem is that when you log into Smart View there is authentication done through Oracle access manager and then an authorisation cookie is created and included in Smart View requests, this would make replicating the functionality over complicated but luckily there is a simpler method.

If you every read my posts on load testing with the EPM Automate utility then you will know that Smart View requests can be made through REST calls, this allows the use of an basic authentication header in the requests just like when using any of the cloud REST APIs.

In all the previous Smart View requests that I have shown there has been a SID value, in order to obtain this we need to log into the cloud instance and generate an SSO token.

This can be achieved by making a GET request using the following URL format:


Like with all the EPM Cloud REST API resources the request requires a basic authorisation header which is a base64 encode of the users cloud credentials.

The response contains JSON which we are not actually interested in as there is no reference of SSO token.

It is actually the header of the response which contains the SSO token we are looking for.

Now that we have the token a new request can be made to the planning Smart View URL to return the SID.

A new URL format can be used which allows you to create equivalent XML requests that are made from Smart View:


The following POST connects to the planning Smart View provider passing in the SSO token in the XML body of the request.

This is the same XML request that is sent from Smart View when connecting to the planning provider and the XML response contains the all-important SID.

This means the next request could be to replicate the Smart View functionality to either create, update or delete an application.

To demonstrate this I am going to put this all together in a PowerShell script which will update the security in a PBCS application using the security worksheet template.

Any of the applications access permissions can be updated using the security worksheet but to keep it simple I am just going to update the security for one member and form folder.

Before I get questioned, the script is not exactly how the final version looks, it has just been written this way to make it easier to go through.

The first section of the script encodes the user credentials to be used in the authorisation header of the REST request, a request is made to the same URL format that was shown earlier and from the response header the SSO token is stored.

The next section replicates the Smart View request to return the SID by connecting to the planning provider and passing in the stored SSO token.

The final section reads in the security excel template file, encodes to base64. creates the XML to update the application inserting the application name and stored SID, the request is then made to update the application.

The access permissions were then verified for the product dimension member.

The security on the administration form folder was also successfully updated.

I could have taken the script a step further and started with a text file, converted it to excel and then updated the application but I think you should get the idea.

Until next time…..

No comments:

Post a Comment

Note: only a member of this blog may post a comment.