Scribd
Upload
95 views28 pages

What Is Fact?: A Fact Is A Collection of Related Data Items, Each Fact Typically Represents A Business Item, A

Facts represent business data or events that can be analyzed. There are three main types of facts: additive, semi-additive, and non-additive. Facts are classified as cumulative or snapshot based on whether they describe data over time or at a point in time. Dimensions provide context for facts and can be conformed, junk, or slowly changing. Common data warehouse schemas include star, snowflake, and galaxy, which differ in how dimensions are structured in relation to facts.

Uploaded by

RajaPraveen
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPT, PDF, TXT or read online on Scribd
95 views28 pages

What Is Fact?: A Fact Is A Collection of Related Data Items, Each Fact Typically Represents A Business Item, A

Facts represent business data or events that can be analyzed. There are three main types of facts: additive, semi-additive, and non-additive. Facts are classified as cumulative or snapshot based on whether they describe data over time or at a point in time. Dimensions provide context for facts and can be conformed, junk, or slowly changing. Common data warehouse schemas include star, snowflake, and galaxy, which differ in how dimensions are structured in relation to facts.

Uploaded by

RajaPraveen
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPT, PDF, TXT or read online on Scribd
You are on page 1/ 28

What is Fact?

 A fact is a collection of related data items,


consisting of measures and context data.
 Each fact typically represents a business item, a
business transaction, or an event that can be used
in analyzing the business or business process.
 Facts are measured, “continuously valued”,
rapidly changing information. Can be calculated
and/or derived.
Types of Facts
 Additive
Additive facts are facts that can be summed up through all of the dimensions
in the fact table.
 Able to add the facts along all the dimensions
Eg. Retail sales in $ (or) A sales fact
 Semi-Additive
Semi-Additive facts are facts that can be summed up for some of the dimensions in the
fact table, but not the others.
Eg. Daily balances fact can be summed up through the customers dimension but
not through the time dimension.
 Non-Additive
Non-Additive facts cannot be summed up for any of the dimensions present in the fact
table.
Eg. %(Percentages) , Ratios etc.,
Classification of Facts
 Based on the classification , there are 2 types of Fact tables.

 Cumulative Facts
 Snapshot Facts

 Cumulative Facts - This type of fact table describes what has happened over period of
time.

Eg. Additive Facts , Total sales by product by store by day or week or month or year.

 Snapshot Facts – This type of fact table describes the state of things in a particular
instance of time.

Eg. Semi-Additive & Non-Additive facts .


Factless Fact Table
 Some event tables have no obvious numeric facts (measures) are
called Factless fact tables.

 Events often are modeled as a fact table containing a series of


keys, each representing a participating dimension in the event.

Example :- Promotion table


PROMO ID Promotion Start Dt End Dt Description
1 Credit card 230413 270413 10% cash back
2 Credit Card 280413 010513 15% cash back

In the above example PROMO ID ‘s Surrogate Keys


and those are not measures.
Dimensions Types

 Conformed Dimension

 Junk Dimension

 Slowly Changing Dimension (SCD)

 Degenerated Dimension
Dimensions Types

 Conformed Dimension

A conformed dimension is a dimension, which is standard


across all data marts.

For example :- Enterprise Data Warehouse's data can


segmented into Sales Data Mart, Inventory and Shipping
Data Mart, Finance Data Mart, Geographical Data Mart,
HR and Management Data Mart and so on.
Dimensions Types
Dimensions Types

 Junk Dimension

Junk Dimension is used to records a collection of low-


cardinality Flags and Indicators data.
Flag data may be non-generic question's answers like
Yes/No or True/False or Activate/Deactivate.
Indicator data may be tiny text data like Height, Width,
Weight, Color, Status.
Dimensions Types
Figure 1 :
Dimensions Types
Figure 2 :
Dimensions Types

 Degenerated Dimension

The term degenerate dimension, refers to a field that will be


used as a criterion of analysis and that is stored in the fact
table.

For example :- If any fields from dimensions can not


perform grouping or summarized by the field in the fact
table.
Item number, Ticket numbers, Transaction number etc., are
examples of degenerated dimensions.
Data marts (DM)

- Data Mart is a subset of Data Warehouse.

It is really similar to a data warehouse but limited in scope and purpose


and is usually aligned with one department, function, application or
business unit.

Several names for DMs:


• Departmental DSS DBs
• OLAP Data bases
• multi-dimensional DBs (MDDB) or Cubes
• lightly summarized tables
Data marts Types

• Dependent data marts are marts that are fed directly by the DW,
sometimes supplemented with other feeds, such as external data.

• Independent data marts are marts that are fed directly by external
sources and do not use the DW.

• Embedded data marts are marts that are stored within the central DW.
They can be stored relationally as files or cubes.
Operational Data Store (ODS)
An ODS

• pulls together, validates, cleanses and integrates data

• foundation for providing integrated view of enterprise data.

• tactical decision support, day-to-day operations and management


reporting.

Characteristics
 Integrated
 Subject-oriented
 Volatile (including update)
 Current valued
Types of Schemas

- Star schema

- Snowflake schema

- constellation (or) Integrated (or) Galaxy (or)


Hybrid schema

- Fact Constellation Schema


Star Schema Design

 Single fact table surrounded by denormalized dimension


tables

 A star schema can be simple or complex .

 A simple star schema consists of one fact table where as a


complex star schema have more than one fact table .
Example of Star Schema
Snowflake Schema

 Single fact table surrounded by normalized dimension tables.

 A snow flake schema is an enhancement of star schema by


adding additional dimensions.

 Snow flake schema are useful when there are low cardinality
attributes in the dimensions.

 slower performance due to joins.


Example of Snowflake Schema
Galaxy Schema

 Galaxy schema contains many fact tables with some


common dimensions (conformed dimensions).

 This schema is a combination of many data marts.


Example of Galaxy Schema
Fact Constellation Schema

 It is a process of joining 2 fact tables.

 The dimensions in this schema are segregated into


independent dimensions based on the levels of hierarchy.
Example of Fact Constellation
Slowly Changing Dimensions

 Dimensions that change over time are called Slowly


Changing Dimensions (SCD).

 For instance ,Product price changes over time; people


changes their names for some reason; Country and State
names may change over time.

 Slowly Changing Dimensions are categorized into three


types namely Type1 ,Type2 and Type3 .
SCD Type1
 SCD Type 1 : Overwriting the old values.

 No History will be maintained in Type1.

 Example : In year 2004 product price $150,


suppose year 2005 product price got changed. Then records will be updated as below.
SCD Type2
 SCD Type 2 : Creating an another additional record.

 Full History will be maintained in Type2.

 Example : In year 2004 product price $150, year 2005 product prices will be maintained.
SCD Type3
 SCD Type 3 : Creating new fields.

 Partial History will be maintained in Type3.

 Example :
SCD Type3

 Example :

You might also like