คลังข้อมูลและระบบสารสนเทศเพื่อธุรกิจ

Your live app database is OLTP (transaction processing): fast small reads and writes. Analytics needs OLAP: large scans over historical data.

Running heavy analytics on the production database harms app performance, so we separate them.

A data warehouse (Snowflake, BigQuery, Redshift) stores large volumes of historical data optimized for analytical queries.

It is the single source of truth for reporting across the whole SaaS business.

Two patterns move data into the warehouse:

• ETL — Extract, Transform, then Load (transform before storing)
• ELT — Extract, Load, then Transform (load raw, transform inside the warehouse)

Modern cloud warehouses favor ELT because they have cheap, powerful compute.

Extraction pulls data from sources: the app database, event streams, and third-party APIs. It can be a full reload or an incremental pull of only changed rows.

Incremental extraction scales far better as data grows.

Warehouses model data as a star schema: a central fact table (measurable events) surrounded by dimension tables (descriptive context like date, user, product).

A fact row records a measurable event with numeric measures and foreign keys to dimensions.

BI queries aggregate facts: sum revenue by month, count signups by plan, average usage by region.

Pre-computed roll-up tables or materialized views speed up frequently used aggregations.

BI tools (Looker, Metabase, Tableau) sit on top of the warehouse and let non-engineers explore data through charts and dashboards.

This democratizes data access across the company.

In multi-tenant SaaS, analytics must respect tenant boundaries. Internal dashboards may span all tenants, but customer-facing analytics must filter strictly by tenant ID.

A leak here exposes one customer's business data to another.

Warehouses can update in batches (hourly, nightly) or near real-time via streaming. More freshness costs more compute.

Match the refresh rate to the decision: executive reports rarely need second-by-second data.

Bad data leads to bad decisions. Apply data quality checks (no nulls in keys, totals reconcile) and governance (access control, documentation) so the warehouse stays trustworthy.

Trust in the numbers is the whole point of BI.

You learned data warehousing and BI:

• OLTP vs OLAP and why to separate them
• ETL vs ELT pipelines with incremental extraction
• Star schemas (facts and dimensions), aggregation, and BI dashboards
• Tenant isolation, freshness/cost trade-offs, and data quality