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.
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).
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.
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|
Power Pivot causes great confusion. This simple 2-minute overview completely de-mystifies Power Pivot and its associated technologies.
This article describes a new way of working with Excel called “modern data analysis” enabled by the new Get & Transform and Power Pivot tools.
This article explains the difference between a regular Excel pivot table and the new OLAP pivot table used by Power Pivot.
This lesson discusses the Power Pivot window and how it differs from the regular Excel window. A sample file is included.
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.
Tables are related to each other using primary and foreign keys. This article explains how Excel uses them in Power Pivot and Get & Transform.