Data Integration and Visualisation

UsefulUseful analysis rarely starts from one clean table — it starts from several messy, differently-structured sources that have to be combined before any question can be asked of them, and ends with a result that has to be shown to a person, not just computed. Integration and visualisation are the two halves of that journey: getting data into one coherent shape, and getting insight back out of it.

ETL versus ELT

ETLETL (Extract, Transform, Load) pulls data from its sources, cleans and reshapes it in a separate processing step, and only then loads the finished result into its destination — the transformation happens before the data ever reaches its final home, which keeps that destination clean but means every transformation has to be anticipated up front. ELT (Extract, Load, Transform) loads the raw data in first and transforms it afterward, inside the destination system itself — practical now that modern data warehouses have enough compute power to do heavy transformation work themselves, and it keeps the original raw data available for a different transformation later, without needing to re-extract it from the source all over again.

Star Schemas and Data Warehouses

AA data warehouse is typically organised differently from an operational database — where Relational Modelling's normalisation actively minimises redundancy for fast, safe transactional updates, a warehouse's star schema deliberately denormalises for fast analytical queries instead: a central fact table (one row per event — a sale, a click) surrounded by dimension tables (descriptive context — the product, the customer, the date) that the fact table references — the dimensional modelling approach Kimball and Ross's standard reference on data warehouse design sets out in full, including the date-dimension pattern covered next¹.

The DIM_DATE table above is a common, deliberate piece of star-schema design worth calling out on its own: rather than storing a raw date and computing "which financial quarter was this?" or "was this a public holiday?" freshly on every query, a dedicated date dimension precomputes and stores those derived attributes once, as ordinary columns to join against — trading a small amount of redundant storage for analytical queries that are both faster and simpler to write. A data cube extends the same star-schema idea to more than two dimensions at once, letting an analyst "slice" (fix one dimension, view the rest) or "drill down" (move from a coarse dimension value, like a year, to finer ones, like the months inside it) across sales by product, by region, and by time simultaneously, without needing a separately designed table for every combination someone might eventually ask about.

Visualisation: Exploration versus Explanation

AA chart built to explore data (quickly scanning a dataset for patterns, outliers, or errors, usually seen by nobody but the analyst) and a chart built to explain a finding (shown to an audience who needs to understand one specific point quickly) are different tools solving different problems, even when they're drawn from the same underlying data. An exploratory chart can be rough and can show everything; an explanatory chart needs a single clear message and has to actively remove everything that doesn't serve it.

Tufte's Design Principles

EdwardEdward Tufte's foundational work on statistical graphics gives the clearest vocabulary for that removal². The data-ink ratio — the proportion of a chart's ink that actually represents data, versus decoration, gridlines, borders, and other non-data ink — is Tufte's central, practical metric: maximise it by removing every mark that isn't doing real communicative work. Chartjunk is Tufte's name for exactly the decoration that metric argues against — 3D bar charts that distort the reader's ability to compare heights accurately, heavy gridlines that compete visually with the data they're meant to support, decorative backgrounds that add nothing but visual noise. Neither principle argues for a bare, minimal chart as an aesthetic end in itself — the goal is a chart where every mark earns its place by conveying something a reader actually needs.

Building an Interactive Dashboard

AA dashboard applies both ideas above at once, and adds a further discipline: showing several related charts together without letting them compete for attention. The practical guidance that follows from data-ink and chartjunk: lead with the metric that matters most, sized and positioned to be seen first; use consistent colour encoding for the same category across every chart on the dashboard, so a reader learns the colour key once rather than per-panel; and resist the temptation to show everything available just because it's technically possible to fit it on screen — a dashboard answering ten questions poorly is less useful than one answering the three questions that are actually being asked, clearly.

References