Tuesday, July 13, 2010

Create Your Own Maxl Operations in OAS

Olap Automation Studio (OAS) allows for configuring your own Essbase MAXL operations for use in tasks. Configuring operations is a technical function usually done by developers who then distribute the operations as XML snippets for end users to use in their automation tasks. Operations cannot be seen in the OAS GUI but are embedded in the XML file that drives all OAS automations. Operations are used in task objects and define the GUI that the user interacts with when they configure a task.


One powerful feature of OAS is that it embeds the MAXL interpretor inside one of it's operation java classes and allows user to configure it to execute any MAXL command. This tutorial will provide a step by step guide to creating your own MAXL operation.

Below is an example of a MAXL operation that comes installed with OAS, the RunEssbaseAdHocCalc operation. You can see this if you open any OAS xml file that you save.



An operation has 3 main components, the user defined name, the classname of the java class that contains the logic that is executed for this operation and the parameters. The parameters tell the OAS GUI what to show the users of this operation when they use it in a task. Notice in the screenshot below the list of properties in the task wizard are exactly those in the required parameter defined in the operation above.




MAXL operations have a maxl parameter that represents the maxl statement template that OAS uses to replace with runtime values such as the essbase app and db names. Values in the maxl parameter that will be replaced are surrounded by ##'s .

In the example below the values inputted by the user in the task for calctext, app and db will be replace in the maxl statement.

execute calculation '##calctext##' on ##app##.##db##


In the image above the xpath attribute of the parameter tells the OAS GUI to query and fill in the values for the parameter so that the user does not have to type in these values directly. Xpath is a standard xml query language. Xpath's can either return a single value or a list. When they return a list the user sees a combo box.

In this example the essbaseconnection parameter will appear in the task as combo box since the xpath query returns a list of essbase connections.



Step by Step Guide

1) Create the operations element



2) Add the a) errors and b) required parameters . The errors parameter allows for controlling error handling in the maxl output when a command is run. For now we will leave it to detect the words error and invalid.




In this example we are creating an operation for creating a location alias. The maxl parameter is the maxl syntax for creating a location alias that comes from the Essbase Technical Reference guide. Notice the ##'s surrounding the required parameter values aliasname, app, db, toapp, todb, server, user, and password. These are the properties that the user will input using the OAS task wizard and get replaced in the maxl statement when the automation is executed to generate a valid maxl statement that Essbase understands.


If we save the xml file right now and pull it up in OAS, when we create a task using the CreateLocationAlias operation we should see a task wizard screen that looks like this one below. Notice that all the required properties are list as text boxes since we did not define any xpath attributes. We will go back and add this later for the essbaseconnection, app and db parameters.
But as it is currently defined the CreateLocationAlias operation is now useable in a task for creating automations.



3) Add xpath attributes for user friendly combo boxes and automatic fill-ins for the essbaseconnection, app and db required properties.

Rather than retype the lines I just copied the xpaths from the RunEssbaseAdHocCalc operation. Notice the parameter names essbaseconnection,app, and db are the exact ones listed in the required parameter value list.


Saving the xml file and then loading it into OAS again shows this screen below when creating a task using the CreateLocationAlias operation. Notice that the essbaseconnection property is now a combo box that shows all Essbase connections previous defined. Choosing an essbaseconnection fills in the app and db automatically for the user.



The CreateLocationAlias operation we just created is now ready to give to an end user to use for creating automations. The user does not need to know maxl. They simply need to fill in the text boxes.

Taking It A Step Further

If wanted to take this a step further we can use an OAS RunSQLLoop operation to build the maxl statement at runtime. This means we can dynamic create maxl statements using data from a database table to setup location alias, create filters for users, define partitions and so on.

A final word on XPath

The XPaths xml queries used in this example look complicated but are completely optional. If you don't specify anything the OAS task wizard defaults to a plain old text box. Not very fancy but they are still better than writing maxl. A full explain of Xpath is beyond the scope of this tutorial but many good sources of Xpath information exist on the net.