Querying Data Via XMLA API Calls

I use Postman to test calls

Here is example text for the body

Select Non Empty CROSSJOIN(Children(Product),CROSSJOIN({[Profit]},{[Qtr1],[Qtr2]} )) on 0 FROM [Demo].[Basic] Where ([Actual]) Provider=Essbase;Data Source=EssbaseCluster-1DemoBasicTabTupleFormatData

Here was the result looks like, not as easy to parse as normal REST request but still doable

Creating CLR Functions

Prerequisites

SQL Server Instance and SQL Server Management Studio.  Can be downloaded here for free, I use the Developer version.

Access to an Essbase Application.  I use applications included on Oracle’s OBIEE Sample VM Image

1)   Create a new Database called Freecast. 

a.    In SQL Server Management Studio Right click on Databases and select New Database

2)      Download all the files from here. 

I included the source code, so you can see what the code does. You can use the assembly in the Assemblies folder or you can compile the Freecast.cs file yourself into an Assembly so you know what is in Freecast.dll. Restsharp.dll can be downloaded here.  If you want to create your own Assembly, I suggest creating a Visual Studio C# Console App then copy my source code in and get the Restsharp NuGet package.

3)      Add the Assembly to the Freecast database

a.    Modify the ‘DLL Add and CLR Function Creation.sql’ file to point to the Freecast.dll file on your machine. The Restsharp.dll needs to be in the same folder

b.     Run the ‘DLL Add and CLR Function Creation.sql’ script to add the assembly and the base CLR Functions

4)      Create the Wrapper Functions

a.     Run the ‘Create Wrapper Functions.sql’ script to add the wrapper functions

5)     Use ‘Example Calls To Sample App.sql’ to create metadata and data calls.  More examples are in this post

Essbase SQL Integration, What Does It Look Like?

This post focuses on making calls to Essbase in SQL but for background purposes here are the steps for achieving the integration.

1) Creating .NET (I use C#) methods that make XMLA web api calls. The idea is to only create a few methods that are dynamic and can work on any cube.

2) Compiling the methods into an assembly.

3) Adding the assembly to a SQL Common Language Runtime (CLR) Environment. Both Microsoft SQL Server and Oracle DB have this feature.

4) Creating CLR Table Value Functions (CLR Functions) that call the .NET methods in the assembly.

5) Wrapping the CLR Functions into more easy-to-use normal SQL Table Value Functions (TVFs) and stored procedures.

For this demonstration there are only two C# methods.  One method is able to pass and parse any type of discover request to create metadata calls.  The other is a execute request for data calls.

These functions work on any cube because instead of returning multiple fields they return a single xml field that represent each row which can be parsed in SQL into a normal multicolumn table result.

[NOTE: The examples are querying Essbase applications hosted on an instance of the SampleApp607 VM image]

Metadata Calls

Here is a base discover call that will return all the dimensions in the ‘Basic’ cube of the ‘Demo’ application.  The basic Discover function has 6 parameters

1)    Request Type
2)      XML List of Restrictions
3)      Essbase Cluster
4)      Host APS URI and Port Number
5)      Username
6)      Password

Example base Discover call

Single row retuned

Obviously, this xml result is not readable for humans and you can't join it to other data sources, but it can easily be parsed into a normal table.

Parsed Discover request

Because you don’t want to have to write a well-formed xml strings, login credentials, and parsing cross apply's  every time you want to know what dimensions exist in a cube you can save the login info in a table (that can be encrypted) and wrap the request into another easier to use TVF.

Saved login info

Wrapped function call

A little more useful function can be created to return members in a dimension

Members function Call

I won't go into all the parameters right now, but the second one can be used to limit members returned to ones under only certain member(s)

Data Calls

Execute or data calls work in a similar way, the base Single_Access_Slicer function has nine parameters but most of those are connection parameters so I will leave those out for this example and just use an already wrapped function called Single_Access_Slicer_Connection. Here is a basic call that I will break down

Example data call

 The four parameters for Single_Access_Slicer_Connection are 

1) Dimension Parameters: A table passed in xml format that is used to create the MDX query axis.  The table has three columns 

        a) Dimension name

        b) Csv list of members or member set functions 

        c) How many in the list to execute at one time - so if you have two members in the csv list (field b) and have a value of 1 in this field it will create two requests that execute in parallel or a -1 will add the member(s) to the slicer axis of the mdx query

First parameter notes

2) Attribute List: List of attributes to be returned with level 0 members if the attribute dimensions are not part of the first argument passed.  For demonstration purposes I added an attribute dimension called 'Category' to the Product dimension.

3) Calculated Member Formulas: Pass a list of the calculated members that you can create when making the call. Here I am creating a Market member called EastVsWest which compares the two markets.

4) Cube Label -> Same as with the metadata calls this is for all the connection info.

The possibilities are endless with what you can do with these calls.  There is a little bit of a learning curve with them but that allows them to be more dynamic. Because applications and enterprise reporting applications work off normal SQL databases this makes it easy to integrate Essbase with other systems. For example, making the calls via a report in a application such as Tableau and then being able to drill through using the member set functions.  In addition, it makes overall cube management easier because it can join to data outside of Essbase and apply logic - Essbase administrators do not need to update cubes for every little reporting requirement.