Thursday, February 22, 2007

Why use UDM?

Continuing with my research about Data Warehousing, the following are some of my notes in Data Modeling. Taken from this tutorial in SQL Server from msdn.

UDM - Unified Dimensional Model

A user who wants to retrieve information directly from a data source, such as an Enterprise Resource Planning (ERP) database, faces several significant challenges:

  • The contents of such data sources are frequently very hard to understand, being designed with systems and developers instead of users in mind.
  • Information of interest to the user is typically distributed among multiple heterogeneous data sources. Even if dealing only with different relational databases, the user must understand the details of each, such as the dialect of SQL that is used. Worse, those data sources might be of very different types, including not only relational databases but files and Web services.
  • Whereas many data sources are oriented toward holding large quantities of transaction level detail, frequently the queries that support business decision-making involve summary, aggregated information. With increased data volumes, the time that is required to retrieve such summary values for interactive end-user analysis can be prohibitive.
  • Business rules are generally not encapsulated in the data sources. Users are left to make their own interpretation of the data.

The role of a Unified Dimensional Model (UDM) is to provide a bridge between the user and the data sources. A UDM is constructed over one or more physical data sources. The user issues queries against the UDM using a variety of client tools, such as Microsoft Excel.

Clients access all data sources through single UDM

There are advantages to the end user even when the UDM is constructed only as a thin layer over the data source: simpler, more easily understood model of the data, isolation from heterogeneous backend data sources, and improved performance for summary type queries. In some scenarios, a simple UDM can be constructed automatically. Greater investment in the construction of the UDM can generate additional benefits that accrue from the richness of metadata that the model can provide.

The UDM provides the following benefits:

  • Greatly enriches the user model.
  • Provides high performance queries supporting interactive analysis, even over large data volumes.
  • Captures business rules in the model to support richer analysis.
  • Supports ‘closing the loop’: letting users can act upon the data they see.