Download Data Modeling in Power BI: A Comprehensive Guide and more Study notes Advanced Data Analysis in PDF only on Docsity!
DATA MODELLING IN POWER BI
Contents: -
**1. Introduction to Data Modelling
- Benefits of Data Modelling
- Different Schemas in Data Modelling
- Steps to create Data Model
- How to Create Relationships in Power BI
- Cardinality
- Things to keep in mind INTRODUCTION TO DATA MODELLING** Data Modeling is one of the features used to connect multiple data sources in BI tool using a relationship. A relationship defines how data sources are connected, and you can create interesting data visualizations on multiple data sources. With the modeling feature, you can build custom calculations on the existing tables and these columns can be directly presented into Power BI visualizations. This allows businesses to define new metrics and to perform custom calculations for those metrics.
BENEFITS OF DATA MODELING
By modeling your data, you can document what types of data you have, how you use it, and the data management requirements surrounding its usage, protection, and governance. The benefits of data modeling include: Creating a structure for collaboration between your IT and business teams.
The star schema offers simplicity and a single granularity level, reducing the number of joins required for running queries. However, it can be rigid and difficult to extend the schema if the business needs change; hence, it’s ideal for data marts with simple data relationships. Snowflake: Another schema commonly used in data warehouses, the Snowflake schema closely resembles the star schema and is employed for complex queries and advanced analytics. Like the star schema, the snowflake schema has a single fact table connected to multiple dimension tables, but these dimension tables link to other related tables.
This further branched-out relationship from the dimension tables to other tables creates the snowflake structure. The normalized Snowflake schema allows storing more data with less storage space and is excellent for performing complex queries and aggregations. However, running queries may involve complex joins, slowing query performance. Fact Tables A fact table stores quantitative, numerical data that can be aggregated and analyzed in different ways. It typically contains information about specific events, transactions, or measurements and serves as the centerpiece of a data model. In a fact table, each row represents a single transaction or event, and each column represents a measurable attribute or fact about that event. For example, a sales fact table might have columns for the date of the sale, the product sold, the quantity sold, and the revenue generated.
Once you add a data source, it is presented on the right-side bar. In the following image, we have used 4 xls files to import data – Sales Target, Branch Details, Daily Target, Sales
In Power BI on the left side of the screen, you have the following three tabs − Report Table View Model View
Cardinality Our fact and dimension tables connect via primary and alternative keys, but we must define their relationship further. Each relationship in a model is defined by a cardinality
Understanding the cardinality between tables is essential in Power BI data modeling, as it impacts how data is aggregated, filtered, and visualized in the report. THINGS TO KEEP IN MIND:- When doing data modeling in Power BI, keeping the following best practices and considerations in mind will help ensure your model is efficient, scalable, and easy to maintain: 🔑 Core Principles
1. Star Schema over Snowflake a. Prefer a Star Schema : fact tables in the center, surrounded by dimension tables. b. Avoid excessive normalization; flatten dimension tables where appropriate. 2. Avoid Bi-directional Relationships (unless necessary) a. Use single-directional filters from dimension to fact for performance and clarity. b. Only use bi-directional when the business case demands it (e.g., many-to-many relationships). 3. Use Proper Data Types a. Assign correct data types to columns. Avoid using text fields where numbers or dates are more appropriate. 4. Create a Date Table a. Always use a dedicated Date table (marked as Date table in Power BI) for time intelligence calculations. **⚙️ Performance Optimization
- Reduce Cardinality** a. Lower cardinality improves performance (especially for slicers and joins). b. Avoid unnecessary GUIDs, high-cardinality strings, or unique IDs unless needed. 6. Minimize Columns in Fact Tables
a. Keep fact tables slim – only include measures and keys. Remove unused or repetitive columns.
7. Use Measures Instead of Calculated Columns (when possible) a. Measures are calculated at query time and don't add size to the model; calculated columns increase model size. 8. Disable Auto Date/Time a. Turn off the Auto Date/Time feature (in Options > Data Load) to avoid unnecessary hidden tables. **🔑 Logical Design
- Naming Conventions** a. Use clear, user-friendly names for tables and columns. Avoid cryptic or technical names. 10. Hide Unnecessary Columns and Tables a. Hide keys and technical fields that users don’t need to see in visuals. 11. Maintain Consistent Granularity a. Ensure relationships are created between fields of the same granularity (e.g., don’t join month to date). 12. Be Careful with Duplicates and Nulls a. Dimension tables should ideally have unique, non-null keys. 📦 **Data Source & Load
- Use Import Mode (unless DirectQuery is required)** a. Import is faster and offers more features. Use DirectQuery only when real-time data is critical. 14. Use Power Query for Transformations a. Do heavy data shaping and cleanup in Power Query (M language), not in DAX. 15. Avoid Circular Dependencies a. Always validate your model to check for ambiguous or circular relationships.
