fact less fact tables & aggregate tables

Fact Less Fact Tables

&

Aggregate Tables

in

Dimensional Modeling

By:

Prof. Sunita Sahu

Assistant Prof.,VESIT,Mumbai

Fact less Fact table

Fact less Fact table does not contain any facts.

Generally Fact less fact tables are used to record

the events such as students attendance,

attendance of participants for an Event like a

Meeting.

There are applications in which fact tables do not

have non key data but that do have foreign keys for

the associated dimensions

Fact less Fact table Example

Aggregate Fact Tables

Aggregates are pre calculated summaries derived

from the most granular fact table(level of detail in

the fact table).

If the fact table is at the lowest grain.

The facts or metrics are at the lowest possible level

at which they could be captured from the

operational system.

what is the Advantages of lowest grain?

Choose a simple STAR schema with the fact table

at the lowest possible level of granularity.

Assume four dimension table with most granular

fact table.

Operational system-single order, invoice, product

and so on.

Data warehouse- produce large result set(these

retrieve hundreds and thousands

Aggregate Fact Tables

Fact table size

Need for aggregates

Aggregating fact table

THREE WAY