Unified Dimensional Model

• Articles
• >
• SQL Server
• >
• Analysis Services
• >
• UDM

Understanding the Unified Dimensional Model

To discuss the unified dimensional model the relational and dimensional models will be touched on first.

Relational Model

The short story is a relational model describes a way to organise or structure your database to efficiently capture and interact with data; some features are reduced data redundancy, referential integrity and data consistency. So this is the preferred method for designing any database that will be used for transaction based system like a call logging system or an online application that processes transactions and is the engine behind your distributed applications. These types of systems are called Online Transaction Processing Systems or (OLTP).

Dimensional Model

There’s also a model used for modelling databases that are optimised to be used for online analytical processing or OLAP. So whereas the relational model is used to design OLTP or transaction based systems the dimensional or multi dimensional model is used to design databases that are optimised for large quantities of data and analytics supporting the multi dimensional view of that data. Data warehouses are an example of the type of systems that use a multi dimensional model to store data. Dimensional models are designed to offer quick data retrieval and the dimensional model is designed to support OLAP.

• use relational database model that are going to support OLTP systems
• use the dimensional database model for databases that will support OLAP systems
• Regardless of the model you choose you’re still creating a relational database both the relational model and the dimensional model are relational database design techniques.
• The model you choose depends on the type of system you are designing (OLTP or OLAP)

Unified Dimensional Model (UDM)

Finally we have the unified dimensional model, the UDM is a logical data model defined within analysis services 2005 that’s used to represent one or more physical data sources such as your OLTP database or your data warehouse database. This logical model can represent data from a database with a relational database model or a database using the dimensional model it doesn’t matter, the unified dimensional model corporate features of each and as such called the unified dimensional model. All requests to analysis services from your reporting or business intelligence applications will use the UDM to access the underlying data source.

So once you have created the unified dimensional model you have this logical data model that gives you a lot of flexibility and do some pretty nice things. You can change some of the field names and make them more intuitive or user friendly, or even combine multiple data sources that will be transparent to the users of your application. You can enhance the data model in other ways like defining additional relationships or even adding new calculations or business rules in the form of a calculated field. You can also create named queries which allow you to view your data differently in the UDM that is defined in the data source. Now it’s important to note all these changes are isolated within the UDM and analysis services 2005 so you won’t be modifying your underlying databases at all.

Conclusion

• Unified dimensional model is a logical data model meaning it exists within analysis services 2005 and is merely a logical representation of your physical underlying data sources.
• We us the UDM within analysis services 2005 for OLAP or Data Mining both of these are two core services of analysis services and are built on top of the unified dimensional model. All the requests for data are used in the UDM, to access that underlying data source.