Introduction
Last week I had the opportunity to learn a few key skills that are used in Power BI, to make stories from data. I learnt that you can load different types of data to Power BI such as excel, csv, parquet and you can also pull data from databases like postgresql. So what is power BI and what does it do?
Power BI is a business intelligence and data visualization platform. It takes the data collected and stored in different formats and which the use of DAX (power BI querying language), you can unify scattered financial, sales, operational data and track key performance indicators (KPI).
What is data modeling?
Data modeling is the process of identifying, organizing and defining the types of data a business collects and the relationships between them. For example a school system has a spreadsheet of classes, teachers and performance score. Individually they are just records but we can combine them to tell us Math, taught by John Doe is the best performing across different class streams.
Modeling involves:
- Creating relationships between tables
- Defining calculated columns and measures
- Optimizing data structure for performance
- Ensuring data accuracy and consistency.
What are Relationships in Power Bi?
Imagine you are the data analyst working for Naivas supermarket, with multiple branches all over the country, and each of the branches has its own database where they store important business data like the customer information, sales, and stock. Data from these different branches was collected, and you were asked to combine all this data together and generate insights from it. You will need to create a relationship between these different datasets so as to link them together before you start exploring the data.
To understand how rows in one table is related to another table, we need to discuss cardinality.
Cardinality specifies how the rows in one table are related to the rows in another table.
To create a relationship in Power BI, you need to link columns that contain the same information and the same column headers together eg Customer_id in the customer table with customer_id in the sales table.
The one-to-many cardinality relationship in Power BI is a connection between two tables in a data model where one unique value in the one table can be associated with multiple values in the many table. For example customers and products: each customer can make multiple purchases of a product, so there is a one to many relationship between them but each purchase(many) is associated with only one customer(one).
Many-to-one relationship: The many to one cardinality in PowerBI refers to a relationship between two tables where multiple records in one table can be associated with a single record in another table. For example in the sales table, one customer can be involved with multiple purchases over time. Multiple purchases associated with one customer.
One-to-one relationship: The one-to-one cardinality in PowerBI is a relationship setting that describes how two tables are related to each other.
Tables have rows that have unique identifies such as Primary Key (PK). Foreign Key (KY): links records across two tables together.
What are schemas?
A schema refers to the structure and organization of data within a data model. Schemas define how data is connected and related within the model, influencing the efficiency and performance of data queries and reports.
Understanding schemas helps in designing best data models that support comprehensive analysis
Types of Schemas in Power BI
1. STAR schema
The star schema consists of a central fact table surrounded by dimension tables, forming a star-like pattern.
The central fact table contains quantitative data(eg sales), while the dimension tables hold descriptive attributs related to the facts.(eg customers, products)
2. Snowflake Schema
Snowflake schema is a normalized version of the star schema. In this design, dimension tables are further divided into related tables, resulting in a more complex structure.
The normalization process eliminates redundancy by splitting dimension tables into multiplr related tables. This results in a web-like structure, resembling a snowflake.
Usage: Snowflake schemas are used in scenarios requiring detailed data models and efficient storage. They are beneficial when dealing with large datasets where data redundancy needs to be minimized.
Galaxies Schema(or fact constellation schema)
The galaxies schema involves multiple fact tables that share dimension tables, creating a complex, interconnected data model.
The schema consists of multiple fact tables linked to shared dimension tables, enabling the analysis of different business processes within a single model.
Usage: Galaxies schemas are suitable for large-scale enterprise environments where multiple related business processes need to be analyzed. They support complex queries and detailed reporting across various domains.
What are joins?
Power BI supports all major joins that are available in SQL. To perform joins, you need to access the Power Query Editor which is appears after clicking Transform data. The following join types are standard join types in Power BI and SQL.
Inner Join: Returns the rows present in both left and right table only if there is a match.
Joining the sales table to the customer table. The common column is customer_id.
The expected result after the join:
Full Outer Join: It returns all the rows present in both the left and right table.
Joining the sales table with the products table. The common column is the product_id
The expected result after the join:
Left Outer Join: It returns all the rows present in the Left table and matching rows from the right table(if any)
Joining the sales table with the store table. The common column is the store_id.
The expected result after a left outer join:
Right Outer Join: Returns matching rows from the left table(if any) and all the rows present in the SQL Right Table.
The expected result from the right outer join:
Anti Join: a filtering operation that returns only the rows from the first table(the left table) that do not have matching value in the second table(the right table).
The expected result from the left anti join:
Note: The null values observed after the join are a result of the absence of matching rows between the two tables.
Conclusion
Power BI is more than just a visualization tool; it is a complete business intelligence platform that enables organizations to transform raw data into meaningful insights. Understanding concepts such as data modeling, relationships, cardinality, schemas, and joins forms the foundation for building efficient and reliable reports.
By creating proper relationships between tables and designing models using schemas such as the star and snowflake schemas, analysts can ensure consistency, improve query performance, and simplify data analysis. Additionally, mastering joins in Power Query allows data from multiple sources to be combined effectively, making it possible to uncover patterns and answer business questions with confidence.
As I explored these concepts, I realized that creating dashboards is only one part of the process. The real value lies in designing a robust data model that enables accurate analysis and storytelling. This learning experience provided me with a deeper appreciation of how Power BI bridges the gap between raw data and informed decision-making, and it has given me a solid foundation for building more advanced reports and analytical solutions in the future.
Top comments (2)
Very informativeπ―
I've learnt alot here. very detailed