One of the major new features in Yukon BI is the Unified Dimensional Model, or UDM, and one of the big benefits of the UDM is the seamless integration of relational and multi-dimensional (MOLAP) data. The idea is that you define the data logically and then decide separately how parts should be physically implement--using purely relational storage, by adding explicit MOLAP aggregations, or by some cool new combinations. It's a great concept.
Most people I've talked to who have heard about the UDM in Yukon have interpreted the idea of “seamless integration“ to mean that they could arbitrarily write either SQL or MDX queries against the UDM. I have to admit that I am one of those who made that assumption. The reality is that to use the UDX you always (for all practical purposes) use MDX, regardless of whether the data is stored relationally or multi-dimensionally. When I first found out that accessing the UDM requires MDX, I was a bit, shall we say, disappointed. I'm a big fan of MDX, and know that there are a lot of upsides to using MDX for reporting, but it has always been plagued by a few serious “downsides“ that made it unusable in some situations, so it was hard to envision building all reporting queries using MDX.
But most of the downsides of MDX have actually been limitations of Analysis Services 2000. For example, it's easy in SQL to create a query to group by, say, Invoice Number, even if there are 10 million invoices. (Not smart, necessarily, but easy.) In AS2K (at least 32 bit), it was simply impossible to create that kind of query. But the limitation was actually how big the dimension could be, not the MDX query per se.
Other limitations that were part of MDX have been removed in Yukon. For example, in SQL, if the filter is WHERE Type In (1,2,3), but there is no type 2, the result set simply does not include item 2. In AS2K MDX, if you request Types 1,2,3 on rows, and there is no type 2, the query fail. This is simply fixed in Yukon.
Some other time, I'll wax eloquent about the upsides of MDX as a reporting language.
Reed
