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