Learn Data Warehousing in 24 Hours

By Alex Nordeen

Copyright 2021 - All Rights Reserved – Alex Nordeen

ALL RIGHTS RESERVED. No part of this publication may be reproduced or transmitted in any form whatsoever, electronic, or mechanical, including photocopying, recording, or by any informational storage or retrieval system without express written, dated and signed permission from the author.

Table Of Content

Chapter 1: What Is Data Warehouse?

What is Data Warehouse?

Types of Data Warehouse

Who needs Data warehouse?

Why We Need Data Warehouse?

Data Warehouse Tools

Chapter 2: Data Warehouse Architecture

Characteristics of Data warehouse

Data Warehouse Architectures

Datawarehouse Components

Query Tools

Chapter 3: ETL  Process

What is ETL?

Why do you need ETL?

ETL Process

ETL tools

Chapter 4: ETL Vs ELT

What is ETL?

Difference between ETL vs. ELT

Chapter 5: Data Modeling

What is Data Modelling?

Types of Data Models

Characteristics of a physical data model

Chapter 6: OLAP

What is Online Analytical Processing?

Types of OLAP systems

Advantages and Disadvantages of OLAP

Chapter 7: Multidimensional Olap (MOLAP)

What is MOLAP?

MOLAP Architecture

MOLAP Tools

Chapter 8: OLAP Vs OLTP

What is the meaning of OLAP?

What is the meaning of OLTP?

Difference between OLTP and OLAP

Chapter 9: Dimensional Modeling

What is Dimensional Model?

Elements of Dimensional Data Model

Attributes

Difference between Dimension table vs. Fact table

Steps of Dimensional Modelling

Rules for Dimensional Modelling

Chapter 10: Star and SnowFlake Schema

What is Multidimensional schemas?

What is a Star Schema?

What is a Snowflake Schema?

Difference between Start Schema and Snowflake

Chapter 11: Data Mart

What is Data Mart?

Type of Data Mart

Steps in Implementing a Datamart

Chapter 12: Data Mart Vs Data Warehouse

What is Data Warehouse?

What is Data Mart?

Differences between a Data Warehouse and a Data Mart

Chapter 13: Data Lake

What is Data Lake?

Data Lake Architecture

Key Data Lake Concepts

Maturity stages of Data Lake

Chapter 14: Data Lake Vs Data Warehouse

What is Data Warehouse?

What is Data Lake?

Key Difference between the Data Lake and Data Warehouse

Chapter 15: What Is Business Intelligence?

What is Business Intelligence

Why is BI important?

How Business Intelligence systems are implemented?

Four types of BI users

Chapter 16: Data Mining

What is Data Mining?

Types of Data

Data Mining Process

Modelling

Data Mining Techniques

Chapter 17: Data Warehousing Vs  Data Mining

What is Data warehouse?

What Is Data Mining?

Difference between Data mining and Data Warehousing?

Chapter 1 : What Is Data Warehouse?

What is Data Warehouse?

A data warehouse is a blend of technologies and components which allows the strategic use of data. It is a technique for collecting and managing data from varied sources to provide meaningful business insights.

It is electronic storage of a large amount of information by a business which is designed for query and analysis instead of transaction processing. It is a process of transforming data into information and making it available to users in a timely manner to make a difference.

The decision support database (Data Warehouse) is maintained separately from the organization’s operational database. However, the data warehouse is not a product but an environment. It is an architectural construct of an information system which provides users with current and historical decision support information which is difficult to access or present in the traditional operational data store.

The data warehouse is the core of the BI system which is built for data analysis and reporting.

You many know that a 3NF-designed database for an inventory system many have tables related to each other. For example, a report on current inventory information can include more than 12 joined conditions. This can quickly slow down the response time of the query and report. A data warehouse provides a new design which can help to reduce the response time and helps to enhance the performance of queries for reports and analytics.

Data warehouse system is also known by the following name:

Decision Support System (DSS)

Executive Information System

Management Information System

Business Intelligence Solution

Analytic Application

Data Warehouse

History of Datawarehouse

The Datawarehouse benefits users to understand and enhance their organization's performance. The need to warehouse data evolved as computer systems became more complex and needed to handle increasing amounts of Information. However, Data Warehousing is a not a new thing.

Here are some key events in evolution of Data Warehouse-

1960- Dartmouth and General Mills in a joint research project, develop the terms dimensions and facts.

1970- A Nielsen and IRI introduces dimensional data marts for retail sales.

1983- Tera Data Corporation introduces a database management system which is specifically designed for decision support

Data warehousing started in the late 1980s when IBM worker Paul Murphy and Barry Devlin developed the Business Data Warehouse.

However, the real concept was given by Inmon Bill. He was considered as a father of data warehouse. He had written about a variety of topics for building, usage, and maintenance of the warehouse & the Corporate Information Factory.

How Datawarehouse works?

A Data Warehouse works as a central repository where information arrives from one or more data sources. Data flows into a data warehouse from the transactional system and other relational databases.

Data may be:

Structured

Semi-structured

Unstructured data

The data is processed, transformed, and ingested so that users can access the processed data in the Data Warehouse through Business Intelligence tools, SQL clients, and spreadsheets. A data warehouse merges information coming from different sources into one comprehensive database.

By merging all of this information in one place, an organization can analyze its customers more holistically. This helps to ensure that it has considered all the information available. Data warehousing makes data mining possible. Data mining is looking for patterns in the data that may lead to higher sales and profits.

Types of Data Warehouse

Three main types of Data Warehouses are:

1. Enterprise Data Warehouse:

Enterprise Data Warehouse is a centralized warehouse. It provides decision support service across the enterprise. It offers a unified approach for organizing and representing data. It also provide the ability to classify data according to the subject and give access according to those divisions.

2. Operational Data Store:

Operational Data Store, which is also called ODS, are nothing but data store required when neither Data warehouse nor OLTP systems support organizations reporting needs. In ODS, Data warehouse is refreshed in real time. Hence, it is widely preferred for routine activities like storing records of the Employees.

3. Data Mart:

A data mart is a subset of the data warehouse. It specially designed for a particular line of business, such as sales, finance, sales or finance. In an independent data mart, data can collect directly from sources.

General stages of Data Warehouse

Earlier, organizations started relatively simple use of data warehousing. However, over time, more sophisticated use of data warehousing begun.

The following are general stages of use of the data warehouse:

Offline Operational Database:

In this stage, data is just copied from an operational system to another server. In this way, loading, processing, and reporting of the copied data do not impact the operational system’s performance.

Offline