If you are asking what a data warehouse is, the short answer is this: it is a central store of data, copied from an organisation’s different systems, cleaned and organised so that it can be used for reporting and analysis. Business Intelligence (BI) reports and dashboards then draw on it. This guide explains how a data warehouse differs from an ordinary database and from a data lake, how data gets into it, and how it is used. For SQL Server, Excel, Power BI and Access training, browse our Microsoft Office and computer skills courses.
The current course runs at your offices anywhere in South Africa, live online or at a BOTI venue. The course page has the up-to-date outline and group prices.
See the current courseGet a quote for your teamSee all Microsoft Office and Computer Skills courses
Learning on your own? Self-paced e-learning from R595 (excl. VAT) · Ask your employer to pay
See all BOTI supply chain, procurement and contracts courses.
Related: Supply Chain Course Cost, Requirements & Where to Study in South Africa.
Related: Tender & Contract Management Training: Manage the Contract You Just Won.
What is a data warehouse?
A data warehouse is a database designed for analysis rather than for running day-to-day transactions. Most organisations keep data in separate systems: accounting, sales, payroll and HR, and a collection of spreadsheets. Each answers its own questions, but none can answer a question that cuts across them, such as which customers are most profitable once returns and discounts are counted.
A data warehouse brings that data together in one place. Four features set it apart:
- Integrated: data from different systems is matched up, so a customer, product or branch means the same thing everywhere.
- Historical: it keeps data over months and years, so you can compare this year with last year.
- Stable: data is loaded on a schedule and then read for reports. Users do not edit it directly.
- Organised by subject: data is arranged around business subjects such as sales, customers, stock and staff.
Data warehouse vs database
Every data warehouse is a database, but not every database is a data warehouse. The difference is what each is built to do.
An operational database sits behind a single application, such as an invoicing or booking system. It handles many small transactions, holds mainly current data and avoids storing the same fact twice. Running large reports on it can slow down the system staff and customers depend on.
A data warehouse is built for the opposite workload: fewer, larger queries that summarise a great many rows. Its tables are often arranged in a “star” layout, with a central fact table holding the measures (sales amounts, quantities, hours) and surrounding dimension tables describing them (date, product, customer, region). That layout makes it quick to slice the figures by any combination of dimensions.
Data warehouse vs data lake
A data lake stores large volumes of raw data in its original form: database tables, but also files, logs, documents and images. Data is usually stored first and structured later, when someone needs it. That makes a lake flexible, but without good management it becomes hard to find or trust anything in it.
A data warehouse works the other way round. Data is cleaned and structured before it is loaded, according to definitions agreed in advance, so that reports are reliable. Many organisations use both: a lake for keeping raw data, and a warehouse for trusted reporting. A data mart is a smaller, focused part of a warehouse that serves one department, such as finance or sales.
| Feature | Operational database | Data warehouse | Data lake |
|---|---|---|---|
| Main purpose | Run day-to-day transactions | Reporting and analysis | Store raw data for later use |
| Data | Current, from one application | Cleaned, integrated, historical | Raw, in many formats |
| Structure | Designed for fast updates | Designed for fast reading | Applied when the data is used |
| Typical users | Staff using the application | Managers, analysts, report writers | Data engineers and data scientists |
How data gets in: ETL
Data reaches a warehouse through a process called ETL: extract, transform and load.
- Extract: copy the data from each source system.
- Transform: clean and reshape it. That means removing duplicates, fixing formats and dates, standardising codes, combining records from different systems and applying business rules, such as how revenue or an “active customer” is defined.
- Load: write the result into the warehouse tables.
ETL usually runs on a schedule, often overnight. A well-built ETL process supports incremental extraction and loading, so each run handles only new or changed data instead of copying everything again. Some teams load the raw data first and transform it inside the warehouse, an approach called ELT.
Data quality is part of the job. Validation checks catch missing or impossible values, and master data, the single agreed list of customers, products or cost centres, keeps every report using the same definitions. If the data going in cannot be trusted, neither can the reports that come out.
How a data warehouse is used for reporting and BI
A data warehouse feeds reporting and Business Intelligence:
- Management reports and dashboards, such as sales by region by month or staff costs by department.
- Trend and variance analysis: actual against budget, this month against last, this year against last.
- Self-service analysis, where analysts connect Excel or Power BI to the warehouse and build their own reports.
- One version of the truth, so that finance, sales and operations quote the same figures in the same meeting.
Most of this data is retrieved with SQL, the standard language for querying databases.
Does every organisation need a data warehouse?
Not necessarily. A small organisation with one or two systems can often report directly from them, or combine data in Excel or Power BI, which can import, transform and load data from spreadsheets, text files and databases. A warehouse becomes worthwhile when data sits in many systems, when reports from different departments disagree, when history is lost as systems change, or when reporting starts to slow down the systems people work in.
Skills for working with a data warehouse
Those who build and load a warehouse need solid relational database experience: tables and relationships, normalised design, SQL queries and some programming. Analysts who use it need to write SQL queries and to analyse and present the results in Excel or Power BI.
BOTI’s data courses
- Getting Started with Microsoft SQL Server: SQL Querying (2 days): for people new to SQL Server who need to query data in a database.
- Data Analytics Fundamentals with Excel and Power BI (3 days): an introduction to data analytics using both Excel and Power BI.
- Microsoft Power BI Basics (3 days): importing, transforming and loading data from Excel, text files and databases, building reports and dashboards, and sharing them. It suits business managers, report developers, analysts, project managers and team leads.
- Analysing Data with Excel (2 days): cleaning and structuring data, then pivot analysis and trend and variance reporting.
- Excel Pivot Tables, Dashboards and Power Pivot (2 days): for staff who summarise large data sets in Excel.
The courses in this list are not accredited. They are delivered as professional skills programmes, and delegates receive a BOTI certificate of attendance. For a short start, the Power BI Foundations: Build Your First Live Report masterclass is a live 3-hour online session.
Speak to a BOTI consultant
Phone 011 882 8853 or email [email protected], or fill in the form and we will get back to you. You can also browse all BOTI courses.
Frequently asked questions
What is a data warehouse?
A data warehouse is the central platform that supports a Business Intelligence (BI) solution. Data is extracted from source systems, transformed and loaded into it (ETL), cleansed and validated so it can be trusted, and then used by BI reports and analysis. Unlike an ordinary operational database, it combines data from many systems and keeps history over time.
What is ETL in a data warehouse?
ETL stands for extract, transform and load: the process that moves data from source systems into the data warehouse. Data is copied from each source, cleaned and reshaped to agreed definitions, and then written into the warehouse tables. A good ETL solution supports incremental extraction and loading, so each run handles only new or changed data.
What is the difference between a data warehouse and a data lake?
A data lake stores raw data in its original form, including files, logs and documents, and structures it only when someone uses it. A data warehouse holds data that has already been cleaned and structured to agreed definitions, so it is suited to reliable reporting. Many organisations use a lake for raw storage and a warehouse for trusted reports.
How is data kept clean and reliable in a data warehouse?
Through data cleansing and validation during the ETL process, which removes duplicates, fixes formats and flags missing or impossible values, and through master data management, which keeps one agreed list of customers, products and other key records. Together they make sure the reports built on the warehouse draw on consistent, validated data.
What experience do I need before learning data warehousing?
To build a data warehouse, you need relational database experience: creating tables and relationships, writing Transact-SQL queries, normalised database design, and basic programming constructs such as branching and looping. To use one for reporting, SQL querying and Excel or Power BI skills are enough to start. An understanding of business priorities such as profitability and financial accounting also helps. If you are new to SQL Server, start with a SQL querying course.
Data warehouse work starts with solid Transact-SQL querying and relational database skills. BOTI’s Getting Started with Microsoft SQL Server: SQL Querying course builds that foundation for people new to SQL Server. It runs in-house at your premises, at our venue in Sunningdale, Johannesburg, or online, and delegates receive a BOTI certificate of attendance. Call 011 882 8853 or email [email protected] to arrange a date.
