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)

The Unified Dimensional Model (UDM)

  • Comments 3
  • Likes

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. 

 

Comments
  • In my opinion the weak link in reporting services is that there is no tool for information workers to

  • I am really new to this, so let me see if I have it right. The UDM does not really replace any old method or model. It is really just a upgrade of OLAP.

    You will have your source data and then the UDM is "placed" on top to create a better view of the data as a whole. This is where I get a little confused but I will try. Now, that the data is making analytical sense we now build the Cubes on top of the UDM to generate the specific data and reports required from within the UDM.

    The key difference being that the UDM has now already done some of the work that the Cubes would have done? Ie: Aggregation, Sum, etc etc?

    Is that correct or am I still thinking of the 2 legged models who diet on cigarettes and Mineral water?

  • This is a good article. We are planning to have UDM in our company. We have around 10 Oracle Dbs a few Sequel server DBs and some MS Access DBs. Could you please suggest some tools and best practices available for implementing this UDM. Also waht is the start point?

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment