Chapter 2 - Kimball Dimensional Modeling

Question 1

Fundamental Concepts

Answer 1

Gather Business Requirements and Data Realities
Before launching a dimensional modeling eff ort, the team needs to understand the
needs of the business, as well as the realities of the underlying source data

Question 2

Fundemental Concepts 2

Answer 2

Collaborative Dimensional Modeling Workshops
Dimensional models should be designed in collaboration with subject matter experts
and data governance representatives from the business. The data modeler is in
charge, but the model should unfold via a series of highly interactive workshops
with business representatives. These workshops provide another opportunity to
fl esh out the requirements with the business

Question 3

Four-Step Dimensional Design Process

Answer 3

The four key decisions made during the design of a dimensional model include:

1. Select the business process.
2. Declare the grain.
3. Identify the dimensions.
4. Identify the facts

Question 4

What are Business Processes

Answer 4

Business processes are the operational activities performed by your organization,
such as taking an order, processing an insurance claim, registering students for a
class, or snapshotting every account each month. Business process events generate
or capture performance metrics that translate into facts in a fact table. Most fact
tables focus on the results of a single business process. Choosing the process is
important because it defi nes a specifi c design target and allows the grain, dimensions,
and facts to be declared

Question 5

What is the Grain

Answer 5

Declaring the grain is the pivotal step in a dimensional design. The grain establishes
exactly what a single fact table row represents. The grain declaration becomes a binding
contract on the design. The grain must be declared before choosing dimensions
or facts because every candidate dimension or fact must be consistent with the grain.
This consistency enforces a uniformity on all dimensional designs that is critical to
BI application performance and ease of use. Atomic grain refers to the lowest level at
which data is captured by a given business process. We strongly encourage you to start
by focusing on atomic-grained data because it withstands the assault of unpredictable
user queries; rolled-up summary grains are important for performance tuning, but they
pre-suppose the business’s common questions

Question 6

what are dimensions for?

Answer 6

Dimensions for Descriptive Context.
Dimensions provide the “who, what, where, when, why, and how” context surrounding
a business process event. Dimension tables contain the descriptive attributes
used by BI applications for fi ltering and grouping the facts. With the grain of a fact
table fi rmly in mind, all the possible dimensions can be identifi ed. Whenever possible,
a dimension should be single valued when associated with a given fact row.
Dimension tables are sometimes called the “soul” of the data warehouse because
they contain the entry points and descriptive labels that enable the DW/BI system
to be leveraged for business analysis. A disproportionate amount of eff ort is put
into the data governance and development of dimension tables because they are
the drivers of the user’s BI experience.

Question 7

What are the facts?

Answer 7

Facts for Measurements.
Facts are the measurements that result from a business process event and are almost
always numeric. A single fact table row has a one-to-one relationship to a measurement
event as described by the fact table’s grain. Thus a fact table corresponds to a physical
observable event, and not to the demands of a particular report. Within a fact
table, only facts consistent with the declared grain are allowed. For example, in a
retail sales transaction, the quantity of a product sold and its extended price are
good facts, whereas the store manager’s salary is disallowed.