Reporting, and the reporting stack

Why this matters

Every organisation runs on a small number of numbers that people trust: last month's revenue, this quarter's growth, how fast support tickets get resolved. When a leader glances at a dashboard and makes a decision, they are trusting a chain of work that starts in a database and ends in a coloured chart. If any link in that chain is wrong — the wrong rows counted, a filter forgotten, a stale refresh — the decision is made on a false number, and nobody in the meeting can see it.

This course is about building that chain so it holds. You will do the database end for real, in SQL, and you will design the dashboard end in Power BI. The single most valuable habit you can leave with is the one this first lesson introduces: always be able to say where a number came from and prove it against the source. A report you cannot reconcile to its source is a rumour with a chart on top.

Concepts

Reporting is not the same as analysis. *Exploratory analysis* is open-ended: you have a question, you slice the data many ways, you follow hunches, and most of what you produce is thrown away. *Reporting* is the opposite: a defined set of metrics, computed the same way every period, presented so that a non-analyst can read them at a glance and act. Analysis asks "what is going on?"; reporting answers "here are the agreed numbers, again, correctly." The skills overlap, but a report is a product with a contract: the same definitions, the same layout, refreshed on a schedule, trusted without re-checking. This course builds reports, so we care about clear metric definitions, repeatability and validation more than clever one-off queries.

The reporting stack is the set of layers a number passes through:

• Source system — where the data is created (an online store, a support desk). Not our concern here

except that it is the origin of truth.

• Data store / warehouse — a database organised for reading and reporting. Ours is PostgreSQL 16 holding

a star schema: fact tables (the events — sales lines, support tickets) surrounded by dimension tables (the descriptive context — date, customer, product, channel). You met stars, or will, in data modelling; lesson 6 shows how the same shape drives Power BI.

• Semantic / reporting layer — where raw tables become agreed metrics. In SQL this is views and

materialized views (lessons 2–4). In Power BI it is the data model plus DAX measures (lessons 6–7). Both exist to say, in one place, "revenue means *this*", so every visual agrees.

• Presentation layer — the report itself: visuals, layout, interactivity (lesson 8), refreshed and

published for an audience (lesson 9).

A healthy discipline is that the *definition* of a metric lives in the reporting layer, written once, not re-typed inside every chart. When you build the same metric twice you eventually build it two different ways, and then two dashboards disagree and nobody trusts either.

Where Power BI fits. Power BI Desktop is a free Microsoft application (Windows only) for building the reporting and presentation layers on top of a source such as our PostgreSQL database. It connects with Get Data, shapes data with Power Query, holds a data model of tables and relationships, calculates with DAX, and lays out visuals; a finished report is a .pbix file that can be published for others. Crucially, Power BI does not replace the SQL layer — it reads from it. Numbers you can define and prove in SQL are numbers you can trust in Power BI, which is exactly why this course teaches the SQL first.

Our data. You will work against meridian, a synthetic database for *Meridian Outfitters*, a fictional outdoor-gear retailer. It has two fact tables — fact_sales (one row per order line) and fact_support (one row per support ticket) — and four dimensions (dim_date, dim_customer, dim_product, dim_channel). The one rule that shapes every revenue number in the course: an order line has an order_status of completed, cancelled or returned, and recognised revenue counts completed lines only.

Guided exercise

The hands-on SQL work runs on your lab database db01. Connect and confirm the source before you build anything on it — step one of validation is knowing what you are connected to.

1. Connect as the read-only reporting role and confirm the version:

``bash psql -h localhost -U reporter -d meridian

``sql SELECT version();

``text PostgreSQL 16.15 (Ubuntu 16.15-0ubuntu0.24.04.1) on x86_64-pc-linux-gnu, compiled by gcc ... 64-bit

The text in brackets is the build the packager made; yours may differ. What matters to this course is the major version, 16 — every query, view and function used here is PostgreSQL 16 behaviour.

1. See the reporting star — one line per table:

``sql SELECT 'dim_channel' AS table, count(*) FROM sales.dim_channel UNION ALL SELECT 'dim_customer', count(*) FROM sales.dim_customer UNION ALL SELECT 'dim_date', count(*) FROM sales.dim_date UNION ALL SELECT 'dim_product', count(*) FROM sales.dim_product UNION ALL SELECT 'fact_sales', count(*) FROM sales.fact_sales UNION ALL SELECT 'fact_support', count(*) FROM sales.fact_support ORDER BY 1;

``text table | count --------------+------- dim_channel | 3 dim_customer | 800 dim_date | 731 dim_product | 54 fact_sales | 19130 fact_support | 2500

Two small fact tables and four dimensions — a textbook star. The whole course is built on these rows, and it only ever reads them: you write to your own reporting schema, never to sales. That is not just good manners — every reconciliation you do later compares your numbers with these tables, and a source that moved underneath you proves nothing.

1. Compute the one number every later report reconciles to:

``sql SELECT sum(net_amount) AS recognised_revenue FROM sales.fact_sales WHERE order_status = 'completed';

``text recognised_revenue -------------------- 3059067.95

Hold on to 3059067.95. In lesson 10 and the project your Power BI report's headline revenue must equal this to the cent, or the report is wrong.

1. (Documented — Power BI Desktop, on your own Windows machine if you want to follow along.) Power BI reaches this same database with Get Data → PostgreSQL database, then you enter the server and database name, choose Import or DirectQuery, and sign in with the Database (username/password) authentication kind. [SCREENSHOT: Power BI Desktop Get Data dialog with "PostgreSQL database" selected, then the connection dialog with Server and Database fields] You will do this properly in lesson 5; for now just note that the box you type the server into is the top of the presentation layer, and the number you just computed in SQL is the bottom.

Troubleshooting

Symptom → psql: error: connection to server ... failed → cause: the database service is not up or the host/port is wrong → confirm the lab machine db01 is running and that you used -h localhost.

Symptom → FATAL: password authentication failed for user "reporter" → cause: wrong or unset password → use the password on your lab card; the reporting role is reporter, not your Linux user.

Symptom → permission denied for table fact_sales on an UPDATE → cause: reporter is read-only by design → reporting never modifies the source; if you need to store something, create a view in your own reporting schema (lesson 4).

Symptom → "Power BI cannot find the PostgreSQL connector" → cause: on very old Power BI Desktop builds the Npgsql provider was a separate install → the provider has shipped inside Power BI Desktop since December 2019; update Power BI Desktop rather than hunting for a driver.

Check your understanding

Take the chapter quiz for this chapter now.

Summary and next step

• Reporting delivers agreed metrics repeatably and provably; analysis explores. This course builds reports.
• The stack runs source → data store → reporting/semantic layer → presentation; metric definitions belong in

the reporting layer, written once.

• You confirmed the SQL source (PostgreSQL 16, the meridian star) and the headline number every later report

must reconcile to: 3059067.95.

Next, in *SQL aggregations for dashboards*, you turn those raw tables into the grouped, filtered results a dashboard actually shows — and build the base view the rest of the course reads.

References

• PostgreSQL 16 Documentation — https://www.postgresql.org/docs/16/index.html (accessed 2026-09-13)
• Power BI: Get data in Power BI Desktop — https://learn.microsoft.com/power-bi/connect-data/desktop-data-sources (accessed 2026-09-13)
• Power Query: PostgreSQL connector — https://learn.microsoft.com/power-query/connectors/postgresql (accessed 2026-09-13)
• Power BI: Star schema and the importance for Power BI — https://learn.microsoft.com/power-bi/guidance/star-schema (accessed 2026-09-13)