Data Warehouse and OLAP Technology for Data Mining

The four-layer architecture connecting operational sources, ETL, the warehouse, and the presentation layer used for reporting is covered in the lesson on business intelligence and data mining, and using mining output as a new warehouse dimension is covered with a worked example in the lesson on data mining for business intelligence. This lesson covers what specifically makes a warehouse a warehouse rather than just another database, and works through the OLAP operations — slice, dice, roll-up, and drill-down — with an actual data cube rather than naming them without demonstrating what they do.

Four Properties That Define a Data Warehouse

A data warehouse isn't simply "a big database" — it's a database built around four specific properties, first articulated by Bill Inmon, that a typical operational (transactional) database doesn't share:

• Subject-oriented — organized around business subjects (customers, products, sales) rather than around the specific applications that generate the data, so analysis can cut across every system that touches "customers" instead of being trapped inside one application's schema.
• Integrated — data arriving from different source systems, often with inconsistent formats and codes, is reconciled into one consistent representation during the ETL process, using exactly the kind of consistency check covered in the lesson on preprocessing in data mining.
• Time-variant — historical data is retained rather than overwritten, specifically so trends can be analyzed across time (this quarter vs. last quarter vs. the same quarter last year), which an operational database optimized for fast current-state lookups typically doesn't preserve.
• Non-volatile — once loaded, warehouse data isn't updated or deleted the way operational data constantly is; new data is appended, giving analysts a stable, unchanging basis for comparison rather than a moving target.

  OLAP Operations on an Actual Data Cube

  A data cube organizes warehouse data along multiple dimensions simultaneously — a small cube with Product, Region, and Time dimensions might hold sales figures like this:

                Q1      Q2
  Laptop, North:  100     120
  Laptop, South:   80      90
  Phone,  North:  200     210
  Phone,  South:  150     160

  Roll-up aggregates the cube to a coarser level along one dimension — collapsing Time from quarters up to a full year:

  Laptop, North, Year = 100 + 120 = 220
  Laptop, South, Year =  80 +  90 = 170
  Phone,  North, Year = 200 + 210 = 410
  Phone,  South, Year = 150 + 160 = 310

  Drill-down is the exact reverse — expanding Year = 220 for Laptop/North back into its underlying Q1 = 100 and Q2 = 120, recovering detail that roll-up had summarized away. Slice fixes one dimension to a single value and keeps the rest, producing a lower-dimensional view — fixing Region = North leaves a Product x Time view:

  Laptop: Q1=100, Q2=120
  Phone:  Q1=200, Q2=210

  Dice is a slice applied across more than one dimension at once — selecting Region = North and Product = Laptop narrows the cube down to just Q1=100, Q2=120, a sub-cube defined by multiple simultaneous conditions rather than a single fixed dimension. These four operations are what "interactive, multi-dimensional analysis" concretely means: an analyst can roll up to see the yearly picture, drill back down into whichever quarter looks unusual, then slice to a single region to investigate further — all without re-querying the underlying source systems each time, because the cube itself already holds every level of aggregation.

  Types of OLAP and Warehouse Implementation

  ROLAP stores the cube's data in ordinary relational tables and computes aggregations at query time — flexible and space-efficient, at the cost of slower queries on very large cubes. MOLAP pre-computes and stores the cube's aggregations directly in a multidimensional structure — much faster for the operations demonstrated above, at the cost of the storage needed to hold those precomputed aggregates. HOLAP combines the two, typically storing detailed data relationally while precomputing higher-level aggregations in a MOLAP structure. An Enterprise Data Warehouse serves an entire organization, while a Data Mart is a smaller, subject-specific slice of that same warehouse (just the sales department's data, for instance) — built for faster, more focused analysis at the cost of a narrower scope than the full warehouse provides.

  Where This Fits

  The warehouse is where cleaned, integrated, historical data lives; OLAP is how people interactively explore it; and data mining, covered throughout this series, is what runs algorithmically against that same warehouse to find patterns no analyst manually slicing and dicing would think to look for on their own — the three are complementary layers of the same overall business-intelligence architecture, covered end to end in the lesson on business intelligence and data mining.