Essential to Expert Skills progression

Excel tutorials

The only Excel books you will ever need

  • The only constantly updated Excel 365 titles more…
  • Thoroughly covers Power Pivot and Power Query more…
  • Covers business intelligence and OLAP features more…
  • Simple, easy, understandable, 2-page lessons more…
  • Available as both printed books and e-Books more…
  • Used by schools, colleges and universities  more…
  • Available for 365, 2019, 2016 and Mac versions more…

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.

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

A simple OLTP database showing relationships
Click on the image above to see it more clearly

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).

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.

 

Learn Excel 365 Expert Skills with The Smart Method (fourth edition) - Front cover

This lesson is excerpted from the above book. 

This is the only up-to-date Excel book currently published and includes an entire session devoted to the new Dynamic Arrays features.

It is also the only book that will teach you absolutely every Excel skill including Power Pivot, OLAP and DAX.

Lessons in Session 12

Session Twelve: Power Pivot, Data Modeling, OLAP and Business Intelligence
Lesson 12‑1: Understand data model memory requirements
Lesson 12‑2: Install the Power Pivot add-in
Lesson 12‑3: Add tables to a data model
Lesson 12‑4: Understand the Power Pivot window
Lesson 12‑5: Add a relationship to a data model using Power Pivot
Lesson 12‑6: Use an OLAP pivot table to analyze data residing in a data model
Lesson 12‑7: Understand OLAP pivot tables
Lesson 12‑8: Understand modern data analysis
Lesson 12‑9: Create a data model directly from a relational database
Lesson 12‑10: Understand many-to-many relationships
Lesson 12‑11: Understand OLTP database design
Lesson 12‑12: Understand OLAP database design
Lesson 12‑13: Understand how to resolve many-to-many relationships
Lesson 12‑14: Create a snowflake data model from a relational database
Lesson 12‑15: Convert a snowflake data model into a star data model
Lesson 12‑16: Hide, remove and rename data model columns
Lesson 12‑17: Add a calendar table to a data model
Lesson 12‑18: Format values in a data model
Lesson 12‑19: Understand MDX queries and OLAP pivot table limitations
Lesson 12‑20: Use the CUBEVALUE function to query a data model
Lesson 12‑21: Convert CUBEVALUE functions to include MDX expressions
Lesson 12‑22: Create an asymmetric OLAP pivot table using Named Sets
Session 12: Exercise
Session 12: Exercise Answers

Related Articles

Understand modern data analysis

Excel 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.

Understand OLAP Pivot Tables

Excel OLAP pivot tables

This article explains the difference between a regular Excel pivot table and the new OLAP pivot table used by Power Pivot.

Understand the Power Pivot window

Excel Power Pivot window

This lesson discusses the Power Pivot window and how it differs from the regular Excel window. A sample file is included.

OLAP Star Schema

Excel data model 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.

Share this article

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

Leave a Reply

Your email address will not be published.

eleven − five =

Cross-lesson hyperlinks

In order to use all cross-lesson hyperlinks you will need to download the full e-book. The Expert Skills e-book contains 253 short lessons that will teach you everything there is to know about Excel.

When you download the e-book you’ll be able to access every lesson,  online or offline, on all of your devices, using Amazon’s free advanced e-book reader