Ravindra BagaleCourses & study guides मराठी Track your progress

11. Data Modelling

11.8 Modelling Best Practices

By Last updated:

Start with a problem

Rani inherits a slow model: Autodetect relationships created odd links, Auto date/time is on, IDs are summed, and unused columns are loaded.

Explain simply

A short checklist keeps models small, fast and hard to misuse: star schema, single-direction one-to-many, one marked Date table, hidden keys, no unused columns, measures for aggregation, friendly names and formats.

Show the data

Check Before After
Schema One flat table Star: Orders + DarkStore + Date
Auto date/time On (hidden date table per date column) Off; one marked Date table
DarkStore columns 7 (incl. Latitude, Longitude, Manager Email) 4 needed (Store, City, Region, Manager)
Order ID Summed Summarize by None

About this data: This table uses columns: Check, Before, After. This table uses columns: Measure, Value. This table uses columns: Wrong result, Reason, Correction. Values shown are the lesson examples already on this page (not a new dataset).

Fictional sample data. Practice file: visuals_orders_apr_may_2026.csv, dark_stores_maharashtra.csv (source: src/practice/).

Demonstrate

  1. Turn off Autodetect new relationships and Auto date/time (File › Options › Current file › Data load).

  2. Rebuild as a star: DarkStore 1 → * Orders, Date 1 → * Orders, both single direction.

    Your result should look like this:

    Relationships
    2

  3. Power Query › DarkStore › remove columns not used by any visual (keep Latitude/Longitude only if you build a map, Manager Email only if RLS needs it).

    Your result should look like this:

    DarkStore columns
    Store, City, Region, Manager

  4. Hide Orders[Store] and Orders[Order Date]; users filter with DarkStore and Date.

  5. Replace calculated-column totals with measures; put them in display folders with formats.

Show the result

Your result should look like this (fictional sample):

Measure Value
Total Sales ₹2,100
Total Orders 8
AOV (delivered) ₹300
Avg Delivery (min) 11.0
Cancellation % 12.5%

Explain what happened

The numbers stay the same; only the model is cleaner. Fewer columns and no hidden date tables make the file smaller, and single-direction relationships keep filter paths predictable.

Explore further

  • Use integer keys where possible; long text keys compress poorly.

Detailed reference for this lesson:

  1. Use a star schema: facts in the centre, dimensions around.
  2. Use one-to-many, single-direction relationships wherever possible.
  3. Always build a dedicated, marked date table and turn off Auto date/time.
  4. Hide foreign keys in the fact table (users should filter using the dimension columns).
  5. Remove unused columns and rows in Power Query – smaller model, faster report.
  6. Use integer keys and correct data types; avoid high-cardinality text keys where possible.
  7. Prefer measures over calculated columns for aggregations.
  8. Give tables and columns business-friendly names (Customer Name, not cust_nm).
  9. Set Summarize by = None for numeric attributes like Year and IDs.
  10. Keep a consistent number format and put measures in display folders with descriptions.

Mini-exercise

Load the Blinkit tables. Build the star schema (रचना), create the DAX date table above, mark it as a date table, set Month Name to sort by Month Number, create the inactive Delivered Date relationship, and hide all ID columns in Orders.

Ravindra Bagale's Tip

Friends, many students let Autodetect relationships create links on columns with similar names, like "ID" to "ID". Turn it off for new projects (File › Options › Data Load) and create each relationship deliberately. Review Model view after every new table. Clear?

Fix common mistakes

Wrong result Reason Correction
A strange ID ↔ ID relationship appears Autodetect relationships Turn it off; create each relationship yourself
The file is large for a small dataset Auto date/time and unused columns Turn it off; remove columns
The same logic sits in three calculated columns Columns used for aggregation Use measures

Practise independently

Practice task

Count the relationships in the star model built in this module (Orders, DarkStore, Date) and give their direction.

Hints
  • One per dimension.
Solution

2: DarkStore[Store] 1 → * Orders[Store] and Date[Date] 1 → * Orders[Order Date], both single direction. Total Sales stays ₹2,100.

Think like a professional

Keep a one-page model checklist in the project folder and tick it before every publish.