Partager via

Microsoft SQL Server 2005 Analysis Services – Unified Dimensional Model (UDM). Can you get your head round it? If you do BI, you’ll need to!

  • Article

There are very many, and from my own professional perspective, too many (I’ve got to try and learn all this stuff) new features in SQL 2005.  Some of them can be described as ‘catch up’ features and some of them might be described as ‘tick in the box’ features.  There is one that is wholly revolutionary and which shouldn’t be described as a feature and that is the Unified Dimensional Model (UDM).

 

I have read and re-read articles that talk about the UDM, some internal, some public, and I’m still trying to get my head all the way round it.  It is beautiful, to be sure, but it is a major shift in paradigm.  This means you have to chuck out some of the old mind set before you can let the new stuff gel; all of which just adds to the overall learning effort.

 

To help you on this journey I’m going to point you towards some articles I’ve read and some I haven’t.  Firstly however, I’m going to give you some facts you should keep in mind while you travel on this voyage of discovery, facts that should help break the old mind set and lubricate the transition to this new enlightenment.  Then I’ll outline some of the key benefits of the UDM to wet your appetite and to keep you motivated through the morphing process.

 

The big fact: The “UDM- Server” is Microsoft SQL Server Analysis Services.

 

  • A UDM provides a single dimensional model for all OLAP analysis and relational reporting needs.  So you can use either MDX or SQL
  • Perspectives are the new data marts
  • Cubes are largely transparent concepts downgraded to the status of caches
  • Commonly you’ll only have 1 cube with multiple measure groups and multiple perspectives.
  • Its better to think of measure groups instead of cubes; partitions now apply to measure groups.
  • Virtual cubes are gone
  • Whilst a UDM can gather data from numerous data sources, the need to cleanse data still requires a data warehouse.
  • A cube is structured around dimensional attributes (previously known as member properties) rather than dimensional hierarchies.  Hence the virtual dimension, as a term, is now gone and concept converted to a real, first class, dimension.
  • UDM has five new dimension types, Role Playing, Fact, Reference, Data Mining and Many to many.

 

So what are the benefits over AS 2000?

 

  • The best of both the OLAP world and the best of the Relational world whilst shedding the worst of both – this is the revolution!
    • Flexible schema
    • Real time data access
    • Single data store
    • Simple management
    • Detail reporting
    • High performance
    • End-user oriented
    • Ease of navigation and exploration
    • Rich analytics
  • Data from multiple data sources can be made to behave as if they’ve come form one – so you can do table joins across those sources as if the tables were in same database.
  • Apart from the data warehouse, no data staging is required.
  • Real-time OLAP (proactive cache as opposed to, and in preference to ROLAP) – a platform for the real-time enterprise and Business Activity Monitoring.
  • Currency conversion.
  • Language translation.
  • No need for dimensional schemas, star or snow flake.
  • MDX scripting to replace calculated members and to poke calculations into any corner of a cube.
  • Storing KPIs.
  • Stored procedures.
  • Server side conditional formatting.
  • Categorising of dimensions and other objects.
  • Display folders  - for meaningful grouping objects (KPIs, hierarchies etc).
  • Controlled default ordering.
  • Discretisation of continuous values into brackets – e.g. salary bands.
  • Highly flexibletime dimensions that don’t require underlying tables.
  • Integration with data mining by way of data mining dimensions.
  • Scalable, flexible, fine grained server based Security.

 

There’s more – but I can’t keep my eyes open, its late, I’ve had too many sherbets and wine gums …. I’m off to bed