Data warehouse explained

WHY

Different business processes need different data structures due to different needs.
The aim of a data warehouse is to

• make the data simple to understand
• performant
• quality assured
• ready for new questions
• secure

It should deliver the data to the user

• in an understandable and performant dimensional model
• with conformed dimensions or separate data marts
• to report and visualize -- by interacting directly with the model -- or through an BI application

OLTP - online transactional processing

For running a business it is important to track different things, like for example inventory, financial transactions or different types of movements.
A single relational database might be sufficient for this part, as there is no redundancy and high integrity.

OLAP - online analytical processing

To understand what is going on in a business it is important to do analysis and monitoring. Like examine market segmentation over a period of time or split users into market segmentations.
As single relational database is not sufficient, because it might become too slow for analytics and quite hard to understand.

WHAT

A data warehouse is a system that enables the support of an analytical process. It gathers data from multiple sources. It is a copy of transaction data specifically structured for queries and analysis.

The data from OLTP will run through an ETL process to provide data for the OLAP.

ETL - Extract Transform Load

Extract

• get data from a source
• update old states

Transform

• integrate many sources together
• clean data
• produce metadata

Load

• structure the data
• load into a dimensional data model

Dimensional modeling

The dimensional model will help to understand the data easily and perform fast analytical queries.

Fact table - numeric and additive

• business events, like a phone call or a review of a book
• context of the business events in quantifiable metrics. Like duration of a call, the rating of a book

Dimension table

• context of business events: who, what, where, why
• attributes, like: date, time, physical locations, human roles, good sold