Unified Dimensional Model (UDM)
A word about Unified Dimensional Model we use in analysis services.
Introduction
To deal with users who want to retrieve data directly from data source, Microsoft introduced Unified Dimensional Model (UDM) with SSAS 2005 as a part. SSAS separates the physical layer and modeling part using UDM which supposed to have advantages both of OLTP and OLAP. UDM will use both Relation Model and Dimensional Model of Data.
Relational Data Model
This method was an oldest method proposed by Mr. Edgar F.Codd in 1969 for IBM, structured data using mathematical matrix relations, rows and columns it is called as a table. According to him a database should be a relational database if it satisfies two properties.
- Database should consist of Tables with Tuples or Unique Values.
- They should use keys.
Dimensional Data Model:
Dimensional Data Model is completely different from Entity – Relationship Model we normally use. Here data is stored in one or more dimension tables and fact tables. They are de normalized tables. Dimensional data modeling is used for calculating summarized data.
Relational Data Model vs. Dimensional Data Model
Relation Data Model mostly uses Relational Databases to store the data whereas Dimensional Data Model uses both Relational and Multidimensional Databases. Data in Relation Data Model stores in several tables with relationships whereas Dimensional has only few tables called fact tables connected to dimensional tables. Relational Data Model is time variant, Dimensional is invariant.
Relational Data Model mainly depends on SQL queries but dimensional uses MDX queries.
UDM provides bridge between the end-user and data sources, user queries the UDM with a variety of client tools such as excel. Advantages are simpler, understandable and improved performance for summarized queries.
For demonstration let me take help of my experiment partner “Adventure Works Dataware housing” database. Let suppose a higher authority want a report with a count and total sale amount of each product that having sales on internet as shown by below image.
I am creating a data source with Adventure Works DW2008R2 database on my local system.
My data source view look like below
I am taking Internetsales table as a base table and Product, DimCustomer, Dim Geography tables as associates. InternetSales table has foreingkey relationship with Product table as Productkey column and with DimCustomer as customerkey as well. DimGeography table has foreighkey relationship with DimCustomer specifying Geographykey.
Now I am moving to UI design form one of the tools provide access to UDM is Microsoft SQL Server tool that embedded for SSAS.
First left pane of the UDM is showing a tree view which has user understandability property. UDM also groups all the attributes into separate dimensions. Customer is one dimension and Product is another. Columns represent transaction values or measurements which are likely to be aggregate. This method which is using dimensions and measures is known as Dimensional Modeling and has to be identified as successful model for better user understandability. Right pane shows the elements in the query simply defined by user by dragging.
We can analyze through hierarchies for a quick review of sales.
Default language of UDM is English we have option to translate into different languages for the sake of international users, they will use system language packs. For UDM data sources can be Relational Databases, web services and even files. Client tools access the data through XML format. UDM are rated high at performance perspective. They have feature like Proactive caching, when we queried initially sent to UDM and are answered with latest data. In meanwhile, UDM will build a data cache of the data and aggregate data. Another perspective we will concentrate is on Security. UDM will use Role based Security as well as individual permissions for reading metadata and access the data.