XML for MDX

[gbacskai]

Hi All

This is a feature request for XML reporting instead of MDX.

Currently I use MDX for OLAP query but the MDX is not usable for any purpose.
When I change the Schema I can not follow the original MDX queries, and I can not find the queries effected by the change. To manage the MDX syntax is horrible and make heavy maintain cost like SQL makes. Parse and validate the MDX also not an easy process. I can not monitor which Dimensions, Hierarchies ... etc used dynamically and which are just used rarely.

Today everybody use client tools to create mdx (in background) like jpivot, MSSQL reporting, Cognos ...etc. To customize the mdx is risky because the changes will not effects on the customized queries.

This is why I want to make a blueprint to change the mdx to xml reporting.

Some advantages of xml reporting are the next :
* To store the xml is easy and analyze all the xmls before changes as well.
* To find the Dimension/Hierarchy/Members...etc usage is easy and usefull for performance tunning.
* To create preview on query parst (like what is the subset of ... ) is easy and managable.
* I do not have a enough knowledge to design an xml DDT or template but I made a mock-up for this purpose.
* Consolidate the reports are easy
* Write client tool is easy
* xml is very familiar for xmla
* Dynamic queries can be made easy (like xsl for loops can be used)
* xml (and part of xml) can be validate fast based on current xmla schema
* no syntax error effected with a good client tool
* in a feature it can be used direct by the olap optimizer
* the beta and alpha functions can be used in the server marked by the current xml template
* several optimization can be run in the current reporting environment

I have attached a mock-up for xml definition but I do not have knowledge to design a good xml template or DTD.

It seems like a WSDL for web services.

Best Regards
Gergo
gergo@bacskai.hu

[jhyde]

You make some good points, but I am not very enthusiastic to pursue an initiative that is not backed by a standard.

The XMLA standard said that queries would be in 'MDXML', which was to be multidimensional queries expressed in XML, but never went beyond a fragment of XML enclosed in a <Statement> tag (see http://en.wikipedia.org/wiki/XML_for_Analysis ). I take that as an indication that the OLAP vendors/users don't think that an XML representation of queries is very important.

If you want to manipulate MDX, use the MDX parser that comes as part of olap4j. With it, you can convert any MDX into a parse tree, and any parse tree into MDX. It would be reasonable to write a transformer that converts an MDX parse tree into XML, so now there would be 3 equivalent forms. One could then write tools in XSL etc. to manipulate queries in their XML form. I would recommend that you keep the XML dialect exactly equivalent to the olap4j parse tree model; otherwise you will have to justify + document the differences.

Julian

[Phantal]

I agree that MDX isn't the most transparent language, but using XML as a 'query language' will only make things more obfuscated, more opaque, and less manageable.

XML is extremely verbose, not concise in the slightest, and would lead to a larger maintenance nightmare. If I want to make changes to one of my MDX queries, I might only have to add one more function call, or add another calculated measure, each of which requires 30 seconds of modifications.

Performing the same operation in XML would either require you memorize tags & their attributes, etc (think mondrian schema here -- who actually has those things memorized other than Julian & the other Mondrian devs?), then you might manage it in the same amount of time; a lot of time spent looking up tags & attributes; or a visual query editor.

Before you say "ooh, ya, a visual query editor". Look at the state of things with Mondrian's schema editors. Mondrian's been around for how many years now, and the tools available are the cube designer and schema workbench. cube designer is gimped -- it only supports some of the more rudimentary/basic features of the XML schema dialect -- and schema workbench is very dated. Half the time its schema validations are wrong, and the other half the messages you get are cryptic, or point you in the wrong direction.

This isn't to say I don't appreciate the work, this is a very massive project, and dedicating resources to these projects requires a lot of cost-benefit analysis, and I'm sure they felt what is available is 'good enough' for now, as providing improvements to Mondrian, etc, is more important.

That said, just how many resources would be devoted to managing what you describe? If I had to guess, we'd end up with a non-standard implementation (as Julian alluded to) not supported by any but Pentaho's tools, a semi-functional 'query' editor, and we'd be years out getting an API like olap4j that allows people to query a remote mondrian server without having to manage XML manually -- that is, assuming anyone bothers.

Now, all of this aside, C/C++ & other languages were implemented as a programming language as opposed to something like XML because the point is to give programmers a straightforward, logical method of getting things done. Comparing MDX to an XML query language, in my book, would be tantamount to doing Riemann sums to solve integrals when knowledge of anti-derivatives gets you there faster, and is easier to foillow.

-Brian