Understand OLTP database design

This article discusses the two types of database commonly used by business. The OLTP database type is used for transactional data and the OLAP database type for reporting.

Excel calls the OLAP type of database a data model. When working with Power Pivot you will spend much of your time converting OLTP databases into OLAP databases.

If you have no understanding of OLAP I recommend that you begin with my Excel Power Pivot 2-minute overview article first. That puts the whole subject of OLAP into context and then the lesson below will make a lot more sense.

The article below is an unedited lesson from one of the 68 short focused lessons in my Excel Expert Skills book/e-book that seeks to empower any Excel user with a complete mastery of the skills needed to create perfect Excel data models from an OLTP source.

Leave me a comment if you find the article useful.

Last Updated: September 7, 2020

note

OLAP, MOLAP, HOLAP and ROLAP

You don’t need to understand the difference between ROLAP, MOLAP and HOLAP to use Power Pivot. You may find this terminology used in articles about Power Pivot however, so I thought I’d give you an overview of what they mean.

Power Pivot uses a tabular data model. An SSAS (SQL Server Analysis Services) server supports three models: tabular, multidimensional and hybrid. The newer tabular model was introduced by Microsoft in 2012 (the multidimensional model is an older technology). Excel uses the newer tabular model (that can be simply exported to an SSAS server if required).

OLAP (OnLine Analytical Processing) is an acronym that is used to describe a database (data model) that is used to interactively analyze data from multiple perspectives. In the Excel implementation a tabular data model is analyzed using an OLAP pivot table.

To differentiate between the tabular and multidimensional models, the term ROLAP (Relational OLAP) and MOLAP (Multi-dimensional OLAP) can be used.

Another type of OLAP database is sometimes referred to as HOLAP (Hybrid OLAP). This refers to an OLAP database that combines ROLAP and MOLAP features.

So which OLAP model is best? IT professionals could spend hours arguing the answer, but Microsoft have stated:

“For new projects, we generally recommend tabular models”.

In this book I use the more generic term: OLAP though it would also be correct to use the term ROLAP as Power Pivot uses the tabular model.

Lesson 12 11: Understand OLTP database design

OLTP database design

A database that is used to support day-to-day business transactions is called an OLTP (OnLine Transactional Processing) database.

OLTP relational databases are designed around the need to quickly perform four actions: Create, Retrieve, Update and Delete. The anagram CRUD is often used for these four requirements.

Imagine that your computer application manages employee records (stored in a database). If an employee’s details change you would need to first Retrieve (find) the employee’s record. You’d then have to Update the record with the new information.

If an employee left the company, you might want to Delete their record. If a new employee was then recruited, you’d need to Create a new employee record.

OLTP database design schema example

The above schema example comes from an OLTP database. You can see a many-to-many relationship between the Orders and Product tables (you learned about many-to-many relationships in: Lesson 12‑10: Understand many-to-many relationships).

The Orders table contains values that relate to items you’d typically find in the header area of a paper order (such as OrderDate).

The OrderDetails table contains one row for each item ordered. These would be the values defined in the body area of a paper order (first order item, second order item, etc).

This schema describes a perfectly designed OLTP database, but it is not useful for an OLAP database (a Power Pivot data model is a type of OLAP database).

A Power Pivot data model is an OLAP database

The data model that you create in Power Pivot is a copy of data (from one or more data sources) at a given point in time. This is sometimes called a Snapshot of the data (as a photograph does not change after it has been taken).

The data model is a special type of database that has to be designed around the need to quickly analyze data. This type of database is called an OLAP database (OnLine Analytical Processing).

Terminology confusion

Current Excel documentation refers to an OLAP database hosted by Excel as a Data Model.

If the same model is created using Power Pivot, it is sometimes called a Power Pivot Model.

OLAP databases are also often referred to as an OLAP data source.

SSAS data models that use the Multidimensional Model (see sidebar) are often referred to as OLAP Cubes.

You’ll also find the term OLAP Cube referred to extensively in OLAP-related discussions as multi-dimensional analysis concepts are often explained using the analogy of a three-dimensional cube (a little like a Rubik’s cube) with aggregated values in each cell.