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:

https://<cloudinstance>/HyperionPlanning/SmartView

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:

https://<cloudinstance>/interop/rest/

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:

https://<cloud_instance>/interop/rest/smartview/HyperionPlanning/SmartView

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…..

EPM Cloud – Managing applications with Smart View

One of the recent new features in EPM Cloud is the ability to manage applications with Smart View, this is not to be confused with managing metadata using the Planning Admin Extension in Smart View, that has been around for a while and I wrote a blog on it back in 2013.

The announcements and new features document for June 2017 has the following information on the new functionality.

“Service Administrators can now use Microsoft Excel to manage applications in Oracle Smart View for Office. Using a downloadable Excel workbook template, you can quickly create Planning applications in Smart View. By editing worksheets within the application template workbook, you define application properties, dimension and member properties, security (access permissions), and substitution variables. You can also load data from the template. Additionally, Service Administrators can edit and delete the application from Smart View.”

So basically with the use of an Excel template and Smart View you can create, update or delete an application.

The Excel template allows you to manage:

• Application properties
• Dimension and member properties
• Attribute dimensions
• Data loads
• Access permissions
• Substitution Variables

At the moment it only looks like this is available for PBCS and EPBCS, there is no mention of FCCS.

As I have already covered the metadata management in the past I thought I would look into this alternative method to manage an application, currently this functionality only exists in EPM Cloud and who knows if it will ever be pushed down to on-premise, obviously it is there hidden in the latest versions of Smart View but would require an update to Planning to bring it to life.

It is worth mentioning that there is no need to have the Planning Admin Extension installed in Smart View to use the application management functionality.

For managing an application it is all about the Excel workbook template, Smart View is only really sending the excel file to planning, to get up and running you will first need the template and this can be obtained in a couple of ways.

The first way to obtain the templates is through the downloads page which can be access by clicking your user name once logged into an instance through the web.

Under Planning Extension there is an option to select “Download Application Templates”

This will download a zip file named “PlanningApplicationTemplates” which contains three Excel workbooks, the zip file is downloaded from:

https://<cloudInstance>/epmstatic/wspace/PlanningSmartviewExtension/PlanningApplicationTemplates.zip

The zip file contains a template for PBCS, EPBCS and a version of the Vision sample application for PBCS.

The alternative method for downloading a single template excel workbook is from the Smart View panel, once logged into the cloud instance in the bottom right corner there should be an option “Download Template” or by selecting EPM Cloud and then by right clicking your instance name.

If no application exists there will also be the option for “Create New Application” which I will cover later.

Selecting “Download Template” will download and open “ApplicationTemplateFile.xlsx", the file is downloaded from

https://<cloudinstance>/epmstatic/wspace/PlanningSmartviewExtension/PlanningApplicationTemplate.xlsx

The template has the following worksheet tabs which is enough to get you going managing an application.

The template does not include worksheets for attribute dimensions or data so for this post I am going to switch to the sample template, it is based on the vision application so should be familiar to many.

The sample template includes data and attribute worksheets.

Let us run quickly through each of the worksheets in the template to get an idea of the format, to be honest there is nothing complex about them and shouldn’t need much explaining.

Starting with the definition worksheet which defines the application properties and cubes.

There are no list boxes or any type of validations in the template, there are some validations when the file is loaded to planning but not for all areas so you need to make sure you enter the property values correctly, the documentation contains the default and valid values available for each property.

In order to create an application the only properties that are actually required are the application name and description, if any of the other properties are left blank the default value will be used.

Under the cubes section if the type is left blank then BSO will be taken as the default.

The definition sheet is just a replication of the details screen when creating an application through a browser.

The advantage using the template is that you can defined all the cubes instead of the two which are available when creating through a browser.

Moving on to the dimension sheets, I will only cover one of them because they are self-explanatory, the naming convention for the sheet should be Dim.<name>, for example

The actual dimension name is picked up from the worksheet so in theory you can put whatever you like after “Dim.” but it makes sense to keep them aligned.

Not all member properties are included as column headers in the template so if you need to include additional properties then you will have to manually add them.

Also instead of “Parent” there is “Roll up Member” to confuse matters, so there are slight differences between the template, Smart View metadata management and metadata import files.

Using the Smart View metadata management, it is named “Parent Member”.

A metadata import file it is defined as Parent.

I tested updating the template and changed “Roll up Member” to “Parent” and it still worked so it looks like using either should be ok.

If you are looking to find out what a property name should be then have a look at a metadata
import/export file as they should match.

If an invalid member property is included as a header then no error will be generated and the column will be ignored.

If an invalid property value is included like:

An error will be generated when loading.

What does not exist in the excel templates or the documentation is Smart Lists so I am not yet sure if you can create Smart Lists, if you can’t this would mean you couldn’t assign them to members until they have been created.

Once Smart Lists do exist then it is possible to assign them to members using the template as I have successfully tested

Next on to attribute dimensions and the format for the worksheet tab should Attribute.<name>, for example

Once again the dimension name is picked up from the worksheet so you can put anything you like after “Attribute.”

On to the advanced settings worksheet which should be named the same:

During my initial testing I couldn’t get the advanced settings to be picked up and the default values were used, I am not sure if this is a bug.

Anyway, the worksheet allows you define the evaluation order settings for each cube in the application.

The equivalent settings in the planning applications are:

The sheet has a section to define the dense/sparse settings, for some reason the template includes populated values for ASO cubes which doesn’t make sense.

The equivalent settings in the planning application are:

The dimension settings can also be defined in the worksheet.

It is also possible to define which dimensions are valid for each dimension in the application.

Now on to substitution variables where the sheet should be named “Variables”.

I don’t think I need to explain the contents of this worksheet as hopefully it should be clear.

On to access permissions where the worksheet should be named Security.

This is quite useful as it is a simple to use format like when using the on-premise planning importsecurity utility, instead of the forced XML format in migrations (LCM).

If you are looking for the best place to understand all the possible values for the worksheet, check out the on-premise importsecurity documentation as currently the cloud documentation does not provide all the detail.

Finally on to the data worksheet which should be named “Data.”, it is up to you what you put after that, for example.

The cube name is defined in the worksheet in cell B3

The column header containing the members should be set as “Dimension”, I did try to set it to the dimension names but when I did the data was not updated in the application.

I did notice a slight issue if there are blank cells in the data, if I load the above data set then the data is fine.

If I remove the value for “BegBalance” so the cell is blank.

After loading the data you can see the periods where the blank cell exists have shifted

To get around this any blank cells should be populated with #missing.

The data will then be correct.

It is also worth pointing out that only up to 1,000 rows of data can be loaded, anything more than that will be ignored, for example I tried to load the following which had more than 1,000 rows of data.

No error was returned but a retrieve shows that the data after 1,000 was ignored.

It looks like data is loaded directly to Essbase so only numeric can be included, the default load method will be to overwrite any existing values in the database with the values from the file.

That covers the worksheets in the template so let us move on to creating a new application.

After connecting to a cloud instance through Smart View and if no application already exists there should be the option to “Create New Application” in the bottom right panel

Once selected the excel file will be uploaded and the application creation process will be initiated, be patient as creating an application can take a while and Excel will look like it is not responding until the process is complete.

If the creation was successful you should see the application, dimensions (if you have the planning admin extension enabled) and cubes in the Smart View panel

After creating the application you can update the application using the Excel templates, there should be the “Update Application” option in the bottom right panel.

Alternatively you can right click the application and select “Update Application”.

The application can be updated with any of the sheets from the template file, so for example if I want to create a new substitution variable I can create an excel file with a single worksheet named “Variables”

The only requirement is the worksheet sticks to the standard format as the template file.

The advantage of only including the sheets where you want to update the application is that the update process is much faster.

Using my example a new substation variable has been created in the application.

The functionality is similar to LCM where it will not delete and only creates/updates are carried out.

If any members are updated then a planning refresh will automatically be performed.

Not that it will be used that often there is also the option to delete an application which can be done from the bottom right panel or by right clicking the application.

You will need to confirm that the application should be deleted.

Once deleted a confirmation message will be displayed.

I am going to leave it there for this post, if I find the time I will create a follow up post to go through in more detail the process behind the application creation, update, deletion and replicate this outside of Smart View.