1.What is data modeling?
Data modeling is the process of structuring and organizing Data to make is easily accessible for reporting in power BI. It involves connecting tables, defining relationships, cleaning and shaping data.
Organizes data into a structured format.
2.SQL Joins in Power BI (power query)
Powe BI enables SQL like joins via merge queries to create a physical table. These are essential for data cleaning. Combine tables based on specific requirements.
INNER JOIN: Keeps only rows that have matching values in both tables.
Example: joining employee table with department table. Only employees assigned to a valid department are returned.
LEFT JOIN: Keeps all rows from the left table and matches from the right. No match it returns null.
Example; Joining sales table with product table. All sales products are kept and product details added and id a sale was made for a product not in the product table, the product name will be null.
RIGHT JOIN: Keeps all rows from the right table and matches from the left.
Example; Join customer table with sales. All sales are kept and if a sale exists with no corresponding customer, then it is replaced with null.
FULL OUTER JOIN: Keeps all rows from both tables matching where possible, filling gaps with nulls.
LEFT ANTI JOIN: Keeps rows from the left table that don’t exist in the right.
RIGHT ANTI JOIN: Keeps rows from the right that don’t exist in the left.
3.Power BI Relationships
Connects tables allowing them to communicate without physically merging them.
Cardinality: Describes the nature of relationships.
One to Many (1:M): One row relates to many rows.
Many to Many (M:M): Both sides have duplicates.
One to One (1:1): Matches unique rows. Used when splitting large tables.
Active vs Inactive relationships: Only one can be active (solid line). Use DAX (USERELATIONSHIP) for the inactive (dotted line).
Cross Filter Directions: controls filters.
Single: Dimension filters the fact table.
Both: Both tables filter each other.
4.Difference Between Joins and Relationships
Joins merge tables during data loading permanently while Relationships keep the tables separate but connected for dynamic filtering based on visuals.
Joins are used for data cleaning while Relationships are used for reporting and slicing.
Joins are created in power query while relationships are created in model view in power BI.
5.Facts vs Dimension tables
Fact tables: Contain quantitative, numeric data e.g. sales. Have many rows
Dimension table: Contains descriptive, categorical context e.g. “who”, “why”, “what”, product name. Have many columns.
6.Data Schemas
Star schema: Central fact table connected to dimension tables.
Example; A sales fact table linked to product table, customer table.
Snowflake schema: Dimension tables are split into multiple tables, normalizing the data.
Used when dealing with highly hierarchical data (complex product catalogs).
Flat table (DLAT): All data in one table. Simple but slow and inefficient. No joins needed.
7.Role Playing Dimensions
A single dimension serving multiple roles in the fact table.
Example; date dimension used for both harvest and planting dates.
8.Common Data Modeling Issues
Circular relationships between tables
Mismatched keys in merges that leads to missing data
Ignoring inactive relationships leads to incorrect totals
Doing too much transformation in DAX instead of power query
Poorly defined entities.
9.Step by Step Power BI Workflow
Get data: Open power BI desktop and use “get data” to connect to various sources.
Transform data to open and clean data in power query editor
Model data (data view): Switch to model view to establish relationships between tables.
Define: Create necessary DAX measures. Use DAX to build calculated columns and measures (e.g. SUM, AVERAGE, CALCULATE).
Visualize data (Report view): Build interactive reports by dragging fields onto the canvas, selecting chart types and formatting visuals.
Top comments (0)