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
and information dashboards monitoring the progress of your business
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:
- 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.
- 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.
- You want to reorganize the data to support fast reporting and querying.
- 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