Designing the Data Warehouse for Analysis Services - Expert Cube Development with SSAS Multidimensional Models - page 6

Nevertheless, if you are reading this topic, it means you are using Analysis Services
and so you will need to design your data marts with specific features of Analysis
Services in mind. This does not mean you should completely ignore the basic theory
of data warehouse design and dimensional modeling, but instead, adapt the theory
to the practical needs of the product you are going to use as the main interface for
querying the data.

For this reason, we are going to present a summary of the theory and discuss
how the theoretical design of the data warehouse is impacted by the adoption
of Analysis Services.

Data modeling for Analysis Services
We will now have a look at some of the concepts of data modeling such as tables
and schemas.

Fact tables and dimension tables
At the core of the data mart structure is the separation of the entire database into two
distinct types of data structures:

• Dimension : A dimension is the major analytical object in the BI space.

A dimension can be a list of products or customers, time, geography, or
any other entity used to analyze numeric data. Dimensions are stored in
dimension tables.
Dimensions have attributes. An attribute of a product may be its color,
its manufacturer, or its weight. An attribute of a date may be simply its
weekday or its month.
Dimensions have both natural and surrogate keys. The natural key is the
original product code, customer ID, or real date. The surrogate key is a new
unique integer number used in the data marts as a key that joins fact tables to
dimension tables. Also, the surrogate key need to be meaningless and relate
only to the data warehouse.
Dimensions have relationships with facts. Their reason for being is to add
qualitative information to the numeric information contained in the facts.
Sometimes, a dimension might have a relationship with other dimensions,
but directly or indirectly it will always be related to facts in some way.

• Fact : A fact is something that has happened or has been measured. A fact

may be the sale of a single product to a single customer or the total amount
of sales of a specific item during a month. From our point of view, a fact
is a numeric value that users would like to aggregate in different ways for
reporting and analysis purposes. Facts are stored in fact tables.