MDX from Hell no longer scares me
In the old days, the MDX capabilities of the reporting-engine were somewhat limited. If you were nice, the Report-Designer would allow you to get a tabular view out of your OLAP cube. But the Report-Designer would become very nasty if your query left the happy path of the regular 2-dimensional table results.
To really handle MDX, we also have to deal with all the advanced cases too:
- MDX results can have up to 255 axes. Thats more than the two we supported. Although not many users will utilize more than 2 axes, there are some good cases for them as well. All of these extra axes can be use to do classical grouping, to insert pagebreaks and so on.
-
MDX results can have zero axes. In that case, the result will be a single cell. Again, not likely, not common, but valid. If we want to claim that we eat MDX, we must not choke on that stuff.
Example:"select from [SteelWheelsSales]"
-
MDX results can be one dimensional. This results in a single row of data, with all measures aggregated for the single axis.
Example:"select [Product].Children on 0 from [SteelWheelsSales]"
-
And then, axes can be empty. Once a axis is empty, measures are no longer printable. But we still get the dimension data for all other axes.
Example:"select [Product].parent on 0, [Time].Children on 1 from [SteelWheelsSales]"
(this ends up empty because the parent of 'Product.all' is null, and null members are implicitly filtered)
-
And of course, we can have ragged hierarchies. In ragged hierarchies, the depth of the shown dimensions is not uniform accross all members of an axis.
Product Time/Year Time/Quarter All 2004 All 2005 All 2005 Q1
OLAP datasources can now be accessed either via a native Mondrian datasource (which exists purely for legacy reasons, so that we can preserve existing datasource definitions) and the new OLAP4J-data-factory which uses OLAP4J to speak with the OLAP-backend. This way, we get XML/A and access to all other MDX-servers that support OLAP4J (like Palo) for free.
For each of the access methods, we support three result-types:
-
The legacy-banded mode. It comes with all the limitations and strange quirks of the old version. It may not be pretty, but gives 100% backward compatibility. For that reason, it still dies on most of the special cases and behaves funny on ragged hierarchies. But that's all just for self-defence: I dont want to be the one who has to explain to all our users that they have to re-edit all their MDX reports with the upgrade of the reporting-engine. The saying "they would kill me for that" might become unpleasently true in that case.
-
The banded mode is a cleaned up and extended version of the old banded-mode. As we dont have to care about backward compatibility here, we were free to fix the strange column naming ("Time", "Time{2}", "EMEA/Sales/Measures" and so on) into a more normalized column naming schema ("[Time].[(All)]", "[Time].[Years]", "[Markets].[All Markets].[#null]/[Measures].[Quantity]" and so on). The cleaned up mode now also fully supports ragged hierarchies and all the special cases and is a good choice for creating classical banded reports from MDX-datasources.
-
And last but not least: The denormalized mode. This data-factory creates a streaming view over the fact-table, so that we can create crosstabs in the most efficient way.
Re: MDX from Hell no longer scares me
However, I'm aiming for the end of November to have a public milestone/preview/beta release. The goal for that release is to get as much feedback, usecases and additional requirements as possible, so that we do not end up creating a laboratory creature that no one wants to use.