Data warehouse facts for kids

How a data warehouse helps organize information

The basic parts of a data warehouse system

Imagine a giant, super-organized digital library for a company's information. That's pretty much what a data warehouse is! It's a special computer system that collects and stores huge amounts of information from different places within a business. Think of it as a central storage place for all the facts and figures a company has gathered over time.

This stored information helps people in the company understand what's happening, see patterns, and make smart choices. For example, a company might use it to see which products are selling best, or how many customers they had last year compared to this year.

The information usually comes from the company's daily computer systems, like sales records or marketing data. Sometimes, this data needs to be cleaned up and organized before it goes into the data warehouse, to make sure it's accurate and useful.

There are two main ways to build a data warehouse: ETL and ELT. These are just different methods for getting the data ready and into the warehouse.

How Data Gets In: ETL

The most common way to build a data warehouse uses something called ETL. This stands for Extract, Transform, and Load. It's like a three-step process:

Extract: First, the system extracts (pulls out) raw information from different sources. This raw data is put into a temporary "staging area."
Transform: Next, this raw data is transformed (changed and cleaned). It's made consistent and organized so it fits together. Imagine taking puzzle pieces from different boxes and making them all fit into one big picture.
Load: Finally, the cleaned and organized data is loaded into the main data warehouse. Here, it's often arranged into groups that make it easy to analyze, like facts (numbers) and dimensions (categories).

Once the data is in, managers and other business people can use it. They can "mine" the data to find hidden patterns or use special tools to analyze it quickly. This helps them make important decisions for the company.

How Data Gets In: ELT

How ELT works in a data warehouse

Another way to build a data warehouse is called ELT. This stands for Extract, Load, and Transform. It's a bit different from ETL:

Extract: Data is extracted from different source systems, just like in ETL.
Load: But instead of transforming it first, the data is directly loaded into the data warehouse. It goes into a staging area *inside* the warehouse.
Transform: All the necessary transformations (cleaning and organizing) happen *after* the data is loaded into the warehouse. The data is then moved to its final, organized tables within the same data warehouse.

ELT can be faster for loading very large amounts of raw data. The powerful data warehouse system does all the hard work of transforming the data once it's inside.

Why Data Warehouses Are Great

Data warehouses offer many benefits to companies:

All in One Place: They bring information from many different sources into one single database. This makes it much easier to get a complete picture.
Faster Analysis: They help avoid slowing down the company's daily systems. Big analysis questions can be asked without affecting regular operations.
Keep History: They store old information, even if the original systems don't. This means companies can look back years to see trends.
Better Quality Data: They help clean up and fix bad or inconsistent information. This makes sure the data is reliable.
Consistent View: Everyone in the company sees the same, consistent information.
Easy to Understand: Data is organized in a way that makes sense to business users.
Quick Answers: They are designed to answer complex questions very quickly.
Smarter Decisions: They make it easier for people to write questions that help them make decisions.

Related Systems

Data warehouses often work with other special systems:

Data Marts

Imagine a smaller, more focused version of a data warehouse. That's a data mart. It's like a mini-warehouse that focuses on just one specific area, like sales, marketing, or finance.

Data marts are usually built and managed by one department.
They get their data from a limited number of sources, sometimes even from the main data warehouse.
They are often quicker and easier to build than a full data warehouse.

Here's a quick look at the differences:

Difference between data warehouse and data mart
Attribute	Data warehouse	Data mart
Scope of the data	whole company	one department
Number of subject areas	many	one
How difficult to build	difficult	easy
How much time takes to build	more	less
Amount of memory	larger	limited

OLAP (Online Analytical Processing)

OLAP is a way to quickly analyze lots of data from different angles. Think of it like looking at a data cube. You can "slice and dice" the data to see totals, averages, or specific details.

OLAP systems are great for finding patterns and trends in historical data.
They help answer complex questions like "What were our total sales for product X in region Y during the last quarter?"

OLTP (Online Transaction Processing)

OLTP systems are for handling everyday business transactions. They are super fast at recording new sales, updating customer info, or processing orders.

These systems focus on keeping data accurate and up-to-date.
They are designed for many small, quick actions, like when you buy something online.

Predictive Analytics

Predictive analytics uses smart math models to find hidden patterns in data. It helps companies guess what might happen in the future.

Unlike OLAP, which looks at the past, predictive analytics tries to predict future outcomes.
For example, it might predict which customers are likely to buy a certain product.

History of Data Warehouses

The idea of a data warehouse started in the late 1980s. Researchers at IBM, Barry Devlin and Paul Murphy, came up with the "business data warehouse" concept. They wanted a better way to get data from daily business systems into systems that helped with decisions.

Before data warehouses, companies often had many separate systems for making decisions. Each system would collect and clean much of the same data over and over, which was expensive and inefficient. The data warehouse aimed to fix this by providing one central, organized place for all decision-making data.

Here are some key moments in data warehouse history:

1960s: The terms dimensions and facts (ways to organize data) were first developed.
1970s: Companies like ACNielsen started using dimensional data for sales analysis.
1988: Barry Devlin and Paul Murphy published their paper introducing the "business data warehouse."
1992: Bill Inmon, a key figure in data warehousing, published his important book Building the Data Warehouse.
1995: The Data Warehousing Institute was founded to promote the idea.
1996: Ralph Kimball, another important expert, published The Data Warehouse Toolkit.

How Information is Stored

In a data warehouse, information is usually stored in two main ways: as "facts" and "dimensions."

Facts

A fact is a number or measurement that tells you something specific. For example, if a phone tower gets 1,000 requests for calls, and 820 of them succeed, the "1,000" and "820" are facts.

tch_req_total = 1000 (total requests)
tch_req_success = 820 (successful requests)

These raw facts can then be added up or averaged to get bigger, more useful facts. For instance, if there are three phone towers in a city, you can add up all their successful requests to get the total successful requests for the whole city.

Dimensional vs. Normalized Storage

There are two main ways to arrange data inside a data warehouse:

Dimensional Approach

In the dimensional approach, data is split into "facts" and "dimensions."

Facts are the numbers, like the number of products sold or the total price.
Dimensions give context to those facts, like the date of sale, customer name, or product number.

Imagine a sales transaction: the fact is the number of items sold, and the dimensions are *when* it was sold, *who* bought it, and *what* product it was.

This approach makes it easier for people to understand and use the data warehouse. It's also very fast for getting answers to questions. Think of it like a data cube, where facts are values inside the cube, and dimensions are the labels on the sides.

The main challenge is that it can be tricky to load data from many different systems into this structure. Also, if the company changes how it does business, it might be hard to change the warehouse structure.

Normalized Approach

In the normalized approach, data is stored in a very organized way, following strict rules to avoid repeating information. Data is grouped into "subject areas" like customers or products. This method makes it easy to add new information to the database. However, because there are many tables linked together, it can be harder for users to combine data from different sources and get the information they need without knowing a lot about the database's structure.

Both methods use linked tables, but the difference is how much they break down and organize the data. Sometimes, a data warehouse might even use a mix of both!

How Data Warehouses Are Designed

There are different ways to plan and build a data warehouse:

Bottom-up Design

In this method, smaller, specific data marts are built first for different parts of the business. Then, these smaller data marts are combined to create a larger, complete data warehouse. It's like building small LEGO sets and then putting them all together to make one giant model.

Top-down Design

This approach starts with designing a big, detailed model for the entire company's data. All the raw, detailed information is stored in the main data warehouse. Then, smaller data marts are created from this main warehouse for specific departments or needs. It's like drawing a big blueprint for a whole city, and then building individual neighborhoods based on that plan.

Hybrid Design

Many data warehouses use a mix of both approaches. They might have a central data warehouse that stores data in a very organized way to avoid repeating information. Then, smaller data marts are built on top of this central warehouse to help with specific reports and analyses. This allows for both a single source of truth and flexible reporting.

Key Features of Data Warehouses

Data warehouses have some special characteristics:

Subject-oriented

Unlike daily systems that focus on tasks (like processing an order), a data warehouse focuses on subjects (like customers, products, or sales). This makes it easier to analyze information related to a specific topic.

Integrated

Data in a data warehouse comes from many different sources. It's all cleaned up and made consistent, so there are no confusing differences in how things are named or measured.

Time-variant

Data warehouses store historical information over a long period, sometimes up to 10 years or more. This means you can look at past trends and patterns, which is great for predicting the future.

Nonvolatile

Once data is in the data warehouse, it usually doesn't change. It's like a permanent record. You can read it, but you can't easily update, create, or delete it (unless there's a special reason, like a legal requirement).

Data Warehouse Options

Aggregation

Data in a data warehouse can be summarized at different levels. You might start by looking at total sales for a product across an entire country. Then, you can "drill down" to see sales in specific states, and then even further to see sales in individual stores. This lets you explore data from a big picture view down to the smallest details.

Virtualization

Sometimes, instead of moving all the data into one physical warehouse, a "virtual" data warehouse is created. This means the data stays in its original places, but special tools allow you to access and analyze it as if it were all in one central spot. This can help with privacy rules and make sure you're always using the newest data. However, it means all the original data sources must always be available.

Data Warehouse Architecture

The way a data warehouse is built and organized is called its architecture. This includes the computer hardware, the software, and how the data itself is arranged. Companies often adjust their data warehouse architecture over time as their needs change.

Data Warehouse vs. Daily Systems

It's important to know that data warehouses are different from the computer systems companies use for their daily operations (like processing orders or managing inventory).

Daily Systems (OLTP): These systems are super fast at recording new transactions. They are designed to keep data perfectly accurate for everyday tasks. They often store only current information and might remove old data.
Data Warehouses (OLAP): These systems are built for analyzing data. They are optimized to answer complex questions quickly, even if it means looking through years of historical information. They keep a long history of data.

How Data Warehouses Evolve

Data warehouses can become more advanced over time:

Offline operational data warehouse: Data is updated regularly (daily, weekly) from daily systems and stored for reports.
Offline data warehouse: Similar to the above, but the data is stored in a way specifically designed for easy reporting.
On-time data warehouse: Data in the warehouse is updated almost instantly for every new transaction. This is like a real-time data warehouse.
Integrated data warehouse: These warehouses combine data from all different parts of a business. This allows users to get a complete view of information across the entire company.