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.
