Data Warehousing

Data Warehousing

• Definition : Data warehousing is a large, centralized storage system that collects and stores data from different sources in one place for analysis and reporting.

• In short, data warehouse is like big library for data.

Real‒world examples of data warehousing

1) Amazon: It tracks customer purchases and recommended products.

2) Netflix: It stores users viewing history to recommend shows.

3) Walmart: It analyzes sales and supply chain data from all stores.

Need for data warehouse

1) It collects data from different systems into one place.

2) It supports in decision making.

3) Data is cleaned, transformed and stored in consistent format. Thus quality of data is improved.

4) Analysts do not need to search for the data from multiple databases. Everything is available one place.

5) Data warehouses store data for many years. This allows the analyst to identify long term trends and patterns.

Characteristics of data warehouses

1) Data in data warehouse is combined from multiple sources into one format.

2) The data is arranged as per the subject like Sales, inventory, customer‒details

3) Historical data is present in the data warehouse.

4) Once data is entered in data warehouse it is not changed or deleted.

1. Components of Data Warehouse

• Here are some core components of data warehouses

1) Data sources: This is a system where raw data from multiple sources come from.

2) ETL tools: ETL stands for Extract, Transform and Load. The ETL tools collect, clean and load data into the warehouse.

3) Data storage: It is a central repository that stores all data. For example Oracle, SQL server.

4) Metadata: Data about data. It is a data that describes the structure and meaning of data.

5) Data marts: These are subset of data warehouse which is customized for specific departments or business functions like Sales or Finance.

6) Query and analysis tools / Front end: These tools allow users to run queries, generate reports and perform data analysis. For examples ‒ SQL Clients, dashboards.

7) Management and Administration: These are the tools that handle tasks like performance tuning, security, backup and user access control.

2. ETL Process

• ETL stands for Extract, Transform and Load. It is the core activity in data warehousing.

The ETL process prepares data before it enters the warehouse.

1.Extract: It collects data from different sources. For example ‒ Sales data can be collected from various branches. Customer data from Customer Relationship Management (CRM).

2.Transform: This is an activity in where the data is cleaned and stored into particular format. The same uniform format of the data is maintained by this activity.

3. Load: Under this activity the stored transformed data is entered into the warehouse. For instance ‒ Sales data can be loaded into Sales warehouse.

3. Advantages and Limitations

Advantages

1) All data is available in one place. Hence access to information is faster.

2) The data help the managers because data is accurate and in uniform format.

3) The historical data is available. So, performance can be compared over the years.

4) The data warehouse support data mining and business intelligence tools.

Limitations

1) Costly: Building and maintaining data warehouse can be very expensive.

2) Complex setup: For creating data warehouse the complex setup is needed. This requires skilled technical team.

3) Data delay: Huge data is stored in dataware house is not real time. It is updated periodically.

4) Storage space: It requires large storage space for storing historic data.

4. Applications

1) Financial analysis: It is used to track expenses, forecast revenue and evaluate investment performance.

2) Supply chain management: Monitors inventory, logistics and vendor performance and sales trend.

3) Customer relationship management: It is used to store customer information, preferences, and behaviour.

4) Healthcare : It is used to manage patient records and treatment outcomes.

5) Government secor: The data warehouse is useful for maintaining and analyzing tax, health and policy records for transparency and planning.

6) Banking: The warehouse is also used to track account performances, assess the credit risks and support fraud detection.

5. Difference between Data Mining and Data Warehousing.

Data warehousing

1.It is a process of collecting, transforming and Storing data from different sources to one central place.

2.It is used to organize and store data for easy access and reporting.

3.It is mainly data storage and management.

4.It used ETL process.

5.The outcome of data warehousing is organized, clean and historical data.

6.For example ‒ A company stores 5 years of sales, customer and product data in one system for reporting.

Data mining

1.It is the process of analysing data to find hidden pattern.

2.It is used to discover useful information or knowledge from stored data.

3It is mainly data analysis and pattern discovery.

4.It uses algorithms and statistical methods to analyze data.

5.The outcome of data mining is patterns, trends and predictions based on data.

6.For example The company analyzes this stored data to find that "customers who buy shoes often buy socks too."

Review Questions

1.Write the main purpose of a data warehouse.

2.List and explain the key characteristics of a data warehouse.

3.What is the ETL process? Describe its three stages briefly.

4. Explain the term metadata in the context of a data warehouse.

5. Explain the difference between data mining and data warehousing.