[Ep 6] PHASE 3: Creating Relationships

Where the model starts to think.

Once the tables were in Power BI, the next phase was about relationships.

Relationships are the backbone of any good dashboard.

They let data across tables connect — unlocking layered insights and smarter questions.

And like any good system, it started with rules.

Rule 1: Every table needs a key.

I began by identifying the anchors.  

• customer_id linked people to their purchases.

• transaction_id tied receipts to each line item.

• campaign_id connected promotions to their impact.

Each key was a thread. Relationships were how I stitched them into a working model.

Table	Purpose	Primary Key
customer_profiles	Master data about each customer	customer_id
product_catalog	Details about products sold	product_id
sales_detail	Transactional fact table (item-level)	transaction_id
marketing_campaigns	Metadata about promotions and offers	campaign_id + derived campaign_key
store_locations	Info about each location — size, region, etc.	store_id
fiscal_calendar	Centralized date table for time intelligence (week, month, etc.)	date

Rule 2: One side always leads.

In relationships, direction matters.

Some tables describe, others record.

A customer may have many transactions, but not vice versa.

Power BI needs to know this — or else risk broken filters and misleading numbers.

💡 Most of the time, sales_detail is your fact table — the core that other dimensions connect to.

Key Relationships:

1. sales_detail → customer_profiles

• Relationship: Many-to-One

• Foreign Key: sales_detail.customer_id = customer_profiles.customer_id

• Use Case: Analyze sales by loyalty tier, acquisition channel, or join date.

2. sales_detail → product_catalog

• Relationship: Many-to-One

• Foreign Key: sales_detail.product_id = product_catalog.product_id

• Use Case: Segment performance by product line or category.

3. sales_detail → marketing_campaigns

There’s no single matching column, so we combine promo_type_used and fiscal_year to create a shared composite key: Campaign_Key.

Method:

1. In Power BI → Go to Power Query Editor (Transform Data)

2. Create Campaign_Key in sales_detail

• Select the table sales_detail_cl

• Make sure both promo_type_used and Fiscal Year are visible

• Go to the Add Column tab → Click Custom Column

• Name: Campaign_Key

• Formula:

[promo_type_used] & "_" & Text.From([Fiscal Year])

• Click OK

• Make sure the new column’s type is Text

3. Create the same Campaign_Key  in marketing_campaigns

• Use the same method to create the column

[promo_type_used] & "_" & Text.From([Fiscal Year])

4. Create the Relationship

• Link the two tables using Campaign_Key

• If Power BI says it’s a many-to-many relationship, check marketing_campaigns for duplicate rows and remove them if needed

4. sales_detail → store_locations

• Relationship: Many-to-One

• Foreign Key: sales_detail.store_id = store_locations.store_id

• Use Case: Track regional performance or test store segmentation.

Rule 3: Time needs its own table.

Behind every period-over-period insight — revenue trends, campaign timing, seasonality — all of it depends on the invisible backbone: a clean, centralized, proper Date Table.

Not the scattered timestamps buried in your sales data.

If you used my Gemini or Python prompt, your fiscal_calendar.csv is that Date Table..

Start Date	End Date	Sample Columns
Jan 1, 2023	July 31, 2025	Year, Month, Week, Fiscal Periods, Holidays

If you don’t have one, you can build it in Power BI with this DAX formula:

Date = 
ADDCOLUMNS(
    CALENDAR(DATE(2023,1,1), DATE(2025,7,31)),
    "Year", YEAR([Date]),
    "Month", FORMAT([Date], "MMMM"),
    "MonthNumber", MONTH([Date]),
    "Weekday", FORMAT([Date], "dddd"),
    "FiscalYear", IF(MONTH([Date]) >= 4, YEAR([Date]), YEAR([Date])-1)
)

Next, we need to "Mark as Date Table".

1. Go to the Modeling tab in Power BI

2. Select your Date Table

3. Click Mark as Date Table

4. Choose the [Date] column → Confirm

Then, relate Date[Date] → sales_detail[transaction_date]

Set as One-to-Many, single filter direction.

Rule 4: Relationships should feel right.

Last step? Test everything.

I sliced by customer tiers. Drilled down into margin by product. Compared promo vs. non-promo weeks. With every chart, I asked:

Does this number make sense?Does the model behave like the business?

Creating relationships isn’t just technical.

It was the moment the model began to think.

When raw tables transformed into usable intelligence.

When clicks turn into questions, and data finally starts to speak.

The Final Relationships

---

Next: [Ep 7] PHASE 4: Funnel Analysis. This is where I dive deeper, chase the “why” behind the numbers, and reveal the hidden logic in the data.

---

💡 Part of my Data × Design Series — follow the journey from raw interview challenge to polished dashboards.

Table of Contents

1. The Interview That Sparked It

2. I Didn’t Get the Job. So I Built the Job.

3. Designing My Own Dataset (Gemini + Python)

4. PHASE 1: Cleaning Data in SQL

5. PHASE 2: Modeling in Power BI

6. PHASE 3: Creating Relationships

7. PHASE 4: Funnel Analysis

8. PHASE 5: The Story — Visualizing the Insights