Cube is nothing but a combination of
Measures (Fact Tables) and Dimensions Tables. Dimensions are like primary
tables like Company, Client, Employee etc, and Measures (fact) are tables like
our calculated number are stored into that like revenue, cost and budget data.
Here is an example how Facts and Dimensions tables;
Dim.Customer:
|
Column
|
Data Type
|
|
CustomerKey
|
Int (identity)
|
|
Customer Name
|
Varchar(25)
|
|
CustomerAdd
|
Varchar(50)
|
|
CustomerCity
|
Varchar(20)
|
|
CustomerTel
|
Varchar(12)
|
|
CustomerEmail
|
Varchar(20)
|
|
CustomerZip
|
Varchar(10)
|
Fact.Profitability:
|
Column
|
Data Type
|
|
ProfitKey
|
Int (identity)
|
|
CustomerKey
|
Int
|
|
Budget
|
numeric(18,0)
|
|
Revenue
|
numeric(18,0)
|
|
Cost
|
numeric(18,0)
|
|
DateKey
|
Int
|
Note: Date Key is derived from Date Dimension table DateKey
is primary key of that.
Dimension and Fact Tables relations: (Star Schema and Snowflake
Schema)
Dimensions and fact tables can have the relations with two ways:
Star Schema: In this schema one fact table are associated
with multiple Dimensions tables like star. In this schema only one dimension
are directly connected with fact table.
Snowflake Schema: In
this schema one fact table are associated with one or more Dimensions tables
but unlike star schema the dimensions are associated with each other with their
parent-child relations. In this schema one dimension are associated with
another dimensions and final dimension directly connected with fact table.
Comments