Page 36 - DCAP606_BUSINESS_INTELLIGENCE

Unit 3: Dimensional Data Warehouse

3.2.2 Types of Fact Table Notes

There are basically three types of fact tables:

Transactional: A transactional table is the most basic and fundamental type of fact table.
The grain associated with a transactional fact table is usually specified as one row per line
in a transaction, e.g., every line on a receipt represents a transaction.

Periodic Snapshots: It takes a picture of the moment, where the moment could be anything
like performance summary of a salesman over the previous 3 months. A periodic snapshot
table is dependent on the transactional table.
Accumulating Snapshots: In this type of fact table the activity of a process is shown such
that it has a well-defined beginning and end.

Example: The processing of an order where an order moves through specific steps until
it is completed.
As steps towards fulfilling the order are completed, the row which is associated with it is
updated in the fact table. This type of table often has multiple date columns, each
representing a complete step in the process. Therefore, it’s important to have an entry in
the date dimension that represents an unknown date, as many of the milestone completion
time are unknown at the time the row is created.

Self Assessment

Fill in the blanks:
3. A fact table typically corresponds to an associative entity in the ..............................
4. Measures that can be added across only some dimensions are ..............................

5. .............................. take a picture of the moment, where the moment could be anything.
6. In .............................. table often has multiple date columns, each representing a complete
step in the process.

3.3 Dimension Tables

Dimension tables consist of attributes that describe fact records in the fact table. Some of these
attributes provide descriptive information; others are used to specify how fact table data should
be summarized to provide useful information to the person who is analysing the information.
Every dimension has a set of descriptive attributes. Dimension tables contain attributes that
describe business entities.

Example: The Client dimension can contain attributes like C_No., Area, State,
Country etc.

Did u know? In a dimensional table, columns can be used to categorize the information
into hierarchical levels.

LOVELY PROFESSIONAL UNIVERSITY 31