Step 1: Understanding the Sample Data
We will use four datasets for practice:
1. Customers Dataset
Holds customer details.
• CustomerID – Unique ID for each customer (e.g., 101, 102, 103, 104)
• CustomerName – Names like John Smith, Emily Davis, Raj Patel, Maria Lopez
• Country – Customer countries of origin (e.g., USA, Canada, India, Mexico)
• Age – Customer ages as integers (e.g., 30, 27, 35, 29)
• Gender (Male/Female)
2. Orders Dataset
It holds information about customers' orders.
• OrderID – Unique Ordered number (e.g., 1001, 1002, 1003, 1004)
• CustomerID – Customer orders relate on this column (e.g., 101 for John Smith; 102 for Emily Davis)
• OrderDate – When the order was placed (e.g., 2024-02-15, 2024-02-18)
• Amount – Value of each order (e.g., 250.00, 100.00, 320.00, 150.00)
• Status – Three types: Shipped, Pending, Delivered
3. Products Dataset
It gives product-related information.
• ProductID – Unique product codes (e.g., P001, P002, P003, P004)
• ProductName – Laptop, Smartphone, Chair, Coffee Mug
• Category – Electronics, Furniture, Kitchen
• Price – 800.00 for a Laptop, 500.00 for a Smartphone, 120.00 for a Chair
4. Sales Region Dataset
Used to build sales locations in a hierarchy.
• Region – North America, Asia
• Country – USA, India
• State – California, Texas, Maharashtra, Karnataka
• City – Los Angeles, Dallas, Mumbai, Bangalore
Step 2 From Loading Data into Power BI
1. Launch Power BI and go to Home > Get Data.
2. Import all datasets selected for the file type (Excel, CSV, SQL, etc.).
3. Click Load to bring the data into Power BI.
Step 3: Creating Relationships in Power BI
Now let's link these datasets together.
How to create relationships
1. Open the model view in Power BI.
2. Drag and drop the CustomerID column from the Customers dataset to the Orders dataset-this will connect customers with orders-one to many.
3. Link the ProductID of the Products dataset to the Orders dataset-every order will be linked to a product.
4. Make sure the relationships have the right cardinality (one to many or many to one).
Best Practices for Relationships
• Always include a unique column (primary key) in one of the tables.
• To enable better interaction, set cross filter direction to Both.
• To significantly enhance performance, use star schema-a central fact table with several dimension tables.
Step 4: In Creating a Hierarchy in Power BI
Hierarchies create interactivity with reports by enabling users to drill down into detail.
Creating a Hierarchy
1. On Data View, select the Sales Region dataset.
2. Right-click on Region column and choose Create Hierarchy.
3. Drag Country, State and City into hierarchy.
4. Rename it "Sales Region Hierarchy".
Hierarchies provide:
• Better visualization, as one can drill down in different levels.
• Simple filtering because every detail can be reached on different granularity levels.
• A smoother navigation experience, as different users will not have to create their reports on different levels.
Step 5: Depiction of Relationship & Hierarchies
Having built up relationships and hierarchies, it is time to construct the interactive reports.
A. Sales Analysis Using Hierarchy
1. Go to Report View and add a Matrix visualization.
2. Place Sales Region Hierarchy into the rows.
3. Drag Amount from the Orders dataset into the values section.
4. Hit the drill-down button (⤓) to view sales by Region → Country → State → City.
B. Customer Orders Report
1. A Table visualization should be added containing:
o CustomerName
o OrderDate
o ProductName
o Amount
2. Use Slicers to filter: by Country or by Product Category.
Step 6 - Effective Practices in Power BI Data Modeling
• Unique Identifiers - CustomerID, OrderID, ProductID must all be different.
- Improve Performance: Simple joins and do not have too many tables joined together
- Star Schema: Helps in faster and more efficient querying.
- Set Cross-Filtering: Both direction for interactive reporting.
- Logical Hierarchies: Hierarchies should be kept in natural order (Region → Country→ State → City).
Conclusion
The essence of relations and hierarchies in Power BI is to build good reports. Relations bring together multiple sources of data, while hierarchies offer some order as to how people can look through the data.
Here, through this step-by-step exercise, we learned:
Connect Tables with the relationships (joining tables).
Create and use hierarchies.
Build interactive reports in Power BI.
With practice, a person gets good at structuring data in Power BI. Go ahead and do some more experiments
