Why Warehouse? Operational vs. Informational & the Four Defining Traits

Two kinds of database for two kinds of work

Back in Module 1 you met the split that drives this whole module: operational versus informational systems. It is worth re-stating crisply, because the exam tests the contrast directly.

- An operational system runs the business *in real time* off current data — order entry, registration, point-of-sale. It is the *system of record*, optimized for many small, fast read/write transactions with absolute accuracy at the moment each one commits. Its users are clerks and salespeople; its database is normalized for integrity. - An informational system supports decision-making off historical, consolidated data — trends, comparisons, ad-hoc analytical queries, data mining. Its users are managers and analysts; it is optimized for *flexible read access* over large volumes, not for transaction throughput.

These two have conflicting design goals. A schema normalized for fast, safe transactions is painful to query analytically (dozens of joins); a schema denormalized for fast analytics is unsafe for transactions. Run a heavy six-table aggregate against the live order-entry database and you starve the checkout line of performance. You cannot optimally serve both workloads from one database — and *that* is the foundational justification for building a separate data warehouse.

The four adjectives that define a data warehouse

The textbook definition is four adjectives long, and every one is load-bearing. A data warehouse is a subject-oriented, integrated, time-variant, nonupdateable collection of data used to support management decision-making.

- Subject-oriented — organized around the business's major *subjects* (CUSTOMER, PRODUCT, SALES), not around applications. An operational database is organized by application (the billing app's tables, the shipping app's tables); the warehouse re-organizes everything around the subjects managers ask about. - Integrated — data from many source systems are made consistent: one naming convention, one set of formats, one encoding, one unit. This is the *“one version of the truth.”* If three systems each store a customer's state as AB, Alberta, and 48, the warehouse reconciles them to a single representation. - Time-variant — every fact is tied to a specific time period, so the warehouse can answer *“how did this change over time?”* Operational data is a snapshot of *now*; warehouse data is a long series of snapshots. - Nonupdateable — end users cannot edit the data. It is *loaded and refreshed* by the ETL process (Lesson 9.4), never changed by interactive transactions. Data goes in via bulk load and is then read-only.

Worked example: the same fact, two ways

Consider a customer who moves from Alberta to British Columbia.

Operational (transient):   Customer 1007  state = BC      (old value 'AB' overwritten — gone)
Warehouse  (time-variant): Customer 1007  state='AB' valid 2019-01..2023-05
                           Customer 1007  state='BC' valid 2023-05..present

The operational system only needs *where the customer is now*, so it overwrites — that is transient data. The warehouse must keep *where they were when each past purchase happened* (so regional sales analysis stays correct), so it never deletes — that is periodic data, and it is why warehouses grow so large: every change adds a row instead of replacing one.

The business driver: an integrated view

Why do organizations build warehouses at all? Because the data needed to run the business is scattered across many operational systems that grew up independently and don't agree with each other. Managers need one integrated, company-wide view of information to make decisions — and no single operational system can provide it. That need for an integrated view is *the* driver the exam asks about.