OLTP versus OLAP

Actually, the question is not whether one needs a database that supports OLTP versus a database that supports OLAP. You always need both, you may just not yet know it or believe it.

In the situations where I have seen customers get into trouble with their database model, it was almost always because the custom built system started out to support their daily transactional need and then, an analytical or warehouse requirement was added to it to support what is commonly called reporting. It is there where the problem begins because the natural tendency is to just augment the current model to support the additional requirements.

Often, this is done by adding time stamps, aggregation tables, and other artifacts to satisfy the management community and pretty soon, the model is negatively affected by the diverging requirements. To understand why this is so, let's review what their respective focus is.

OLTP database models focus on:

  • A detailed view
  • Transaction orientation
  • The current state of an object
  • Serve the operational user
  • Serve a business process

OLAP database models focus on:

  • Aggregated, summarized, and statistical views
  • Analysis orientation
  • A historical view of an object
  • Serve the management user
  • Serve undefined ad hoc processes

Armed with that understanding, it becomes clear why those two databases folded into one are neither a well performing OLTP nor a well performing OLAP support. Another observation is that automatic handling of de-duping of records and dealing with survivorship is greatly impeded when the OLTP also contains myriads of historical information. In a snapshot database, it is relatively easy to merge two person records for example but in a historical context, this becomes almost impossible.

So, what is the solution should you have slipped into this situation? It is actually quite easy. Go offload the analytical reporting functionality to a specially created data warehouse for just that purpose. The steps to this are straight forward and involve the creation of the data warehouse based on the need for the reporting, the creation of the ETL processes to bring the appropriate transactional data over on a daily basis, and the creation / modification of the reports. It might be advantageous to use an Operational Data Store (ODS) as an intermediate hop. This is then followed by cleaning up and removing all the introduced disturbances to the OLTP database.