Database Reference
In-Depth Information
There is a drawback with 3NF, that is, it is not a model built for querying; it requires
many joins in the queries to write the simplest report. So a simpler model of the data
is often necessary. A common model built for querying is the
dimensional mod-
el
defined by Ralph Kimball and is available at
http://www.kimballgroup.com/
. The
dimensional model defines those things that you want to measure and should be
stored in a fact table. Around the fact table, you will have multiple dimension tables
containing the things that you would like to slice the facts by.
In the preceding figure, you have the fact table defined containing each order row,
you will have the
measures
such as order quantity and sales amount, as well as the
keys referencing
Product
,
Seller
,
Time
, and
Customer
on each row of the table.
The dimension tables contain
attributes
such as
year
,
month
, and
day
in the
Time
table,
Customer name
,
address
, and
customer number
in the
Customer
table.
This design is often referenced to as a
star schema
.
AnalysisServicesisbuiltwithmultidimensionalmodelinginmindandworksbestwith
data warehouses or data marts that use this technique; however, as you will later
see, there is a possibility to work with all kinds of schemas in the database through
the use of data source views in Analysis Services.
During the course of this topic we will work with the
AdventureWorks2012DW
data-
base. This is a data warehouse built for the fictitious company called Adventure
Works Cycles. They have a data warehouse built using dimensional modeling with
several fact tables containing the things that they want to measure in their business.
Tip
For a background on Adventure Works and their business, refer to the following
description:
Search WWH ::
Custom Search