Data Warehouse Tutorial

Data Warehouse Tutorial

Data Warehouse is a storage repository for integrating data from multiple sources, which can be used for Reporting and Analysis. This repository then becomes a source for Business Intelligence.

Input for a DWH is extracted from different systems of the enterprise that can include current or historical data. The main goal of DWH is, to be able to handle bulk data, to establish a relationship across the systems, and use the end product for Analytical & Reporting purposes.

Usually, data is gathered from the Input sources, a clean-up is done to avoid redundancy and inaccurate junk data, staged, Integrated and Loaded on to the database, where the database can contain different schema based on the categorical groups of the given data. Schema is a model or specific format, applied for all the data in DWH, irrespective of their source formatting.

Why do we need to learn DWH?

Data Warehouse plays a vital role in Business Intelligence. Using DWH, business decision-makers can create analysis or reports on any given perspective. DWH helps them to integrate huge amounts of data gathered from multiple sources. The business personnel can fetch all the required information from DWH, to generate the required Statistics or Reports.
DWH makes it uncomplicated in terms of time consumption, as all the data is situated in one location, instead of multiple sources. Since all the data are transformed into a single schema, DWH has well defined and accurate data, with minimum or zero redundancy. Business professionals, who have constant need to make business decisions, need not go through the hassle to research and gather information anymore. A data warehouse can be used as a one-stop solution.

Applications of DWH

Data Warehousing can be implemented in any area that has the need for frequent decision making to be done, in order to have constant growth in their business.

Below are a few areas where DWH can be implemented:

1. Any Industry that runs with its employees as its main resource, like –

a. Police Department – Information like officers in each region, officers in specific roles, officers succeeded in their mission, number of cases completed in a given time, etc., can be used to generate a report on the Department’s records for a particular given.

b. Software Industry – Information like Employees’ attendance, pay, Awards won, previous years’ performance, etc., can be used to decide on the Employees’ current performance

2. Banking – DWH has multiple applications in Banking Industry that include subdivisions like customer care, customer’s transactions, Bank’s communication systems, Bank’s employees, etc.

3. Retail – In retail industries, customer relationship management, sales, services, etc., can be facilitated flawlessly as DWH holds data accuracy as its key feature.

Example

Let us consider an example here:

A Food production company named ‘Complete Foods’ needs to see the Statistics on the sales performance for the current financial year. The Data warehouse owned by ‘Complete Foods’ can be used to pull out all the sales records with respect to the sales region, outlets, profits/ loses, customer
feedback, employee records, along with the individual sales flow for different departments like packaged food, fresh produce, poultry, dairy, etc.,

Prerequisites

For anyone to be able to implement a DWH, the below are the basic requirements:
1. Knowledge of Data warehousing process
2. Data handling experience
3. Hands-on with querying languages like pl/SQL, mySQL, T-SQL, etc.,
4. Documented requirements from the Project’s stack holders.
5. Other resources like access rights for data from all the required sources, all the required hardware and software setup, etc.

Target Audience

Software Engineers with data handling experience, along with the additional knowledge on Data Warehouse or Data Science in general.