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.
Betcha didn’t think that was possible did you? I didn’t.
Fintan, Dimitri if you’re reading, thanks for the suggestion