SQL Server Analysis
services separated the database physical layer from the data modeling part
using a logical data view called the Unified Dimensional Model (UDM). The UDM
is supposed to combine the advantages of OLTP and OLAP.
Relational Model
The traditional
database model that has been used for long time (especially for the OLTP kind
of databases) is the relational database model. Although, that it is outside
the scope of this article to discuss the relational model, I will try in short
to summarize and point out some of the features of this model in order to be
able to see the benefits of the UDM in SSAS.
The relational
model (sometimes called normalized model) offers the following advantages over
the flat file model:
·
Reduce redundancy of data by eliminating repetition
·
Improve data consistency by enforcing some constraints (unique,
referential integrity, etc…)
·
Optimized for transaction based systems where you have repetitive
data entry
Dimensional Model
Reporting needs
have increased and the request for timely, as well as summarized data (even
from multiple heterogonous systems), has increased lately with the focus on
making decisions based on that information. Despite the fact that OLTP system
contains the data, it was costly to generate data from the system directly for
two main reasons.
·
The OLTP system is being used most of the day by the business
users (creating a query against the database usually kills the performance of
your production environment especially if you are joining multiple tables).
·
You may need to access data stored in two different databases (or
even more) to get the report done
To solve the
above problem, we came up with the dimensional model which is optimized for
analytical processing as well as reporting. That kind of model supports large amounts of data that can be retrieved in a fast way for reporting purposes.
Unified Dimensional Model (UDM)
The UDM
represents a logical data model for your OLTP as well as OLAP databases in
SSAS. You should remember that UDM represents your actual physical databases as
data sources for your project.
In addition, the
UDM can be used to create new relations between tables from different data
sources.
Another nifty
feature would be the ability to create new calculated fields as well as to
create your own named queries (views) of the UDM.
Please note that
all of these changes you make to the data model in the UDM do not affect in any
way the underlying databases that formulate the UDM.