Database and Data Warehouse concepts for BI

Before diving into an even detailed data warehouse, modeling and, analytical topics, I would prefer to develop a clear understanding of related background topics.

Data as Information

Any record in any organization is its data asset which can be transformed into information for many purposes. Gathered data can be used for two major purposes: Operational record keeping —termed as OLTP (Online Transactional Processes)— and analytical decision making —termed as OLAP (Online Analytical processes).

What are Operational Systems? (Application structure/ Logical design)

For operational data keeping, many organizations use different software to take data in and perform day-to-day transactions. All inventory management tools are operational systems such as keeping records of employees, maintaining students records with respect to classes/departments and also making inventory system for a super mart etc. are highly structured and normalized databases designed for easy access of data for fast queries.

What is Data Warehouse? (Warehouse structure)

A data warehouse is a self-descriptive term as in a data warehouse we store data by gathering it from different sources like CRM, ERP or flat files etc. After data collection, it goes into ETL process which comprises of extraction, transforming and loading phase. Once the data is loaded, analysis can be performed with the aid of complex queries.

A very precise and well-stated definition of data warehouse by Barry Devlin is:

A data warehouse is simply a single, complete and consistent store of data obtained from a variety of sources and made available to end users in a way they can understand and use in a business context.

Database vs Data Warehouse

Data warehouses and databases both are significant and are build to perform their roles differently. If you don’t have a database/operational system or any record keeping tool initially, you can never perform the analysis for business decisions. Below is a table establishing some of the comparisons between a Databases and a Data Warehouse.