Insufficient data from Andrew Fryer

The place where I page to when my brain is full up of stuff about the Microsoft platform

The Unified Dimensional Model (UDM)

It’s coming up to London Fashion Week so I thought a post on models for all you dedicated followers would be good.  The models in this post are rarely size zero ! So what is one of these?  Technorati isn’t much help but if you look hard you will find some posts e.g. by one of my predecessors Mat Stephen and one by   Mark Frawley.  So I thought a short post on what it is might be useful.

The UDM is at the core of analysis services.  It is the semantic layer between the source data and the end user experience irrespecive of the source.  The design surface of the UDM allows you to use any relational data to build a data source view.  The objects in this view can be tables or views in the source or a query over the top of these.  Thus you can create a sort of virtual data warehouse over an operational system.  This flies in the face of alot of accepted BI practice but Micrsosoft arealways providing choices and leaving it up to the user to decide what  is best for them.  

If we look at good old adventure works data source view using the BI Development studio (BIDS)

We can see it looks like an early design for an intel CPU.  Zoom in and each box is a query table or view.  The calculations for each of the derived attributes can be accessed from a right click and here we can see that the UDM has stored a SQL snippet to derive the CalendarQuarterDesc attribute.  A good tip is to create a separate view for each fact table and you will see that this has been done in the other views in adventure works.  Once of you have got this right you are ready to build dimensions, and cubes.  So not that hard after all and this is pretty much the same in analysis services 2008 form what I have seen so far.