Oracle BI EE 10.1.3.4 & Hyperion Essbase 126.96.36.199 – MDX & C API or MDX & XMLA – A brief look at XMLA API – Part 1
Posted by Venkatakrishnan J on January 8, 2009
One of the few things that i wanted to do after writing this series of Essbase and BI EE articles was to understand in detail about the protocol that BI EE uses to connect to Essbase. As you might probably know, MDX is the query standard (which Microsoft defined initially) that is used universally (all major OLAP vendors) for accessing Multi-Dimensional data sources. One major exception to this is Oracle OLAP. To use MDX for analysis on MOLAP data sources, one would need an API. Currently Hyperion Essbase supports the following APIs
1. C API
2. Visual Basic API
3. Java API
4. XMLA API
Out of these 4 APIs, the C API is the one that is most widely used in client applications. This API has a wide range of functions to query & modify data within Essbase. The other important fact is that this API supports MDX for querying. XMLA also supports MDX for querying. I was always under the impression that BI EE uses the XMLA API to fire the MDX queries back to Essbase. But having done some investigation over the last couple of days, this seems not to be the case anymore. Looks like BI EE uses the C-API to fire the MDX queries. Of course, the basic criteria to make the BI EE Administrator to connect to Essbase is to install the Essbase Client (which will also install the C-API). But it is strange to see that XMLA is not used for reporting. In order to use the XMLA API, one would have to install the provider services. This is not a pre-requisite for BI EE & Essbase connectivity. This to me is kind of strange that too after using the connectivity in 10.1.3.3.1 where it was a hidden feature.
XMLA or XML for Analysis, is nothing but a standard defined to enable client applications to communicate with Essbase server over the internet through the standard SOAP protocol. That is one of the reasons why one would have to install the provider services. Basically provider service would accept input requests through the standard SOAP protocol. Then the MDX query embedded within the SOAP request would be fired on to the Essbase server. The Essbase Server would return the data back to the provider service. The provider service would inturn embed this into a SOAP response and then send it back to the client. This is represented by means of the diagram shown below
I studied the XMLA specification in detail for a couple of days. Though i still have some open ended questions which i will be blogging about in the coming days, i believe this API has excellent features and has tremendous potential for further uptake. Having said that, it would be interesting to see how this standard would be further developed(or endorsed by Oracle) as currently BI EE does not use this API. Though BI EE uses MDX which is the heart of XMLA, the standard in itself is not used. Though XMLA does not support quite a few reporting formats like C-API does, I am not sure whether this was the reason to go with this API rather than XMLA in BI EE. The main reason why i believe this was chosen was to facilitate writebacks to Essbase(which is available currently for relational sources). MDX over XMLA cannot write data back to Essbase as it is not supported as of now. But C-API has extensive range of data manipulation functions. Something has surely changed between what was available in 10.1.3.3.1 as a hidden feature and 10.1.3.3.2 (the actual release). I will go into detail of how XMLA works and how that can be used by generic relational reporting tools other than BI EE next week. The above is just my observation. I have not validated this with Product Management. Feel free to leave your comments/observations. I would be covering all the 4 APIs above (its been some time since i have used C. Time for me to refurbish my C skills) in the coming months.