The Smart Method Logo

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

Star Schema data model

This lesson is excerpted from the Expert Skills book

This lesson as it 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 is the first introductory lesson in the 3D Maps session.  Students do not learn about data models in this session but use 3D maps without a model.  This lesson is designed to give a background understanding of data models and their relationship with 3D Maps even though students do not yet understand how to create their own models using Get & Transform and Power Pivot (that comes later in the book).

The self-service BI Suite

Since 2010, some versions of Excel (those aimed at enterprise users) were able to install Microsoft’s “Power” suite of Business Intelligence (BI) tools. Collectively they are often referred to as the Self-Service BI suite.

The suite comprised of four components:

  • Power Pivot
  • Power Query
  • Power Maps
  • Power View

Two of these products (Power Pivot and Power Query) were tools that could only be used by specialized IT staff (with extensive data modeling skills) to create data models.

The other two (Power View and Power Maps) were simple easy-to-learn tools that created visualizations of data within a data model. Excel users could also use a data model as the source data for a pivot table.

The vision behind self-service BI was that Excel users with no specialized data modeling skills could make use of the data models created by their IT department to create sophisticated visualizations and reports faster and more easily than was possible with Excel alone.

In 2013 the Power Query tool was added to Excel and re-named Get & Transform.  The Expert Skills book has an entire session (Create Get & Transform Queries)  that will teach you everything there is to know about Get & Transform. 

In 2016 Power Maps was added to Excel and re-named 3D Maps. This is the tool that you will learn in this session.

In 2019 Power Pivot was added to Excel. The Expert Skills book has an entire session (Power Pivot, Data Modeling, OLAP and Business Intelligence) devoted to Power Pivot and the DAX language (used to create more sophisticated data models) is covered in another session: An introduction to DAX.

Power View no longer exists as a self-contained product (or add-in) and cannot be added to current versions of Excel. Instead, Power View has evolved into one component of a new desktop and cloud application called Power BI.

You can learn and use 3D Maps without learning data modeling

The data modeling skills that are taught in in the Power Pivot, Data Modeling, OLAP and Business Intelligence Expert Skills session are very challenging to master.

In recognition of this the 3D Maps feature has been designed so that users with no data modeling skills can use 3D Maps by using a single Excel table (or range) as a data source.

Behind the scenes, 3D Maps automatically converts the table (or range) into a one-table data model. The 3D Maps tool then uses this data model as source data.

If you later create advanced data models everything in this session is equally relevant

Data modeling is a very challenging subject that many Excel users may decide not to invest time learning.

If you decide to progress to the later lessons in the Expert Skills book: Session Twelve: Power Pivot, Data Modeling, OLAP and Business Intelligence and Session Thirteen: An introduction to DAX, you will learn some very advanced data modeling skills.

While well-designed data models are very difficult to create, they are amazingly easy to use. All of the skills you will learn in this (3D Maps) session will work in exactly the same way when powered by a hand-crafted multi-dimensional data model.

3D Maps can cause “out of memory” errors

3D Maps needs to hold a data model (that is a duplication of the source data) in your computer’s memory.

Data models can require a lot of memory (depending upon the size of the source data).

If you encounter crashes or out-of-memory error warnings while working through this session there’s a lesson in the Expert Skills book: Understand data model memory requirements that explains the memory issue (and how to solve this problem) in depth.

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

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.

OLTP database example

Understand OLTP database design

A Power Pivot data model is an OLAP database. Business databases are OLTP databases. This article discusses how OLTP databases are structured.

Leave a Reply

Your email address will not be published.

4 − three =