Smart View and Essbase Runtime Substitution Variables

Back in August 2015 when Smart View 11.1.2.5.500 was released the readme had reference to new functionality for runtime substitution variables, the readme contained the following information:

"In Smart View, you can now execute Oracle Essbase calculations that, when launched, prompt you to enter variable information, called runtime prompts. Calculation scripts and runtime prompts are created by your Essbase administrator for your specific system.

Essbase administrators define calculation scripts so that Smart View users can work with runtime prompts. For information on setting up your calculation scripts with runtime prompts, see the Oracle Essbase Database Administrator's Guide, “Using Calculation Scripts with Runtime Substitution Variables in Smart View.”

After a while it was confirmed this functionality was not yet available and the information was subsequently removed from the readme.

The Essbase 11.1.2.4.010 patch readme contained reference to runtime substitution variables in the documentation updates section:

 “Correction: Using Runtime Substitution Variables in Calculation Scripts Run in Smart View
The following information updates the “Using Runtime Substitution Variables in Calculation Scripts Run in Smart View” section of the Oracle Essbase Database Administrator's Guide for 11.1.2.4”

I have searched for the section in the 11.1.2.4 Essbase documentation and could not see it but it is a different story if you look at the Essbase 12c documentation.

At the time of writing this the latest patch release of Essbase 11.1.2.4.011 and the functionality still doesn’t look available which I am sure will change in a future patch.

Currently Essbase 12c is only available in OBIEE 12c and I wrote a detailed post about it last year looking at the changes.

So are runtime substitution variables available in Smart View with Essbase 12c, well this would be a short post if they were not :)

It is worth pointing out that it doesn’t look like it is the version of Smart View or Essbase that is holding back the functionality in 11.1.2.4, it is code differences in Provider Services that are making the difference.

In this post I thought I would look at a few extremely simple examples of using the runtime substitution variable functionality in Smart View.

The examples are using Smart View 11.1.2.5.600 and Essbase/Provider Services 12.2.2.1, there is no EAS in 12c but the good news that EAS 11.1.2.4.008+ is now supported with Essbase 12.2.2.1 which makes life much easier, you are still under the constraints of OBIEE security but at least you can use the majority of functionality once the Essbase 12c server has been added in the EAS console.

In OBIEE 12c you can still use a Smart View connection URL and the only difference is the default port will be 9502.

The same goes for private connections:

The default Essbase cluster name is “bi_cluster”

Before going any further with Smart View we need a calculation script to run that includes runtime substitution variables.

The syntax for using Smart View runtime substitution variables in a calc script is:

SET RUNTIMESUBVARS
{
   rtsv = POV
   <RTSV_HINT>
      <svLaunch>
         <description>rtsv_description</description>
         <type>member | string | number</type>
         <dimension>dimName</dimension>
         <choice>single | multiple</choice>
         <allowMissing>true | false</allowMissing>
      </svLaunch>
   </RTSV_HINT>;
};

The different options within the tags will become clearer as I go through some examples.

Let us start with one runtime sub var.

I will breakdown the above definition of the sub var
.
“Market = POV” - A variable called “Market” has been defined which is set to POV, when set to point of view (POV) it basically means it will pick up information from the current member(s) within the grid in Excel.

<RTSV_HINT> and <svLaunch> are a requirement of using runtime sub vars in Smart View.

<description> just describes the runtime sub var and is shown in the runtime prompts section in Smart View.

<type> can be set as member, string or number, in this example I want the sub var to be a member.

<dimension> this is the dimension that the sub var is defined against which in my example will be the “Market” dimension

<choice> has been set to single to assign one member to the sub var, the other option is multiple.

<alllowMising> the definition of this is

“Specifies whether to allow or suppress data cells for which no data exists in the database”

To be honest I have not seen this actually make any difference whatever it is set to, I am probably missing something obvious.

So in summary all that definition means is that the current Market member in the Excel grid will be picked up and assigned to the “Market” sub var, the sub var is then used in the FIX.

It makes more sense with a demo of it in action, I have created a simple retrieve in Excel.

The market dimension member in the retrieve is “Florida” which should be picked up and assigned to the sub var.

Select “Calculate” to bring up the calculation scripts window.

Once the calc script I have created has been selected a “Runtime Prompts” section is displayed.

The description of the sub var defined in the calc script is shown and the member “Florida” has been correctly picked up from the grid.

The calc script was successfully run and a quick check of the Essbase application log confirms the member “Florida” was assigned to the sub var and used in the fix.

Aggregating [Product(All members)] with fixed members [Market(Florida); Scenario(Actual)]

A refresh of the sheet shows the product dimension has been aggregated.

The runtime sub var definition can be easily expanded and this time I have added a Scenario variable into the script.

It is probably worth pointing out that if you do validate the script in the console you will be hit with an error.

The log will contain:

Error: 1200315 Error parsing formula for [POV] (line 5): invalid object type

This error can be ignored but one to watch out for if you need to validate a complex script.

Anyway, back to the example, if I run the calc on the same grid in Smart View this time the current Scenario is picked up.

After running the calc the log verifies the sub var was correctly assigned.

Aggregating [ Product(All members)] with fixed members [Market(Florida); Scenario(Actual)]

It is working fine for single members but how about multiple members.

This time I updated the definition to set choice to multiple for the Market sub var.

The Excel grid now includes two Market members.

Selecting the calc script now displays a different message in the runtime prompt box.

The message does not quite it on and expanding the window makes no difference but the full message is

“Market Dimension Members on Row/Col/POV will be used.”

After launching the calc the log confirms that both Market members were successfully assigned to the sub var.

Aggregating [ Product(All members)] with fixed members [Market(New York, Florida); Scenario(Actual)]

So what happens if choice is set as “single” but there are multiple members in the grid.

I set “choice” back to “single”

In Excel I have purposely not selected any Market member cell.

Select the calc script and the following warning is displayed.

The calculation script window is still displayed with the Market runtime prompt empty which cannot be changed so the only option is to close the window.

This time I have selected a Market member cell.

No warning this time and the selected member is assigned.

Ok, moving on to some of the other options available in the runtime sub var definition.

If you don’t want to use the POV method and manually enter the value for a sub var then the “string” type can be used.

In the above example “type” has been set to string and POV updated to a Scenario member.

Interesting that in the patch readme the documentation update states:

“The value of the runtime substitution variable must be set to POV; it cannot be set to a member name.”

The way I read that is what I am doing now cannot be done or maybe as usual it is some cryptic message that can only be understood when all the planets align.

The “choice” value will be ignored and can be removed as it is only valid when “type” is set as “member”

The calc script was run again and now the runtime prompt is populated with member set in the sub var definition.

The runtime prompt can be updated or left as the default.

A comma separated list of members is also a valid input.

A quick check of the log verifies the string type is working.

Aggregating [ Product(All members)] with fixed members [Market(Florida); Scenario(Actual, Budget)

If member selection is required, then the “type” can be set back to “member”

The runtime prompt will now be enabled for member selection.

As “choice” has been set to single only one member can be selected.

As expected the member is correctly assigned to the sub var in the calc script.

Aggregating [Product(All members)] with fixed members [Market(Florida); Scenario(Actual)]

To select multiple members then “choice” can be updated to “multiple”

The member selector will now accept multiple members.

Aggregating [ Product(All members)] with fixed members [Market(Florida); Scenario(Actual, Budget)]

On to the final “type” which is number

In the above example the sub var was defined as “SalesRate” and given a default value of 1.2, “type” was set as “number” and the “SalesRate” sub var is then used in a calculation.

The following Smart View grid was set up in Excel.

This time when selecting the calc script a new runtime prompt is displayed with the “SalesRate” sub var.

The default value can be overridden by entering a new value.

The calc ran successfully.

Calculating [ Measures(Sales)] with fixed members [Product(100-10); Market(New York, Florida); Scenario(Budget)]

A refresh of the data and confirmation that Sales data has been multiplied by 1.2.

If you are not aware it is possible to include logging of the runtime variable values in the application log by using the ENABLERTSVLOGGING configuration setting.

I added the following to the Essbase configuration file so runtime sub vars would be logged for the Sample application.

ENABLERTSVLOGGING Sample TRUE

After running the calc script again the log contained the runtime sub var details.

Executing calc script [MTRTP1.csc] with runtime substitution variable { Market = "Florida","New York" }

Executing calc script [MTRTP1.csc] with runtime substitution variable { Scenario = Budget }

Executing calc script [MTRTP1.csc] with runtime substitution variable { SalesRate = 1.2 }

I did notice a bug in Smart View when using multiple members with the sub var set as POV and also having a sub var defined as a member.

There is no problem when the member selection is not selected so the defaults are kept.

If you do open the member selection and then run the calc the following error will be generated.

The Essbase application log highlights the issue as instead of passing the members from the grid through it has sent the default message “Market Dimension Members on Row/Col/POV will be used”

Invalid Calc Script syntax [
FIX(Market Dimension Members on Row/Col/POV will be used, "Budget","Actual")
AGG(Product);
ENDFIX;]

Smart View basically sends XML to provider services and by capturing what is being sent from Smart View you can see it is incorrect

It is an issue with Smart View and not provider services though I am sure it will be fixed in a future Smart View release.

Well I think I am going to leave it there for today, once the functionality is available in 11.1.2.4 I will update this post to reflect that.

Load testing with the EPM Automate replay command – Part 2

In the last part I went through setting up Fiddler and the process to create a HTTP archive (har) file after recording Smart View actions.

Once the “har” file(s) have been created the next part of the process is to create a replay file.
The documentation provides the perfect insight to what a replay file is.

“A replay file is a CSV file that lists the credentials (user name and password) and the name of the HAR files that are to be run to load the system using the replay EPM Automate Utility command. Ensure that the user name and password that you specify has the rights to run the activities included in the HAR file.

On executing the replay command, the EPM Automate Utility runs each row in the replay file in parallel to exert load on the service. For example, if your replay file contains 10 rows, the utility replays 10 sessions so that you can perform tests to verify that user experience is acceptable when the service is under specified load. Each activity included in the HAR file is run serially.”

Basically every row in the replay file will be executed in parallel so the more rows the more load that will be generated.

The format for the replay file is:

username,password,harfile

So in the above example I am going to run one session and the “har” file contains a recording of opening a form, data was entered, the form was saved which also fires of a business rule and then the session was logged out.

The next step is run the replay command using the EPM Automate utility.

The format for the command is:

epmautomate replay replay_file_name.csv duration=N [trace=true] 

replay_file_name.csv contains the “har” files and user credentials

duration is the number of minutes to run the load test for, if all the actions in the har file complete before the end of the duration then it is run again until the duration is up.

trace is optional and creates XML files with each post and response carried out as part of the replay, you would only really use this to diagnose issues.

In my example the replay is going to run for a duration of 3 minutes and the output written to a csv file.

I am writing the output to a csv file as it opens up well formatted in excel for further analysis.

The outputted information is pretty well detailed and breaks down each Smart View action with a description and duration.

The replay command does not take account of time for the user to carry out an action so as soon as one action has completed it moves on to the next, there are no options to add delays or think time but I suppose this is not a problem if you are just trying to produce load on the system.

You can see at the bottom of output log that once the actions are completed then they are repeated as the duration time had not been reached.

The longest duration of this test is the saving the form as this also executes a business rule.

The total sequence of actions was run six times over the three minutes, I averaged out the total time and the time to save the form and run the rule.

Next I recorded a similar set of actions against a different form, produced the “har” file and added it to the replay file.

The replay command was run for the same duration and results collated.

I now have the average times for two sets of actions that have been run independently so how about running them in parallel.

All that is required is to include both “har” files in the replay file.

The replay command was run again for three minutes and the average times calculated.

The average times are higher with just two sets of actions being run.

I decided to record running a resource heavy rule from Smart View and add that to the replay file.

The replay command was run using the same parameters.

Now you can see that the average times are starting to take a bigger hit as more load is being put on the system.

I could go adding more entries to the replay file to create more load but hopefully you get the picture of how the replay command can be used.

Anything that can be done in Smart View can be recorded and added to the testing, as I said earlier you could have users actively using the application while the replay is being run to get a feel of how the system performs under load and at the same time you will be getting stats back from the replay.

Once the replay has been set up it can easily be run from different client locations to validate whether the timings are similar or not.

I must stress this has just an example of using the replay and is in no way a statement on how to go about load testing your application.

I was going to end the post here but I thought I might as well include a bit of technical detail on how the replay command works, this might not be for everybody so If you are not interested, until next time… :)

I mentioned earlier that the “har” file contains all the actions carried out in Smart View in JSON format, and in simplistic terms the replay command runs through file and extracts the relevant information then posts this to the Smart View URL.

The main obstacle that the command has to deal with is that in Smart View posts there are two unique identifiers that are required for authentication purposes, these being the single sign on (SSO) token which is generated first and then a security identifier (SID) which is used in further communication, both are generated at session time so this means that the recorded set of Smart View actions cannot just be played back from the “har” file as authentication would fail.

First of a valid SSO token has to be generated and this is done by a REST resource.

The username and password are read from the replay file.

The username and password are base64 encoded and added as a Basic authorisation header and the following REST resource is requested.

If authentication was successfully the response header will contain the SSO token.

The SSO token is then stored and is then used in the XML body of a POST request to the Smart View URL.

What is interesting is that the replay command always uses the URL /interop/rest/smartview instead of the one used with Planning and Smart View which is /HyperionPlanning/SmartView

So a REST resource is being used to accept Smart View XML and then I presume is being posted on to the Planning Smart View servlet. I am guessing this is get around having to mess around with Oracle Access manager tokens in a cookie which is the way it is done in Smart View.

The response from the above request contains the SID

With the SSO and SID stored the replay command can now run through “har” and process the JSON.

Let us take an example of one of the Smart View actions which opens an application, the output from the replay command has the following entry.

In the JSON there is a matching entry which I have converted to object form so it is easier on the eye .

The replay command cycles through the JSON entries and first checks whether “url” contains "*/HyperionPlanning/SmartView"

If this is true, it then checks that “method” equals “POST”

The “postData” “text” string is stored and the text is replaced with anything between with the stored SID and anything between with the SSO token.

Now the text string is posted as XML to the REST resource https://<epm_cloud_instance>/interop/rest/smartview to replicate the Smart View action.

The headers in the response contain the information that is written to the output log.

X_EPM_FUNCTION = “screen” column in the output log.
X_EPM_ACTION = “action” column
X_EPM_OBJECT = “object” column

The next entry is read in and processed and so on until the end of the “har” file.

If the duration has not been exceeded the process is repeated until it has.

So that is pretty much covers how the replay command operates.

If you are interested in load testing your EPM cloud environment, feel free to get in touch.