How to run relational queries trough an UDM
Well, this is a good question... A common understanding is that the source of an UDM can be a cube or a relational database. So how do you define a relational source for the UDM?
The idea of the UDM is that it's the only source for any reporting demand in an enterprise. So you collect all your relational schemas and use UDM to set up a dimensional model on top of it. It also controls access to the data because it carries all the security information you had to define at different places before. This model is used by all reporting tools as a data source.
So the question is not "how to feed a UDM with relational data" because this is the most natural thing. Every UDM has one (or more) data source, and this is relational (in most cases). Just think of the UDM as a cache for your (relational) queries. And this cache is what we call a cube. This can be ROLAP (here you have your relational data source, if you want without any caching) or MOLAP (the "classical" cube). Using 100% ROLAP (without any aggregation), 100% MOLAP (with aggregations and scheduled updates) or anything in between, that's your decision. The aggregation strategy is defined per partition (that means a logical part of a cube). You can mix ROLAP and MOLAP in one cube, process a partition regularly (based on a schedule) and use another partition with proactive caching which processes the partition based on notifications (when the underlying data changes). So real-time OLAP is becoming true with the speed of MOLAP aggregations. And you don't have to be careful what attributes you include in a cube... With SQL Server 2000 you had to think carefully about every dimension, level or member property you want to define... Might it be too much for the cube (regarding memory) or is it getting too complex (for the end user)?! You can forget about that in SQL Server 2005... Add all the attributes from your relational schema to the UDM! Because it's the single source of information for your enterprise your user will expect that!
The concept is really compelling. A single source of information, only one place to define security, one model where you provide an easy to understand access to your data for end users, which you can feed from different sources, a common place to define business logic (by storing complex calculations in the UDM), central definition of currency conversions and multi language support, that's nearly too good to be true. This is what Microsoft has to prove that it will work.
Back to the question... I hope I made things a little bit clearer. The only thing you can't expect that you can run SQL queries against the UDM. At least that's not the way you should do it. MDX is the language of the UDM. There might be a way to get it working with SQL, too (like there was a "workaround" for that in SQL Server 2000), but I didn't try that, yet.
If you have any comments on this please let me know...
Thomas