When Do Your Reports Need a Data Warehouse?

Signs your business reports have outgrown the transactional database, and how to move step by step from a read replica to a data warehouse.

Every business application starts by reporting from its own database. That is the right choice at the beginning. At some point the reports get slow, the numbers stop matching between departments and someone suggests a data warehouse. Here is how I judge whether it is time, and how to get there without a large project.

Two kinds of work

A transactional system, often called OLTP, handles many small operations: save an order, update a balance. An analytical workload, or OLAP, asks few but heavy questions: total sales by product by month for three years. The designs that make one fast make the other slow, which is why they are eventually separated.

Signs you have outgrown operational reporting

• Month-end reports slow down the people entering transactions.
• Reports need data from several systems, such as the ERP, the sales app and spreadsheets.
• Two departments present different figures for the same measure.
• Managers ask how a number looked last quarter, and the system only knows today.
• Every new report needs a developer to write a long query.

Step 1: a read replica

The cheapest first move is a replica of the production database used only for reports. It removes the load from the transactional system in a day. It does not fix slow queries or mismatched definitions, but it buys time.

Step 2: summary tables

Next, stop recalculating the same totals. A nightly job writes daily sales by product, territory and distributor into a summary table, and reports read from it. Most management reports are satisfied by a handful of such tables. I describe the query side in MySQL query optimization for reports.

Step 3: a warehouse with a star schema

When several sources and many reports are involved, build a separate database designed for analysis. The common design is a star schema: a central fact table of measurable events, surrounded by dimension tables that describe them.

fact_sales
  date_key, product_key, distributor_key, territory_key,
  quantity, gross_amount, discount_amount, net_amount

dim_date         date_key, date, month, quarter, year, is_holiday
dim_product      product_key, code, name, category, brand
dim_distributor  distributor_key, code, name, type, region
dim_territory    territory_key, name, area, zone

Queries against this shape are simple and fast: filter by dimensions, sum the facts. Business users and reporting tools can work with it without knowing the source systems.

Loading the data

A scheduled process extracts new and changed rows from each source, cleans and maps them to shared codes, and loads them into the warehouse. Load incrementally, make each run safe to repeat and log row counts so you can prove the warehouse matches the source. Reconciliation with the source is what earns users' trust.

Keep history deliberately

If a distributor moves to another territory, should last year's sales move too? Usually not. Decide for each dimension whether to keep the old value with validity dates, so historical reports remain as they were.

Definitions come before technology

The hardest part is agreeing what each measure means. A warehouse built on unclear definitions only produces wrong numbers faster. Settle them first, write them down and reuse them everywhere, including in newer tools such as natural-language sales analytics.