Business intelligence is a word of mouth for every business user. In this series of articles I will begin explaining how Microsoft has done a great job in the world of business intelligence using Microsoft SQL Server Analysis services. I will try to explain as step-by-step as possible the process. Please note the articles will start somewhat theoretical then proceed practically as we go along.

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.

The UDM is a logical data model that represents a view of the underlying data sources (simply the physical databases) to provide a platform for SSAS to create OLAP, as well as data mining services, by supplying them with a virtual view of a single logical database.