Loading Hyperion Planning Metadata In 5 Easy Steps using OAS

I have resisted adding a Hyperion Planning metadata loader to Olap Automation Studio (OAS) for a long time due to the fact that tools like ODI, Informatica and the out of the box Outlineloader.cmd utility all do this function. But on the request of a few users who felt that the current tools are too difficult to use and at times a bit overkill for what they want to do. I decide to implement this in OAS with the goal of improving easy of use and the time it takes to create such an automation. Below is a step by step guide to using OAS with the new Hyperion Planning metadata loader operation.

Step By Step Guide to Loading Hyperion Planning Metadadata

Step 1) Create a Planning connection in OAS. Right mouse click connections from the OAS tree and create a new connection. Choose "PLANNING" as the connection type (see figure below).

Step 2) Create a planning dimension table. A table is a logical view of the properties for a dimension in a Planing application. Find the new connection you just created above, then right mouse click the connection name from the OAS tree then click "create table" from the popup menu. Choose "planning dimension" as the table type (see figure below).

Clicking next will take you to this screen below. Click the "Retrieve Hyperion Planning Dimension Info" button to retrieve the list dimensions. Choose a dimension from the Dimensions drop down box then click "Load sample data" to view a list of properties for that dimension.

Clicking next will take you to this screen below. Here you can choose which properties you want to load into the dimension. Most properties are optional but the first two are required for loading a dimension table. In this case the the Account dimension load would require at least the Account, and Parent properties. Check "Alias:Default" to load a default alias. Click "Finish" to save the table.

Step 3) Next we need to create a mapping object to tell OAS where to get the source data to load the dimension table we just created. Right mouse click "mappings" uder the OAS library tree then click "Create Mapping" from the popup menu. The Create mapping wizard allows for mapping columns from two tables. In this example screen below the source is a sql server table and the target is a Hyperion Planning dimension table.

Click next to map the columns then click "Finish" to save the mapping.

Step 4) The final step is to create a task using the CopyData operation. This is a generic operation that will copy data from many different sources including text files and sql database tables to Hyperion Planning.

The CopyData operation requires one "mapping" property. Choose the mapping we created earlier from the dropdown box in the Value column then click "Finish" to create the task.

Step 5) Before we can execute the task we need to create an automation. You can do this quickly by right mouse clicking the task name we just created above and clicking "Automate Task" from the popup menu. This will create an automaion with one task with the name defaulting to the name of the task and the word "Automation"

Execute the planning metadata load by right mouse clicking the automation we created above and clicking "Run Automation."

Log into Hyperion Planning to see that the data is loaded.

Assumptions

In this post I am assuming the reader has some familiarity with OAS but even if you have never used OAS before you should be able to download and install OAS in 10 minutes and create this load just as quickly and easily as you see here.

We have tested this loader operation on Hyperion Planning from verson 9.3 to the freshly release 11.1.2.

To get a tutorial and introduction to OAS click here.

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.

Use MAXL Essbase System, User, Session, and Application info to drive automations

With Olap Automation Studio OAS it's easy to capture, monitor, and save session data from an maxl display statement such as this one below. This example walks you through using OAS to take Essbase session data and save it to an Excel spreadsheet.

First create your spreadsheet that will hold the output of the session data. Make sure you put in all the columns you want to capture from the session output. Noticed I used the column name DB in this example instead of database. This was needed to avoid an ODBC error. My guess is database is a reserve word for ODBC SQL's syntax which OAS uses.

Next launch OAS and create a file descriptor for the Excel file you just created by right mouse clicking the files icon in the OAS library tree view (see below).

Browse to the file you just created in the previous step and click finish to create file the descriptor in OAS.

Once you create a file descriptor you will need to create a connection to access the file. Do this by right mouse clicking the connections icon in the OAS library view. This should take you to the screen below. You should see the file descriptor you just created from the previous step in the dropdown box.

Next you will need to create a connection to Essbase Administration Services(EAS) and Essbase. Do this now by right mouse clicking the connections icon from the OAS library tree view. Give the connection a name and choose EAS for the connection type. Input the required info and click the test button to test the input.

Next you will need to create a connection to Essbase by right mouse clicking the connections icon again. The essbase connection wizard will ask you to pick the EAS connection you created in the previous step. You will also need to pick an application and database.

Next we will create a task to insert the session data into the Excel file. We will do this using a simple SQL insert statement. We are using SQL because OAS accesses the Excel file like a database. To create a task right mouse click the Tasks icon in the OAS library tree and choose Create Task from the pop up menu. At the create task wizard input a name and choose ExecuteSQLUpdate for the operation then click next.

This is the required properties page for the ExecuteSQLUpdate operation. It takes a "dataconnection" which is a drop down box. Choose the excel connection we created earlier. The second property is the sqlstatement.

Type this text below into the sqlstatement property:

insert into [sheet1$] (user,session,application,db,request,request_time) values ('##user##','##session##','##application##','##database##','##request##','##request time##')

The [sheet1$] refers to the Excel sheet name in the workbook that I created earlier. The ## surrounding the column name is used by OAS to dynamically insert the value from the maxl session output.

Now click finish to create the the task.

Next create another task to run the maxl display command. Right mouse click the Tasks icon in the OAS tree and choose create task. Give this task a name like GetSessionInfo and choose the RunMaxlDisplayLoop operation. The operation gets the maxl data from a display statement and executes another task. The task it will execute is the insert task we just created. Since the task it executes can do anything we can use this task to do other things like kill sessions or grant security on certain users. It's very powerful.

The RunMaxlDisplayLoop takes five properties: Essbaseconnection, info,displaymaxl,type, and task. When ever possible OAS will provide dropdowns to pick information it knows exists like the Essbase connection you just created. The info property is also a dropdown. Choose session in the info property drowpdown. Leave the displaymaxl and type properties to the default values. For task choose the task you created in the previous step in the dropdown box. Click finish to create complete the task creation.

Next we want to create an automation. Automations are groups of tasks that we want to execute or run. Right mouse click the getsessioninfo task we just created and choose automate task from the popup menu.

This will create a default automation under the automations icon in the OAS tree with the name of the task and the world automation appeneded to the name by default.

At this point you should do a file save from the File menu to save your work.

After saving you can right mouse click the getsessioninfoautomation under the automations icon and choose Run Automation. OAS will execute the tasks you created and insert the data into the Excel file. You will see information on the task execution in the Console window to the right.

Here is my final output in the excel file.

Sending this Session data to a database table could be done just as easily. In fact the steps are almost the same except for the creation of the excel file. You would instead create a database connection.

New OAS operations widget allows users to use MAXL Essbase System, User, Session, Application to drive automations

The latest build of Olap Automation Studio (OAS) includes a new operation call RunMaxlDisplayLoop.

This operation takes as parameters an essbaseconnection, info (application, database,disk volume, filter, session, system, user, variable...). This is the OAS version of the MAXL display command. Using this operation you can drive your automations based on any data returned from the MAXL display command. For example you can log users off. Grant security. Set values for substitution variables or even kill sessions. Later I will show how to save user information to a database table or excel file.

Release 1.0 Beta Of OAS

The 1.0 Beta of OAS is now avaliable at www.olapautomation.com . It currently includes these modules:

1) Data Valdation for checking if your Essbase data ties to your sql source or another cube.

2) Executing automations based on data from the MAXL display command such as display users for doing mass user security automations in MAXL

3) Email alerts

4) Database loggging

5) MAXL automations

6) Embedded database for staging data

7) SQL data driven automation

8) Sub substitution variable manager

Olap Automation Studio

This blog provides information on a software product call Olap Automation Studio. I will be posting a beta release in the May 2008 time frame. Here is a short list of features:


1) An extendable automation platform for building any type of automation
2) The initial release will have automation widgets focused on Essbase
3) After the initial release widgets will be released for automating mdx based data/outline extracts and shared services imports/exports
4) The GUI is built on the java Eclipse platform
5) The automation engine and GUI makes heavy use of Hyperion's documented and undocumented java API's