What Is a Data Warehouse

What Is a Data Warehouse?

A Data Warehouse is a large-scale data storage system specifically designed for analytics and reporting — not for recording daily transactions.

The concept was introduced by Bill Inmon in the 1990s, who defined a Data Warehouse as:

"A subject-oriented, integrated, non-volatile, and time-variant collection of data in support of management's decisions."

- Bill Inmon, Father of the Data Warehouse

In simple terms, a Data Warehouse has 4 key characteristics:

Characteristic	Meaning	Example in Government Context
Subject-Oriented	Data is organized by analytical subject, not by the system that generated it	"Budget disbursement" data from all departments and programs consolidated in one place
Integrated	Combines data from multiple systems into a unified format	Data from ERP + GFMIS + e-GP + HR system consolidated into a single format
Non-Volatile	Once data is loaded, it is never modified or deleted — preserved as historical record	Fiscal year 2025 disbursement data remains unchanged, even if adjustments are made in the current fiscal year
Time-Variant	Stores data with time dimension, enabling cross-fiscal-year comparisons	Compare capital expenditure disbursement results over the past 5 years to see which years met targets

How Does a Data Warehouse Differ from an ERP Database?

Many agencies wonder, "We already have a database in our ERP system — why do we need a Data Warehouse?" — The answer is that they are designed for entirely different purposes:

Aspect	OLTP Database (in ERP)	Data Warehouse (OLAP)
Purpose	Record daily transactions (disbursements, procurement, goods receipt)	In-depth data analysis, executive reporting
Data Characteristics	Current data, constantly changing	Historical data accumulated over multiple fiscal years
Query Pattern	Read-write one record at a time, optimized for speed	Read large volumes, aggregate across tables
Schema Design	Normalized (3NF) — reduces redundancy, optimized for writes	Denormalized (Star/Snowflake Schema) — optimized for fast reads
Users	Operational staff (finance, inventory, accounting)	Executives, planning & policy offices, State Audit Office, analytics teams
Example Query	"Has purchase order PO-2026-001 been approved yet?"	"How does the Q1 FY2026 capital expenditure disbursement rate compare to the past 3 years?"

Core Components of a Data Warehouse

A Data Warehouse is not just "another database" — it is an entire system architecture consisting of:

1. Data Sources

For government agencies, data sources typically come from multiple systems:

• ERP System — accounting, procurement, inventory, budget, and HR data
• GFMIS — Government Fiscal Management Information System
• e-GP — Electronic Government Procurement system
• HR / Document Management Systems — personnel and document data
• External Data — KPI indicators from the Bureau of the Budget, OPDC, and State Audit Office

2. ETL Process (Extract, Transform, Load)

This is the heart of the Data Warehouse — the process of extracting data from various sources, transforming it into a unified format, and loading it into the warehouse:

Step	What It Does	Government Example
Extract	Pull data from source systems	Extract disbursement data from ERP and GFMIS nightly at 2:00 AM
Transform	Clean, reformat, and compute derived values	Harmonize agency codes, calculate disbursement % against targets, group by program/output
Load	Load transformed data into the Data Warehouse	Insert new disbursement records into the Fact_Budget_Execution table daily

3. Data Warehouse Storage

The core storage layer, designed with schemas optimized for analytics:

• Star Schema — A central Fact table (numeric data such as disbursement amounts) surrounded by Dimension tables (e.g., fiscal year, agency, program)
• Snowflake Schema — Similar to Star but with further normalized Dimensions, suitable for complex hierarchies

4. BI Tools (Analytics Tools)

Tools that query Data Warehouse data to build dashboards and reports:

• Apache Superset — Free and open source, ideal for agencies looking to reduce licensing costs
• Metabase — Open source, user-friendly, create reports without writing SQL
• Power BI — From Microsoft, integrates easily with various systems
• Tableau — Creates beautiful visualizations, ideal for executive presentations

Star Schema — The Most Popular Design

Star Schema is the most widely used Data Warehouse design pattern because it is easy to understand and fast to query. It consists of 2 components:

Table Type	Function	Government Example
Fact Table	Stores measurable values (Measures) such as disbursement amounts and contract counts	Fact_Budget_Execution: date, program_id, agency_id, disbursement_amount, allocated_budget
Dimension Table	Stores contextual data used for filtering and grouping	Dim_Agency: agency_id, division/bureau_name, parent_ministry

With this schema, executive questions can be answered instantly, such as:

• "What percentage of Q1 capital budget did Department A disburse vs. the Bureau of the Budget target?"
• "Which project had the highest procurement value in fiscal year 2026?"
• "What are the operating budget disbursement rates for all departments, ranked highest to lowest?"
• "Compare capital expenditure disbursements over the past 3 years, broken down by quarter"

Data Warehouse vs. Data Lake — What Is the Difference?

Another term often heard alongside it is Data Lake — both are large-scale data stores, but they differ:

Aspect	Data Warehouse	Data Lake
Data Stored	Structured (well-defined schema)	Structured + Semi-structured + Unstructured
Schema Approach	Schema-on-Write (define schema before loading)	Schema-on-Read (define schema at query time)
Data Quality	High — cleaned through ETL processing	Varies — raw, untransformed data
Best For	Executive reports, dashboards, internal auditing	Machine Learning, deep analysis, raw document storage
Cost	Higher (requires Schema + ETL design)	Lower (store raw data first, transform later)

For government agencies that need executive reporting and State Audit Office compliance — a Data Warehouse is the more targeted choice, as data is organized, quality-assured, and ready to use immediately.

Why Should Government Agencies Have a Data Warehouse?

1. Real-time Executive Reports Across Departments and Programs

Director-General or Deputy Director-General level executives need to see the big picture across the entire agency — not just one department's data. But in ERP, each module generates separate reports. To see "all departments' disbursements + procurement status + workforce + remaining budget on a single screen" — a Data Warehouse is essential.

2. Support KPIs and Indicators from the Bureau of the Budget / OPDC

Many government KPIs relate to budget disbursement rates for both operating and capital expenditures — a Data Warehouse can calculate these indicators automatically, without waiting for staff to manually compile data from multiple sources.

3. Analyze Trends Across Multiple Fiscal Years

ERP systems typically store current and previous fiscal year data — but a Data Warehouse stores many years of historical data for trend analysis:

• Are capital expenditure disbursement rates improving or declining each year?
• Which types of projects are most frequently delayed?
• Which expenditure categories have the highest unspent funds returned every year?
• What is the trend of e-bidding procurement versus specific method procurement?

4. Support State Audit Office Audits

A Data Warehouse stores data as non-volatile — once loaded, data is never modified, with a complete audit trail. This enables agencies to quickly retrieve historical data when the State Audit Office or internal auditors need to review records.

5. Reduce ERP System Load

Running large analytical queries for summary reports will slow down the ERP system because the database is under heavy load — a Data Warehouse solves this by offloading analytical workloads to a separate system, without affecting staff's daily operations.

Real-World Data Warehouse Use Cases in Government

Scenario	Without Data Warehouse	With Data Warehouse
Executive asks "What is our capital expenditure disbursement rate?"	Staff must pull data from ERP + GFMIS into Excel — takes 1-2 days	Open the dashboard and see instantly — by department, program, and quarter
State Audit Office requests 3 years of procurement data	Must search across multiple systems and Excel files — may take weeks	Query the Data Warehouse instantly — complete, accurate, and auditable data
Preparing next year's budget request	Can only view 1-2 years of historical data, lacking supporting evidence	Analyze 5-year spending trends by program/activity to build data-backed budget requests
Tracking strategic plan performance	Each department reports in different formats — cannot be consolidated	Dashboard shows real-time performance vs. targets for all departments and indicators

How to Get Started with a Data Warehouse — For Government Agencies

1. Start with policy questions first

   Don't start with "We want a Data Warehouse" — start with "What questions do executives need answered?" such as disbursement rates by quarter, overall procurement status, and remaining budget by program.

2. Verify data quality in the ERP system

   A Data Warehouse is only as good as its source data — if ERP data is incomplete, inaccurate, or inconsistent (e.g., some departments haven't entered all records, some entries are miscategorized), fix this first.

3. Choose tools appropriate for your agency

   For government agencies concerned with software licensing, suitable open-source options include:

   • PostgreSQL — Free and open source, supports medium-scale Data Warehouses, no licensing fees
   • ClickHouse — Column-oriented database designed specifically for analytics, open source
   • Apache Superset + Metabase — Free and open-source BI tools
4. Design the ETL pipeline

   Define how to extract data from the ERP, how to transform it, and how often to load it into the Data Warehouse (daily? hourly?).

5. Start small — 1 Subject Area

   Don't try to do everything at once. Start with the highest-demand Subject Area, such as "budget disbursement" — since every agency must report on it. Then gradually expand to procurement, inventory, and HR.

Saeree ERP and Data Warehouse

Saeree ERP uses PostgreSQL as its core database, which is a significant advantage for government agencies looking to build a Data Warehouse:

Advantage	Details
Open Source — No Licensing Fees	Both the ERP database and Data Warehouse can run on PostgreSQL — no redundant license fees, saving budget
Open Data Format	Data in PostgreSQL is easily exportable, with no vendor lock-in, aligned with government Open Data policies
SQL Standard	Uses standard SQL, compatible with all ETL and BI tools, whether Superset, Metabase, or Power BI
Logical Replication	Replicate data in real-time from ERP to Data Warehouse without impacting ERP system performance for active users
Complete Data Structure Across All Modules	Budget, procurement, inventory, accounting, and HR data have clear relationships, ready to serve as Data Sources for the Data Warehouse

A Data Warehouse is not an IT project — it is an investment enabling government agencies to make data-driven policy decisions, report transparently, maintain auditability, and align with the national Digital Government direction.

- Saeree ERP Team

Summary

• Data Warehouse is a data repository designed for analysis, not for recording transactions
• Differs from a Database in that it stores historical data, integrates multiple systems (ERP, GFMIS, e-GP), and is optimized for fast reads
• ETL Process is the heart — Extract, Transform, and Load data from various sources into the warehouse
• Star Schema is the most popular design — Fact Table (disbursement amounts) + Dimension Tables (fiscal year, agency, program)
• For government agencies — supports executive reporting, disbursement KPI tracking, State Audit Office compliance, and budget request preparation
• Start from policy questions, not from technology — determine what executives need to know, then design accordingly

If your agency is looking for an ERP system with an open database ready to connect to a Data Warehouse, you canschedule a demo orcontact our consulting teamto assess your agency's readiness.