[Mondrian] Efficient JSON representation for "XMLA" responses

Andy Grohe agrohe21 at gmail.com
Wed Sep 19 22:40:30 EDT 2012

In terms of #1, I have developed clients against Saiku and Xmla servers and
there is a difference in retrieving metadata.

In Saiku, to get down to levels, which is generally where you need to be to
formulate a query, you need Catalogs, schemas and cubes which come in one
convenient HTTP request. Then for each cube you have dimensions and
measures each in their own HTTP request. So 1 + (N*2) HTTP requests.

In Xmla, it is more linear.  You need datasources, catalogs and cubes. In
this case each with its own HTTP request. 3+(N*4).  In the case of Xmla you
need to call dimensions, hierarchies, levels, measures separately.

What I would "like" to see is not in the Xmla standard but one HTTP call to
discover all the cubes with associated details for the catalog and not just
CATALOG_NAME.  And then one HTTP call to get all the details, outside of
members, for each cube. 1+(N*1). Saiku is close, but makes you get measures
and dimension data separately although measures are really dimensions.

In terms of #2, the client doing the processing... Javascript Object
Notation (JSON) will obviously be more easy in Javascript than XML.
 Everyone knows where I stand on XML.  However, Roland's library has hidden
all of the nasty bits away from me.

I don't like the fact that an Xmla Cellset can have missing cells in the
cell list. That makes processing the results a real pain.  Some may not
agree, but I would like to see this not happen.  I do realize that that
Xmla spec says it can happen.

What would be good is if we could retain the MD portion of the Cellset like
full axis positions, etc.. and have the results streamlined.  I don't think
we need a node for each cell that is returned, just have a JSON array of
the cells in Axis position order.  Since you cannot have an axis 1 without
and axis 0, etc.. this should be fairly easy.  So something in between the
tabular results and the MDDataSet results. Maybe something more like what
CDA does with a metadata object and then an array of arrays of restuls.  I
have done something like this on the client side in olap4js where the
number of cells should be the number of axis * number of hierarchies on
each axis * number of positions in each hierarchy.

For those who are unaware, the olap4js project bridges both of these 2
backends for the client javascript developer.  Each has its own
implementation/drive like olap4j but works in the host environment.  It
could be Node/Rhino on the server or xmla, saiku in the browser.  It also
includes a client side query model in javascript which neither Xmla or
Saiku have at the moment.  Suggestions welcome.

On Tue, Sep 18, 2012 at 6:29 PM, Paul Stoellberger <p.stoellberger at gmail.com
> wrote:

> One of my biggest concerns over xmla-like communication is the overall
> overhead in a) number http calls b) processing the results
> With this row (array) based results we will have to build in logic into
> the client to be able to understand the logic behind catalogs, schemas,
> cubes and how they correlate and turn those rows into objects instead of
> returning objects directly, as we do in saiku today. In order to work with
> this data I need those objects, and this just doesn't feel right to me.
> Furthermore this will have to be processed by the client. Now I know this
> is probably not something very expensive to do but I would rather keep the
> necessary logic and processing on the client side to an absolute minimum.
> Doing the same transformations on server side code will always be faster
> and therefore deliver a better user experience to the user. The less the
> browser has to work, the better.
> But my major point of concern is the number of server calls needed to
> retrieve the necessary metadata information.
> I admit I don't know the details about all xmla calls but what I can tell
> by looking at olap4j's xmla driver there are a lot of round trips needed to
> retrieve all the information necessary. I have plans to understand those
> calls better in order to improve the xmla driver. Maybe I'm wrong, so feel
> free to convince me otherwise.
> Regarding standards, are there any other OLAP servers that return metadata
> in a xmla like manner?
> What was good for xml, doesn't have to be necessarily the best for todays
> "ways of doing things".
> People were able to consume xmla (like Andy / Roland proved successfully)
> directly before, however we got great feedback from web developers who
> confirmed how easy it is to work with our (metadata) API.
> When I look at public API's like github's I see rich JSON objects and not
> row based resultsets.
> What are your arguments to keeping the structure completely the same but
> just switching from xml to json?
> Metadata calls are pretty straightforward and there is not much you can do
> differently, so I am happy to adapt to a better standard (although having 1
> server return the data this way doesn't make it a standard).
> However, the best API and standards don't make good software. My biggest
> concern is to make things work.
> If it means I can do stuff more efficient (less http calls, less
> processing needed) I prefer that over a "clean" API.
> But maybe in order to move forward:
> Maybe there is a way how we can turn those xmla like results into
> something nice thats still mappable to xmla results.
> E.g. the JSON you pasted below:
> If "row" becomes "cube" and this cube element (can) contain already
> hierarchy and/or level information in the same result, but still keep the
> xmla like result style that would make me already happy.
> Andy, Roland ... you two have probably the best insights on this topic so
> it would be great if you could share your opinion with us as well!
> -Paul
> On Sep 19, 2012, at 12:44 AM, Julian Hyde wrote:
> Paul,
> On IRC yesterday you said there was a more efficient JSON representation
> of XMLA responses than the one implemented in olap4j-xmlaserver (formerly
> Mondrian's XMLA server).
> There are some examples in XmlaBasicTest [ see
> https://raw.github.com/pentaho/mondrian/master/testsrc/main/mondrian/xmla/XmlaBasicTest.ref.xml and
> search for "Json" ]. One is
> "cxmla:DiscoverResponse": {
>   "xmlns:cxmla": "urn:schemas-microsoft-com:xml-analysis",
>   "cxmla:return": {
>     "root": {
>       "xmlns": "urn:schemas-microsoft-com:xml-analysis:rowset",
>       "xmlns:xsi": "http://www.w3.org/2001/XMLSchema-instance",
>       "xmlns:xsd": "http://www.w3.org/2001/XMLSchema",
>       "xmlns:EX": "urn:schemas-microsoft-com:xml-analysis:exception",
>       "row": [
>         {
>           "CATALOG_NAME": "FoodMart",
>           "SCHEMA_NAME": "FoodMart",
>           "CUBE_NAME": "HR",
>           "CUBE_TYPE": "CUBE",
>           "LAST_SCHEMA_UPDATE": "xxxx-xx-xxTxx:xx:xx",
>           "IS_DRILLTHROUGH_ENABLED": true,
>           "IS_WRITE_ENABLED": false,
>           "IS_LINKABLE": false,
>           "IS_SQL_ENABLED": false,
>           "CUBE_CAPTION": "HR",
>           "DESCRIPTION": "FoodMart Schema - HR Cube"
>         },
>         {
>           "CATALOG_NAME": "FoodMart",
>           "SCHEMA_NAME": "FoodMart",
>           "CUBE_NAME": "Sales",
>           "CUBE_TYPE": "CUBE",
>           "LAST_SCHEMA_UPDATE": "xxxx-xx-xxTxx:xx:xx",
>           "IS_DRILLTHROUGH_ENABLED": true,
>           "IS_WRITE_ENABLED": false,
>           "IS_LINKABLE": false,
>           "IS_SQL_ENABLED": false,
>           "CUBE_CAPTION": "Sales",
>           "DESCRIPTION": "FoodMart Schema - Sales Cube"
>         },
>                 ...
> and there's another that returns nested metadata (cubes, dimensions,
> hierarchies, etc.) and another that returns a cell set.
> I'm open to making this more concise and "json-like" as long as there is a
> clear mapping from the XMLA spec. (If we depart from the spec entirely
> we're back in the wild west, where clients can only rely on their own
> server.) If you have some ideas please propose them.
> Julian
> Julian Hyde
> jhyde at pentaho.com
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org
> http://lists.pentaho.org/mailman/listinfo/mondrian
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20120919/312dbe9b/attachment.html 

More information about the Mondrian mailing list