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

29. Practice Exercises and Final Assignment

29.6 Modules 11–12: Data Modelling, Hierarchies and Groups

By Last updated:

  1. Classify each table (Orders, Product, Customer, DarkStore, DeliveryPartner, Date) as fact or dimension and justify.
  2. Draw the star schema on paper with cardinality and filter direction.
  3. Create the date table with CALENDAR and add Fiscal Year (April–March). Mark it as a date table.
  4. Why does Month Name sort alphabetically, and how do you fix it?
  5. Create the inactive Delivered Date relationship. How can a measure use it?
  6. Why should the Date table relate to Order Date and not to Order DateTime?
  7. Create Location (State › City › Area › Store Name), Date and Product hierarchies. Turn off Auto date/time.
  8. Create a Region list group (West, Vidarbha, North Maharashtra, Marathwada, South Maharashtra) with an Other group, and a hierarchy Region › City.
  9. Create 5-minute bins on Delivery Time Mins and ₹100 bins on Amount, and build histograms.

Practice files (fictional): dark_stores_maharashtra.csv, visuals_orders_apr_may_2026.csv

Hints
  • A fact records events (many rows); a dimension describes them.
Solutions (checked on the practice files)
  1. Facts: Orders. Dimensions: Product, Customer, DarkStore, DeliveryPartner, Date.
  2. Each dimension 1 → * Orders, single direction from dimension to fact.
  3. Fiscal Year = IF(MONTH([Date]) >= 4, YEAR([Date]), YEAR([Date]) - 1); 06-Apr-2026 → FY 2026-27. Mark as date table.
  4. Month Name is text; sort it by Month Number (or FY month number for April–March).
  5. USERELATIONSHIP(Orders[Delivered Date], 'Date'[Date]) inside CALCULATE.
  6. Order DateTime has times, so it would not match the Date table's whole dates.
  7. Location hierarchy example from dark_stores_maharashtra.csv: Region › City › Store. Turn off Auto date/time in Options.
  8. Region totals on the practice data: West Maharashtra ₹850, North Maharashtra ₹800, South Maharashtra ₹450.
  9. ₹100 bins on Amount: 0–99: 1 (308), 100–199: 1, 200–299: 2, 300–399: 2, 400–499: 2. 5-minute bins on delivery: 5–9: 3, 10–14: 3, 15–19: 1, blank: 1.

Ravindra Bagale's Tip

Friends, many students draw relationships until the visuals "look right" instead of checking cardinality and direction. For each relationship in the exercise, write down why it is one-to-many and single direction before you create it. Don't worry – after doing it two or three times, it becomes a habit.