introduction
When i first heard about power BI,i thought it's all about making charts and dashboards.I came to realize even the most beautiful visuals can produce wrong numbers when it's not well organised and most beginners start questioning themselves why their totals are not adding up correctly.
Data modeling
Data modeling is organising and structuring your data so that it can be efficient and accurate for power BI to easily analyse it.
In simple terms,modeling is what separates a report that looks good from what is actually correct.You can think of it like a building plan.You cannot skip the plan and expect the building to go well.If you skip data modeling,you will get inaccurate report.
Joins
Joins in SQL are used to join tables with common columns.
1.inner join
The inner join joins common columns in two tables.It ignores data that does not match.Example:
select customer_name,age,
from customer,
inner join orders ,
oncustomers.customer_id=orders.customer_id;
2.outer join
Outer join in SQL returns all rows from both tables.It fills the missing columns with NUll where there is no match unlike inner join which does not include the columns.Example:
select customer_name,age,
from customers,
full outer join orders,
on customers.customer_id=orders.customer_id;
3.Right join
This joins all rows from the right table.It also includes everything from the left table that don't match as 'Null'.Example:
select customer_name,age,orders.orderid,
from customers,
right join orders,
on customers.customer_id=orders.customer_id;
If there is no match on the left table,the the data on the right will still appear.
4.left join
The left join keeps everything from the left.It can also join with data from the the right table that don't match as 'null'.Example:
select customer_name,age,orders.orderid
from customers
left join orders
on customers.customer_id=orders.order_id
5.right anti join
This returns only the rows from the right table that have NO match on the left table.
6.left anti join
This only returns the rows from the left table that have NO match on the right table.Example:
select customer_name,
from customers,
left join orders,
Power BI relationships
Power BI understands how tables get connected.You don't merge them like the joins in sql you basically conect them.When you conect two tables in power BI,that conection is called a relationship.
1.Cardinality
Cardinality describes how many rows on each table can merge.It helps you understand the structure of your data and how to join the tables perfectly.
a.1:1
This matches one-to-one where by each row in table A matches exactly one row in table B.
b.1:M
In this case,one row in table A matchs many rows in table B.They are most efficient and predictable in power BI and i recommend to always aim for 1:M
c.M:M
Multiple rows in table A can match multiple rows in table B.
This relationship can cause unexpected filter behaviour double counting and and you can resolve it by introducing a bridge table.
d.M:1
This relationship many rows in table A matches to 1 row in table B.
Active vs inactive
Tables can be connected in different ways when building data models
An active relationship is always the default and always on between two tables and appears as a solid line in model view.Every DAX and ever visuals in your report uses the active relationship automatically.
Inactive relationship is a stand-by connection ignored by default.You can create it only when you need a second or third path between two tables.
Schemas
Schemas simply is the structure of your data model.It shows how tables are arranged and connected to each other.You can think of it like a map to your data.
1.star schemas
This is the recommended standard for power BI.
It takes one flat table and splits it into separate,clean tables,one fact table in the middle sorrounded by dimension tables hence resembling a star.
2.Snowflake schema
This is basically like a star schema where some of the dimension tables are broken down further into smaller tables.
Conclusion
I used to create dashboards that had weird numbers and calculations that i couldn't even explain.Data modeling made power BI make sence to me and once the model was right and everything started to fall in place.Once you understand joins,relationshipsand schemas,your reports start telling accurate story and not just creating beautiful visuals.
Top comments (0)