The process of data warehousing involves information from a company’s internal system, such as invoices and sales logs, as well as data from outside sources, being filed away in an electronic vault.

How it works

The data warehouse is a repository that holds the company’s sales and operational history, as well as relevant economic and trade information from other sources. The data goes through three stages before it is stored in the warehouse, which makes it usable for analytical purposes. Once stored, the data may be accessed by all areas of a company—from accounts and operations to sales and marketing.The data is often used to assess beliefs and intuitions about the business. For example, the marketing manager of a power tools company might presume that 25–35-year-old men are more likely to purchase their products than women in the same age bracket. The manager would test this belief by analyzing sales data and customer records accessed from the data warehouse.

Warehousing process

The data stored is regularly updated. When the business requires information from the warehouse, it is transformed into an accessible format and analyzed using software tools.

Tapping data sources The information a company collects includes online transaction processing (OLTP) data, historical data, and data from external sources.

OLTP Includes
transactions such as sales and
refunds recorded via OLTP

HISTORICAL DATA
Repository of past sales information

EXTERNAL DATA
Includes
government statistics on business

EXTRACT, LOAD,
TRANSFORM (ELT)

Staging data The ELT process converts raw data into a usable format.

USABLE FORMAT

USABLE FORMAT

Storing data The data is stored in three sections: metadata, summary data, and raw data.

• METADATA Information relating to the data itself
• SUMMARY DATABusiness activity information
• RAW DATA The original form of the information

Accessing data Using software, the data can be analyzed and retrieved in three ways: via online analytical processing (OLAP), reporting tools, and data mining.

• OLAP Accesses data to answer specific questions
• REPORTING TOOLS Presents data as tables or graphs
• DATA MINING Finds detailedpatterns in data for analysis

WHO USES THE DATA WAREHOUSE?

The key departments of a company can access the data warehouse to find out how they are performing. The method in which the data is formatted and stored makes it possible for them to seek answers to questions relevant to them. Typical questions various departments might ask include:

FINANCE “What was profit
margin on product sold in a region?”

MARKETING “How did online ad compare to poster ad campaign?”

SALES “What are average sales of product by region?”

HUMAN RESOURCES “How much have we spent on contract staff this year?”