Data Warehouse Basics

What’s a Data Warehouse? Why do they exist? How are they used? What are the data warehouse basics?

Here are the data warehouse basics:

Data warehousing is a key technology on the way to establishing business intelligence. A data warehouse definition is:

A data warehouse is a collection of data extracted from the operational or transactional systems in a business, transformed to clean up any inconsistencies in identification coding and definition, and then arranged to support rapid reporting and analysis.

Click here to see another data warehouse definition

Why do I need a data warehouse?

A quick answer would be to support your business strategy and information dashboards monitoring the progress of your business goals with fast and up to date information from disparate sources.

But let's first look at how data and systems are typically organised in a business.

First is the transactional or routine systems in a business which are designed to support fast data entry and retrieval of the basic information to progress the day to day, hour by hour work of the company. They are not usually designed to produce in depth analysis and consolidation reports.

A business or organization has usually three levels of Activities:

• Transaction or Routine or Production processes e.g. order taking
• Tactical processes e.g. production planning
• Strategic processes e.g. business and market planning

See the following diagram which shows these systems, time frames, data flows and where a data warehouse would fit in.

Reasons for a Data Warehouse

There are a few more reasons why a data warehouse should exist:

1. You want to integrate data across functions or systems to provide a complete picture of the data subject e.g. customer orders, customer complaints, salespersons. To do this on the fly or run, would be time coming and performance of your BI system would be poor.
2. You do not want to interfere with the fast performing transaction systems by running large computer resource queries and reports whilst routine users and possibly customers are executing the essential business transactions.
3. You want to reorganize the data to support fast reporting and querying.
4. You want to clean up the quality of the data to give consistency and data integrity. Many systems do not have strict input validation and garbage gets in ... duplicates e.g. same customer entered more than once. Also there often different definitions for the same subject or entity within the business e.g. customer, client, prospect.

Most data warehouses are built using database management software systems with the internal data structure of a warehouse usually taking two forms - star or snowflake

This concludes this brief summary of data warehouse basics.

Remember to Click here to view the video on Tableau Software

What is a Data Mart

How to Extract, Transform and Load (ETL) your data

How to Structure your Data Warehouse -
Star and Snowflake Dimensional Data Models

How to Succeed at Data Warehouse Project Management

Return to the Top of Data Warehouse Basics

Back to 'Business Intelligence' Home Page