The Smart Method Logo

Publishers of the world’s most comprehensive and up-to-date Excel tutorials

OLTP database example

Understand OLTP database design

This lesson is excerpted from the Expert Skills book

This lesson is excerpted form the book: Learn Excel 365 Expert Skills with The Smart Method: Fourth Edition: updated for the Jul 2020 Semi-Annual version 2002. 

This lesson is contained in the session: Power Pivot, Data Modeling, OLAP and Business Intelligence.

OLTP databases

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.

A simple OLTP database showing relationships

The above schema example comes from an OLTP database. You can see a many-to-many relationship between the Orders and Product tables (the Expert Skills book explains many-to-may relationships in: Lesson 12 10: Understand many-to-many relationships).

OLTP database illustration

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.

Additional information (shown in the sidebar of the Expert Skills book)

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.

 

This lesson as shown in the Expert Skills e-book Kindle reader for PC

Lesson 12-11 Understand OLTP database design

Available for Excel 365, 2019 and 2016 as both an e-book and paper printed book.

Share this article

Share on facebook
Share on twitter
Share on linkedin
Share on stumbleupon
Share on email

Related Articles

Understand modern data analysis

Understand modern data analysis

This article describes a new way of working with Excel called “modern data analysis” enabled by the new Get & Transform and Power Pivot tools.

OLAP Star Schema

Understand OLAP database Design

A Power Pivot data model is an OLAP database. OLAP databases can be modeled as a Snowflake or Star schema. This article explains both designs.

Leave a Reply

Your email address will not be published.

5 × 4 =