The other week I went to an evening event at Microsoft’s UK office in Reading, given by Matt Stephen. It was a general introduction to BI in SQL2005 and as such, attended by people who didn’t know much at all about the new features in AS, RS, IS and so on. All the familiar Powerpoints were shown and much was made of the Unified Dimensional Model as being the best thing since sliced bread. I’m sure almost everyone reading this has seen these presentations, especially the slides where the relational reporting and olap reporting worlds ‘come together’ like two pieces of a jigsaw and the one where Analysis Services is described as a cache on top of your data warehouse. At the end of the session, though, the very first question that was asked was which I think had been on a lot of people’s minds – "What exactly is the UDM?". This reminded me of the first time I saw any presentations on Yukon AS at an airlift in Redmond two-and-a-half years ago: for a while afterwards I was confused over what exactly the UDM was too. And Myles Matheson in a blog entry from a month or so back feels obliged to answer exactly the same question so I suspect this is a common reaction.
The answer of course is actually pretty simple. Put simply, the UDM is just the cube in Analysis Services 2005; because it can now model so many more features of a relational data warehouse (eg many-to-many dimensions, role-playing dimensions) the message is that there’s now no reason to run queries directly against your data warehouse at all because you’ll get much better performance and query-flexibility by building a cube and querying that instead. From a technical point of view I have no problems at all with the claims being made here – in my experience AS2005 lives up to its hype as much as any software product can – but I didn’t understand why all this talk of the UDM and the resulting confusion was necessary. Why not just talk about the new capabilities of cubes in AS2005?
Then I came up with the following theory. The UDM doesn’t exist as a feature, really, but is more of a marketing concept. Marketing concepts are meant to help sell a product. For AS2005, who are the new customers that Microsoft is trying to target? Probably the same new customers that AS2000 won over, people who hadn’t been customers of other BI companies but who had either been priced out of the market or were trying to hand-code their own BI solutions using a relational database and encountered the usual problems. They’re going to be easier to sell to than someone who already has a big investment in Cognos, Essbase or Oracle. In my experience there’s a vast amount of people out there who are still in this position, but in contrast to the people who picked up on AS2K they’re by nature a bit more cautious and unwilling to leave their relational comfort-zone – they know about OLAP but they’re not sure they want to learn a new technology. This constituency is, in my opinion, who the whole UDM pitch is aimed at: let’s not talk about cubes, because that might frighten you, but let’s talk about the cube as a cache (which is less threatening) and the UDM as something that is the successor of both relational reporting and OLAP reporting.
So this is why I think I was confused: I was meant to be confused. Quite a clever strategy to avoid knee-jerk anti-cube prejudice or fear, then, if it works. But does it work? Well, maybe, maybe not. The fact I was confused doesn’t really matter because I’m cube-friendly anyway, but for the confused relational guy his first reaction to hearing about the UDM is to start asking questions to try to clarify the situation. And what I found interesting at Matt Stephen’s presentation was that the second question asked was exactly the same question that I asked when I was trying to understand what the UDM was: since the UDM is a replacement for both OLAP and relational reporting, can you therefore run both SQL and MDX queries against the UDM? The answer is a qualified no, because although AS2005 like AS2K does support querying using a very limited subset of SQL, it is a very limited subset and isn’t practically useful. You have to learn MDX to query your UDM or buy a tool that will generate MDX for you. I suspect that this is the point where many relational guys turn off, having realised that the UDM is the cube and that they’ll still have to learn a completely new, non-SQL technology.
This then leads on nicely to the question of whether OLAP is better off shoe-horned into the relational world and queried with SQL, as I understand Oracle have done with what used to be Express, or whether it’s better off as a distinct technology with its own query language as Microsoft have done. I touched on this topic a few months ago here, and as you might have guessed I’m in favour of the Microsoft approach. I don’t blame Microsoft for trying to blur this distinction though, as anything that will get people to look at AS2005 is a good thing in my book. It’s just that I’m not sure that your average BI customer can be hoodwinked in this way for very long, that’s all…
"The UDM doesn\’t exist as a feature, really, but is more of a marketing concept" I\’m glad someone\’s finally had the balls to say that. I spoke to someone recently who had gotten a MS employee from Redmond to finally own up to this fact.-Jamie
There was similar MS marketing obfuscation around .NET, since it embraced disparate technologies, both existing (like SQL Server) and new (like CLR). But maybe Shakespeare had it about right?"What\’s in a name? That which we call a roseBy any other word would smell as sweet." –From Romeo and Juliet (II, ii, 1-2) – Deepak
Hi people,UDM is a concept. Not a marketing concept. UDM is a concept in the same way that OLAP, datawarehousing or changing dimensions are concepts and not features. Some products have features that support building features on that concepts. UDM says that is better to have just one model to see enterprise data, one way to access all data, it doesn\’t matter where the data physically exists (at least for the end user:).IMHOPSM (Pablo Mugica)
I will first admit that I do come from the other side down. I am on the marketing / sales side trying to figure out the technology. Sales guys call me technical and technical guys call me sales.You made this comment: "The answer is a qualified no, because although AS2005 like AS2K does support querying using a very limited subset of SQL, it is a very limited subset and isn\’t practically useful." But I asked one of my co-workers about this to be able to apply the practically of the technology comes out. As Nick says in his blog (http://nickbarclay.blogspot.com/2005/10/ssrb-ssas-report-models.html) You can put Report Builder on top of your UDM. Does Report Builder support this "simplistic" version of SQL that you are refering to, or is there another way around this. This just seems like a hole in the product that is someone functionally overlooked if you are not able to right SQL in some form against the UDM. It would be a great little product idea to flog if MS didn\’t do it themselves.- Mark
We tried OLAP technology and we\’ve been disappointed by the lack of client tools and the lack of flexibility (updates are difficult, need of rebuilding …)Currently, we use micro-cubes (Business Objects) coupled with Sybase IQ :http://www.rittman.net/archives/001368.htmlOur customers are very happy (great performance and great flexibility).My opinion is that OLAP technology is promising but not yet maturebut maybe i\’m wrong …
I think that the Semantic Model Definition Language (SMDL) used to create Report Models in the new version of Repoting Services is the raw version of what UDM stands for… may be MS will add multidimensional data sources in future versions of the spec.B wellPSM
Mark/Pablo,In fact Report Builder does generate MDX and not SQL when you put it on a cube, but if you actually try to use RB on top of a cube (full blog post on this topic coming soon…) you\’ll realise that this AS support is something of an afterthought and it\’s very much a relational query tool. The whole data navigation (see http://blogs.msdn.com/bobmeyers/archive/2005/10/15/481355.aspx) philosophy makes lots of sense for relational sources but is completely non-intuitive for an AS source.
Hi Chriss, have you seen this?http://support.microsoft.com/?kbid=899825It tells you what are the limits if you use RB with SSAS.PSM
At last some clarity on this new concept. I agree it is an excellent concept but the blurriness of where cubes and UDMs start and end was giving me a migraine! Thanks for finally saying it Chris!!!
This blog made me feel good, because after reading many white papers and watching several presentations I was still trying to understand what is exactly the UDM and I was starting to think that maybe I\’m becoming too old for this business.Ah, and it was also well written!
Good article. I\’m still a tad confused…In SSAS I can create and OLAP project – that uses an OLTP Data Source. Then using the Cube wizard, I can create dimensions, measures and fact information that are conformed.
You speak of the Cube as the "cache" – well from what I see, using Data Source views as my "ET – Extract & Transform" method, I have bypassed the need for a data mart – which UDM is all hyped up about.
You also mentioned that using SSAS to design my dimensions, measures, etc really is UDM – I believe that statement is false if your data source is an actual data mart.
Hi Joe,
A couple of points:
You can certainly build a cube on top of an OLTP system, but that doesn\’t mean that data marts or data warehouses aren\’t very useful. The point about a data warehouse is that it tracks history – an Order from three years ago might not be hanging around still in your OLTP system, but it should be there in the data warehouse; and if Salesman X moved from covering London last year to covering Manchester this year, your data warehouse would probably track that change too. Data source views are ok for doing some basic remodelling of data but they\’re no substitute for a tool like Integration Services when it comes to doing the kind of complex transformations that you need to do in a data warehouse. And the UDM, in Microsoft\’s marketing literature, isn\’t anything to do with ETL or dimensional modelling anyway, it really is another term for Analysis Services, regardless of what data source you\’re using and how its modelled.
Good article. This is my suggestion if still it\’s not clear what is UDM. First read and understand what is OLAP, Cube if this concept is clear, then by now you know what is UDM.
I think what Microsoft try to do is push other databases one step back like Oracle, Sybase. Microsoft knows that it\’s hard to ask companies to replace Oracle or Sybase with SQL Server..so why not bring SQL Server in between OLTP database and Reporting Solutions (cubes and flat report). What Microsoft is trying to tell, you can have any database you want but keep your aggregated data in OLAP/AS2005 (as cube) and then query or let your reporting solution ready from there…. it\’s fast and good. Well does come with bit pain and takes time. Lot of good BI soltions but performace not good when using Oracle and Sybase databases…ok then replace you other OLTP database with SQL Server….there you go…
I am Microsoft guy…but open for all databases. My suggestion is take your time to understand and implement new Microsoft solutions or you are really going to hate it. There are lots of good stuff in SQL Server 2005 but you may not be able to build it right in first try…
Good luck – Ashok