Dimension Tables Types
Dimension table contains the master data information. A dimension table has a primary key column that uniquely identifies each dimension record. The dimension table is associated with a fact table using the primary key.
confirmed dimension — The value will be refered by more than one dimension table
junk dimension — Combined values of transaction
degenerate attribute — If a dimension data kept on a fact table for reference
Role Playing Dimension — Date dimension which act as a orderdate dimension and purchasedate dimension.
Types of Dimension Tables in SSAS was last modified: October 5th, 2017 by Justin Antony
Fully Additive Facts: This measure which can be added across all the associated dimensions.
Example: sales amount is a fact which can be summed across different dimensions like customer, geography, date, etc.
Semi-Additive Facts: These are facts which can be added across only few dimensions.
Example: bank balance is a fact which can be summed across the customer dimension but it cannot be added across the date dimension
Non-Additive Facts: These are facts which cannot be added across any of the dimensions in the cube.
Example: profit margin is a fact which cannot be added across any of the dimensions. we can not add against product dimension.
Derived Facts: Derived facts are the facts which are calculated from one or more facts,
For example: profit margin.Which is calculated when needed
Factless Facts: Fact table will not have a measure they will have only the key values(Foreign key) refered to the dimension are called factless fact table.
Types of measures/facts tables was last modified: October 1st, 2017 by Justin Antony
The table which contains METRICS. This table will store only the measure values.The primary key of a fact table is usually a composite key made up of all of its foreign keys. This Fact table will locate at the center of the Star and snowflake schema
What is a Fact Table ? was last modified: October 1st, 2017 by Justin Antony