Business Intelligence Developer (MCSE)

Interrogating and automation of Essbase cubes with Essbase Web Services and C#

It’s funny how Essbase was once considered one of the premier BI tool on the market and nowadays don’t even get a mention in the Gartner Magic Quadrant. But given the unnecessary complexity of the Oracle EPM, is it surprising? Oracle is after all, the most hated database by most developers (Larry, listen to us) – if you’ve ever had the pleasure of developing something on it, you’ll understand why (though things are improving). Essbase cubes and the EPM stack? Several times worst.

Nevertheless, it still remains a very popular system (from those days) and people in finance still love it, not to mention it is our bank’s core reporting system, so its worth revisiting an approach to automation Hyperion developed a while back but to this day is not widely used as most people are not aware it exists much less how it works-automation through Essbase Web Services (EWS), in particular with Multi Dimension eXpressions (MDX) and C#.

Running MDX queries wrapped in C# against SSAS cubes is not difficult as this library is well documented and popular enough there’s blogs/articles on it all over the web. Doing the same thing with Essbase is another challenge, my own attempt/research/observation into this shows those who have tried mostly failed or gave up as I did before simply because there is no documentation on it as all the leading authority on this topic can tell you-I don’t claim to be one, just someone persistent 🙂

As a start and contrary to what you may think, the namespace/DLL needed are not even the same for the two OLAP servers/services, despite the fact you’re working with .Net. With SSAS, the namespace is Microsoft.AnalysisServices.AdomdClient.

For Essbase, you actually need the DLLs from SSRS of all places, but given SSRS (and Tableau) is known to be the only systems outside Oracle that is able to connect to EWS, perhaps not that surprising. The DLLs you need are shown here (just first two). The interesting bit is it works from SSRS 2008 right up to SSRS 2016.

So with your namespace imported, you might be attempted to go the way of SSAS with something like this as per MSDN documentation.

The only problem with the approach above is a number of the properties and methods do not work as the code was intended for the SSAS DLL and we’re using the SSRS DLL, despite the similarity.

What you need to do is something like this below. Notice the similarity but also the important differences (in connection string for a start).

I also found an approach that actually works better than the official one in MSDN by using a XML reader in conjunction with LINQ (Language Integrated Query) and de-serializing it that way as opposed to trying to reverse engineer to understand the SSRS classes (what is a TupleCollection for SSRS for start? Do you have the time or is there a need to understand this library to that depth? There’s no doco for SSRS, remember?).

Also with LINQ, it’s generic and everyone understands, including those that don’t know the internal structures of a cube-you only need to read the XML (SOAP) response, like any XML file. It is also reusuable for other APIs like Tableau and perhaps most important of all, far fewer lines of code.

So it goes like this, open connection, grab XML from server, read or persist XML, close connection. We’re only using the DLL for the connections, everything else is generic C#/LINQ.

But the most interesting thing? When you run the queries, the entire cube, data and meta data all comes back. This is what the XML looks likes

Or in my favourite tool

Once you have the data, then work with it within your application or persist back to SQL

There’s actually a lot more properties to see but I’ll leave for you to explore. There is another useful trick I can share. Don’t like writing MDX? Well, you don’t have to. For SSAS, there’s a very handy little tool, the OLAP Pivotable Extensions from CodePlex (https://olappivottableextend.codeplex.com/). Just open Excel, point and shoot. Works even with calculated members.

Once you have the MDX, just wrap it within your C# (or embed it in SSIS data source like a custom SQL query).

What about Essbase? Similar thing but you need Tableau Desktop. It just records the MDX for you on the view you have.

Anyone get lost, feel free to reach out as usual, I can send you the codes with detailed instructions. This is a good piece by leading Essbase expert John Goodwin for those feeling adventurous.

Another cool and little known feature that is not widely use is web services for SSAS-will definitely give that a try one day. Love to hear from anyone that’s tried it.

2 thoughts on “Interrogating and automation of Essbase cubes with Essbase Web Services and C#”

Hey Sam, this was really insightful. I wasn’t able to get the AdomdConnection to work, I keep getting a timeout error which is the same thing that happens in SRSS. However, I put it into a POST request for the XMLA service and it returned the xml data which can be parsed the same way. We have been using the Hyperion Planning REST APIs but these calls are a lot faster (because it is hitting Essbase directly), return better metadata, and can run different type of queries (properties, calculated members, etc..). Also the Planning REST APIs (especially the dataexport) is buggy. Thanks for posting

Hi Jeremy, thanks for the feedback. Great to hear you got something out of it, all the best. I’ve been wanting to play with the other APIs but have changed employers and they don’t any of the EPM stack. Cheers, Sam

Like most modern servers nowadays, Power BI has a fairly good set of web APIs that developers can leverage to extend functionalities of the product and further automate many processes. The developer’s section of the documentation provides an easy to follow set of instructions on how to get started and showcases some very useful feature … More Power BI REST API

Came across a few little extensions in Visual Studio Code marketplace recently that has and will further save me considerable time when I need to work with JSON. … More Serializing and Deserializing JSON

Whether you're a manager, developer or DBA, SQL Auditing is a powerful tool that can help you understand what is happening within your platform and its underlying data. Auditing on SQL Server allows you to monitor your server from two different aspects: at the server level and the database level. … More Auditing Database Servers with SQL Server-A Primer

In my last blog on partition tables, I talked about the benefits from an archiving and management perspective. In this one, I'll go over the query performance that can be gained. If partitioning is something new to you, it's worth thinking it as dividing large tables into smaller ones … More Partitioned for Performance

If you do any development beyond the most basic on the .Net Framework, understanding and resolving DLL dependencies is a necessity and a fact of life and without the right tools to guide you, this can be a very time consuming and frustrating exercise. … More Untangle and Understand DLL Dependencies with NDepend

It’s fairly common knowledge amongst database professionals partitioned tables can help considerably with performance in various situations but my guess is not many would have considered the enormous benefits when it comes to archiving, a necessity when it comes to massive tables. … More Horizontal Partitioning for Big Tables

Despite its reputation, I'm still a massive fan of linked servers. Microsoft developed it for a reason and it's ongoing presence on the platform since day one says it all. No one told you to pull back a gazillion rows on an inner join with a local table. So, when I saw it's now possible ... … More Linked Servers for Azure’s NoSQL Cosmos DB