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
- They should use keys.
Dimensional Data Model:
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
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.
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
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