Model facts and dimensions deliberately — Smart Study Module 14

A report can show correct grand totals while giving wrong branch slices if relationships are ambiguous.

Outcomes

Understand

Use Branches → Loans → Payments and Agents → Loans with one-to-many, single-direction filters. Calendar → Payments uses payment_date; Branches → Targets and Calendar → Targets use branch_id and month_end. Avoid adding a second branch-to-payment route.

Targets are branch-month facts recorded at month-end. A whole-month filter includes that month-end. A single-day filter usually does not; label target KPIs as month-level or suppress them at day grain.

Loans contains one January-end snapshot. Its POS is not additive over months. A loan snapshot trend needs dated historical snapshot facts; do not invent them from payment events.

Follow along

  1. Confirm uniqueness of Branches[branch_id], Loans[loan_id], Agents[agent_id] and Calendar[date].
  2. Create the specified single-direction relationships.
  3. Filter Nashik and check January collection ₹21,000.
  4. Verify POS still totals ₹1,000,000 and label it 31 January snapshot.

Worked example

Branches[branch_id] 1 → * Loans[branch_id]
Loans[loan_id] 1 → * Payments[loan_id]
Calendar[date] 1 → * Payments[payment_date]
Branches[branch_id] 1 → * Targets[branch_id]
Calendar[date] 1 → * Targets[month_end]

Nashik snapshot POS=500000; all branches snapshot POS=1000000.

Practice

Enter Nashik snapshot POS.

Create a free personalized study plan · Download project instructions

Interactive practice runs in your browser.