Tuesday, April 6, 2010

DW Terms

Dimension:
Dimension Table contains description of Facts.
The dimensions are generally non-numeric and correspond to the what, when or where aspects of a question.
For example, the Store table contains store names and addresses; the Product table contains product and packaging information; and the Period table contains month, quarter, and year values.

Fact:
Fact tables provide the (usually) additive values that act as independent variables by which dimensional attributes are analyzed.
The facts also called as measures are generally numeric and correspond to the how much or how many aspects of a question.

Confirmed Dimensions:
"Dimensions which are common for all the fact tables are called Confirmed dimensions.
Exa. Time, Geography"

Fact Less Fact:
A factless fact table is a table that contains nothing but dimensional keys.  Ralph Kimball’s earlier article is still the best source to learn this.
There are two types of factless tables.  One is for capturing the event.  An event establishes the relationship among the dimension members from various dimension but there is no measured value.  The existence of the relationship itself is the fact.
This type of fact table itself can be used to generate the useful reports.  You can count the number of occurrences  with various criteria.  For example, you can have a fact less fact table to capture the number of stores in a particular region or you can have a factless fact table to capture the student attendance (the example used by Ralph).  The following questions can be answered:
  • Which class has the least attendance?
  • Which teachers taugh the most students?
  • What is the average number of attendance of a given course?
All the queries are based on the COUNT() with the GROUP BY queries.  I think that the interesting metrics are the nested GROUP BY so you can first count and then apply other aggregate functions such as AVERAGE, MAX, MIX.
The other type of factless table is  called Coverage table by Ralph.  It is used to support negative analysis report. For example a Store that did not sell a product for a given period.  To produce such report, you need to have a fact table to capture all the possible combinations.  You can then figure out what is missing.

No comments:

Post a Comment