All the information that describes a business entity should be included in individual
table.
- e.g. Customer, Product, Employee, Sales, Return, Store, etc.
A right way to define a table is expand
it's attributes.
- e.g. Product Table should have Name, Group, Category, Sub Category, etc.
So that we can create a relationship
between two different Table ( Business Entities )
It creates a better granularity
and help us to describe each attribute in more simple way.
-
Remove
the duplicated or redundant data. -
Reduce
the size of the table. -
Having a seperate table for each Business entity.
- Creating a
single
table with all Business entity and attributes including redundancy and duplicated data.
Best
Schema for Power BIFacts
table in the middle andDimensions
tables are around.- Each Dimension table has
one
tomany
( 1 to * ) relationship with thecolumn
of Fact table. - Fact table consist of various
Foreign
keys columns, it is connected with one column ( Primary key ) of each Dimension tables. No
direct relationship is possible between the Dimension tables.
- Data table with all the important
quantitative
numeric metrics. - price, quantity, cost, margin, etc.
- Transactions, Sales, Returns, etc.
- Especially used for Aggregating ( SUM, AVERAGE, MIN, MAX, COUNT )
- Contains various
Foreign
keys and related to many dimension tables.
- Something that describes a
fact
- Describes the
qualitative
attributes of the table ( group, category, subcategory ) - No duplicates or redundancy.
- Especially used for grouping, slicing and filtering.
- Contains only
Primary
keys andUnique
keys ( Single row for each data )