What is datawarehouse and features of data ware house.

1) What is data warehouse:-

Data warehouse can be defined as ‘Structural Repository’ of historic data. It is developed in evolutionary process by integrating the data from non integrated systems like text files, excel sheets, databases(The same is shown in the diagram below.)

Features of data warehouse:-

1) Subject oriented.

2) Integrated

3) Time variant

4) Non volatile.

5) Data Granularity.

1)      Subject Oriented:-DWH is subject oriented in the sense that the data is integrated from disparate sources unlike in OLTP, where we store the data according to the applications for example the applications for keeping track of transactions which is happening on daily basis. In the case of OLAP, the data is not stored according to the application but is stored according to subjects like customers, products, branches etc.

Example:-‘the big market ’ have the application called inventory management which basically manages number of units sold and number of units present in inventory for particular product for certain period.This application is OLTP since the data is available according to the application.In DWH the data will be stored according to the subjects like product,region,branch and this data will be used data for analysis purpose.

2)Integrated:-The data in DWH is integrated in the sense that data from the disparate system as well as the data from different application will be accumulated and will be stored in the single database called DWH(This process is known as ETL in DWH.)  for analysis and decision taking purpose.

Example:-‘The Big market’ have different applications like Point Of Sale(POS),inventory management,vendor management.These applications are used to keep the track of number of SKU’s which are sold,number of SKU’s which are present for the particular product and number of SKU’s which are taken from different vedors.

In case of OLTP systems,these application will be maintained in excel sheet,notepad,databases which means that all the system are disparate.In DWH system data from the disparate system will be accumulated in single database for analysis like :-

1)Which vendor is giving products at lower cost for particular product.

2)Which product is there in inventory for long time and have to be removed.

So the data from all the system will be collected and finally put in DWH in Integrated form in the sense that all the data will be subject oriented.

3) Time variant

In OLTP the data will be stored according for current values unlike in DWH,the data will be stored for time which may range from 1 year to 10 years.

For example:-If we take inventory management OLTP system,it will store the number of SKU’s which are present for different products like apparels,deodorant etc .On the other hand DWH is meant for analysis and decision taking purpose which will help management to take decisions like:-

1)Which product is most liked by customer?

2)Which product is giving me profit?

3)Which product is not all taken by customers and order of that product should be stooped.

Difference between OLTP and DWH with respect to Time Variant:-

Sl. No.	Operational System	Datawarehouse
1	View of business today	View of business for a period of time like time ranging between 3 to 10 yrs.
2	Operational Time Frame	One Snapshot per cycle.
3	There may be no date key	Date key is most important in DWH.

                            

Note:-Data warehouse is an environment not a technology.

It is an environment which provides:-

1) The total view of company information.

2) Makes company data available for analysis and decision making.

3) Makes decision support transactions possible without hindering the operational systems.

4) Non-Updateable:-

 DWH is not updateable in the sense that the data cannot be deleted or modified, it can only be viewed. The DWH is used for analysing the data so it periodically get refreshed by picking up the data from various OLTP systems.

Sl. No.	Operational systems	Data warehouse
1	The business transactions updates the data.	The various disparate operational systems updates the data.
2	Updation or deletion of the records can be done	Data cannot be modified or deleted, only can be inserted.
3	Operational system is for keeping track of day to day transaction	Warehouse is only for query and analysis.

5) Data Granularity:-

Data granularity can be defined as the level of details of data.In OLTP the data granuality is the number of units for each unique products.They will have the details of how many units of product is sold and how many are still remaining in the stock.In the case of DWH,the user will only look into the summary data like:-

1)Sale of products across all the stores.

2)Which region have recorded the maximum sale.

3)Which store in that region has given maximum sale.

“The lower the level of details, finer is the data granularity” it means that if you have to data to be more granular then lot of data has to be stored in the warehouse.