Data Modelling .
This process involves structuring,organizing and connecting data tables for effective performance.Designing a good data model is important since it makes reports accurate and easy to understand.To set up a data model in Power BI is done by opening the report view and go to Data Model.It involves bringing many data tables together and connecting the relationship between them.
A schema is a structure that shows how data is organized and related in a data model
The two common data models are Star Schema and Snowflake
Star Schema
This data model contains one central fact table and many dimension tables.The dimension tables are connected directly to the Facts table.
The fact table is at the center sorrounded by the dimension tables forming the star shape.
Star schema is the data model mostly used in Power BI because it is simple to understand,executes DAX Functions effectively and its great for queries performance.
Snow flake schema
In this data model the Fact table is connected to dimension tables and the dimension tables can have a few dimension tables connected to them. A dimension table is connected indirectly to the fact table through another dimension table.
It is less preferred because of its complex nature, slow performance and its low speed in performing DAX functions.
Key difference between Star schema and Snowflake Data models
- Star schema contains a fact table sorrounded by dimension tables while snowflake contains a fact table connected to dimension table which are in turn connected to other dimension tables.
- In star schema a single join is used to connect the fact table to dimension table while in snowflake many joins are required.
Relationships in Data models
This explains how tables are connected in a data model.
Types of relationships
- 1 to many(1:*)-One record in a dimension data relates to many records in a fact table.
- Many to 1(*:1)-Same as 1 to many but viewed in the oppositedirection.
- Many to many(:)-multiple records in one table match multiple records in another table.
Facts table
This table stores the key values for your analysis.It stores measurable and quantitative values and the values are mostly numeric.
Example :Revenue,Gross profit,total sales,Quantity sold
- A fact table contains many records,used for calculations(SUM,AVERAGE,MIN,MAX)
- A fact table has many rows and few columns.
Dimension tables.
This tables act like a look up table for the values in the facts table.
Example:Employee name,Month,Country ,Region,Product category
This tables contain textual data and are used for grouping and filtering data.
They have many columns and fewer rows.
Why is good data modelling critical?
- Good data modelling ensures data accuracy by reducing data ambiguity and duplication.
- A good data model improves performance by reducing the number of joins and relationships.Like in the case of star schema which has one fact table and a few dimension tables.
- Simplified analysis -a well designed data model makes it easy to perform accurate DAX calculations.
- A good data model ensures data integrity by enhancing accurate storage of data hence maintaining its integrity over time.
Conclusion.
It is important for data analytics to understand data modelling since it plays a critical role in data analysis, reporting and decision making.A good data model improves performance and provides accurate data insights.
Top comments (0)