Define a measure once and make every report use it

capstone · 75 min · Objective 1.4

Task

The domain 1 capstone. Define "net revenue" once, as a view in a copy of the database, build two different reports on top of it, document the measure, and prove the reports agree with each other and with an independent calculation. This is what a semantic layer does, built by hand.

Steps

  1. Copy data/shop.db to lab/governed/shop.db and work only on the copy.
  2. Create a view net_sales returning order_id, order_date, region, category and net_amount, where net_amount is quantity times unit price times (1 minus discount_pct / 100), joining the three tables.
  3. Build report A, net revenue by region, into lab/governed/by-region.csv with header region,net; and report B, net revenue by month, into lab/governed/by-month.csv with header month,net. Both must read from the view, not the tables.
  4. Write lab/governed/definition.md stating the measure's formula, its source tables, what it includes and excludes, and its owner.
  5. Record the grand total of each report in lab/governed/totals.txt as by region: N and by month: N, rounded to two decimal places.

Verify

These checks run in a POSIX shell with Python 3: Terminal on macOS or Linux, and on Windows Git Bash (it comes with Git for Windows) or WSL. On Windows, python3 is often a Microsoft Store placeholder that prints “Python was not found”; run alias python3=py in Git Bash first.

python3 -c "import sqlite3; c=sqlite3.connect('lab/governed/shop.db'); print(c.execute('SELECT COUNT(*) FROM sqlite_master WHERE type = ?', ('view',)).fetchone()[0], 'view(s)')"
grep -E '^by (region|month): ' lab/governed/totals.txt
awk -F': ' '{print $2}' lab/governed/totals.txt | sort -u | wc -l
awk -F, 'NR>1 {n++} END {print n" months"}' lab/governed/by-month.csv
grep -ciE 'formula|includes|excludes|owner' lab/governed/definition.md

One view, two reports whose totals are both 353816.74 -- one distinct value, so they agree -- twelve months, and a definition that states the formula, the inclusions and exclusions and an owner. Two reports that each calculated revenue their own way could have disagreed without either being wrong; two reports reading one definition cannot.

Notes

The same idea at scale is a certified data set or a BI tool's semantic model: the measure is defined in one governed place and every report inherits it.

This is an independent study companion for CompTIA Data+ DA0-002 and is not produced by or endorsed by CompTIA.