Blogging all things data

The difference between a database and a data warehouse? OLAP 

The difference between a database and a data warehouse? OLAP 

On the very surface of it, it might not seem like there is a difference between a database and a data warehouse. However, don’t be tempted to use those terms interchangeably.  There is a very important distinction between them that is very relevant to anyone looking to get the most out of their analytics platform. A major part of the difference lies in whether the architecture that is implemented is OLTP or OLAP.

Contents

  1. The Difference Between a Database and Data Warehouse
  2. Your Operational Databases are OLTP
  3. Your Analytics Data Warehouse is OLAP
  4. The Cube is an OLAP Aggregation Engine
  5. Know the Power of OLAP

The Difference Between a Database and Data Warehouse

A database could be described as a medium to store data, whether it be for your transactions (Online Transaction Processing or OLTP) or to support your analytics (Online Analytical Processing or OLAP). The key difference is in design, the OLTP design principles are centred around getting data normalised and written to the database as quickly as possible. The Data Warehouse is designed in such a fashion to get data out as quickly as possible to service business reporting for creating aggregated dashboards.

Why does this matter? Having your business systems inputting customer data, processing requests and taking actions is paramount to operations. Getting data into these systems should never stop or slow down. However, the nature of their design makes this very hard, which is where the BI Platforms come into the picture. They allow your business to analyse events, measure your business performance without ever hampering your existing business processes adversely. 

an abstract visual representation of an oltp database

Your operational databases are OLTP databases

Modern businesses utilise a variety of enterprise systems which enable the day-to-day fundamentals to keep rolling. Resource planning, customer relationships, transaction management and other in-house, operational systems are used by staff daily. Throughout the course of the day, these systems generate or alter large numbers of data points within their own, individual databases. The focus of these operational databases is speed, accuracy and dependability meaning they are built using a data processing approach which facilitates this: OLTP.   

The success of a business is directly linked to the consistent successful operation of these data transactions. When it relates to updates to payment or inventory data, it is clear why there is no room for any kind of error or uncertainty in the numbers. Because of this, OLTP is by its nature “atomic”, meaning that a transaction either entirely succeeds or entirely fails as one unit of work. This means that it cannot exist in an intermediate or unfinished state, which might complicate and introduce ambiguity into a database.

The benefits of an OLTP database

A further strength of OLTP is the speed at which the data can be queried and updated. Because these transactions are simple and direct, they can be done without much demand on system resources. Additionally, these databases are highly normalised, greatly reducing the potential for data redundancy as well as anomalies in data updating, insertion or deletion.  

A combination of these databases geared towards reliable and frequent data transactions is the circulatory system which keeps an enterprise running. However, due to this particular focus the data is often stored in a variety of separate, proprietary data formats and is not geared towards complex, sweeping data queries which enable the type of calculation required for advanced analytics.  

Your analytics data warehouse is OLAP databases

When it comes to extracting insights across the entirety of an enterprise data landscape, the OLTP design is simply not up to the challenge. It cannot easily join two different systems together to form a more holistic view of a piece or many pieces of data, on top of this the simple act of asking a semi-complex question to a singular OLTP database could result in adverse performance for the end users, and slowing down your business.

To overcome this limitation a data warehouse brings together the data from your OLTP system(s) and brings them together in a more conformed and de-normalised manner to enable your business to see further insights into your data assets in a singular view.

The benefits of an OLAP system

OLAP capability, as embodied by a data warehouse, goes beyond simple querying and reporting. It involves the integration of the numerous enterprise source systems into a large historical dataset which can be processed with advanced queries. The main advantage that this offers is its multi-dimensionality, meaning that elements such as inventory, location and time are combined in ways which are simply not possible in an OLTP database geared towards granularity.   

The Cube is an OLAP Aggregation Engine   

The concept which best represents the aims of OLAP data capability is the cube. It is essentially an abstraction of the ways in which the data is aggregated and projected across multiple dimensions. As mentioned above, one dimension of the cube could represent inventory, another, the different branch offices with the final one representing time.

an abstract cartoon representation of an olap data cube

Because it is structured in this relational and hierarchical way, it can then be manipulated by analytical operations which can generate the highest-level insights with an immediate ability to drill down or to change the dimensionality of the entire data set.   

A cube can be sliced in order to immediately increase the focus and zero in on a specific point of issue. An example would be to exclude all store locations except for one. Likewise, dicing involves reducing the data set down across a number of dimensions, such as excluding particular years, products and locations in order to zero in on a perceived data anomaly. At that point, a drilldown would make visible a finer grain of data to continue the exploration.  

At any point, when the cube has been appropriately cut, the data for that particular section can be rolled up, meaning more advanced calculation and formulas can be applied specifically to that subset of data. This may involve pivoting the entire section in order to view the data with a completely different focal point.   

Know the power of OLAP   

If you are a business analyst interacting with data, think about the difference in what “interactive” means when applied to OLTP as opposed to OLAP. Within an OLTP collection of databases, it would mean monitoring individual data points in a de-contextualized way presented in a granular, static list. It may provide answers to a highly specific and localised question but will not enable you to explore larger trends and truly understand why these trends exists. OLAP capability in a data warehouse is what truly enables interactive data exploration, seeing how the data fits together at the largest scale, across multiple dimensions allowing the truly impactful strategic-level business decisions to be made.

If you are looking to implement an OLAP-based data warehouse for high performance business analytics within your business, BizData offers a solution called Data Discovery OnDemand which brings together the pipelines of all of your operational systems and in-house apps.