12 - Data Warehouse

Summary

Chapter Objectives

✓ To understand the need of an operational data store in OLTP and OLAP systems

✓ To understand data warehousing, its benefits and architecture

✓ To do a comparative study on OLAP, OLTP and ODS.

✓ To comprehend the concept of data mart.

In order to understand what an Operational Data Store is, it is very important to know what led to such Operational Data Stores and what the limitations were of OLTP systems to answer management queries.

The Need for an Operational Data Store (ODS)

Online Transaction Processing (OLTP) systems have become popular due to their versatility. Right from financial transactions to daily log operations, these are being used by myriad multinational companies and world level organizations to record transaction details.

In the present scenario, organizations and their branches operate in many locations across the world. Each, such, branch generates massive amounts of data. The management of a large retail chain operating from multiple locations, for example, will at the end of day, want to know about transactions done that day. One could take the case of a Dominos Pizza store, where the management needs to know the total sales for that day or other details such as the number and types of pizzas sold. Such companies rely on OLTP systems to get data from multiple stores spanning the world. On these OLTP systems, queries usually run on an indexed database, as this makes the searching fast and efficient. But unfortunately, data spread over multiple systems leads to a plethora of technical errors when carrying out the simple task of running queries on data stored on OLTP systems.

When dealing with corporate data it is also necessary to maintain correct and accurate information, in order to provide swift customer support services. This is possible if the data is acquired from all information sources.

It is very important for any organization's management to know the real state of affairs of their organization. However, OLTP systems fail badly, as they were not designed to support management queries. Management queries are very complex and require multiple joins and aggregations while being written. To overcome this limitation of OLTP systems some solutions were proposed, which are as follows.