INTHEBLACK April 2022 - Magazine - Page 72
20
WORK SMART
// E XC E L YO U R S E L F
YE ARS
STORY NEALE BLACKWOOD CPA
INTRODUCING
THE EXCEL
DATA MODEL
OF E XC
YOUR SEELLF
relationship between each Dimension table
and the Fact table. Relationships use unique
codes to relate the tables together.
EXCEL HAS DRAMATICALLY IMPROVED ITS DATA HANDLING
CAPABILITIES OVER THE PAST DECADE, INCLUDING THE DATA MODEL.
P
ower Pivot is part of the Excel Data
Model. It allows you to report on
multiple tables at once and create
special calculations called Measures. Measures
extend what calculations are possible within
Excel’s PivotTable reports, and are written
in a language called DAX (Data Analysis
Expressions).
The Data Model, Power Pivot and DAX are a
huge topic. In this article I will introduce some
of the basic features, which are still extremely
powerful and flexible. Power Pivot and DAX
are also part of Power BI. Anything you learn
in Power Pivot can be applied to Power BI.
LOADING POWER PIVOT
To see if you have Power Pivot, click the File
tab and then Options (bottom left). Click Addins on the left side. In the Manage: Drop Down
(bottom of dialog) select COM Add-ins and
click Go. Make sure Microsoft Power Pivot for
Excel is ticked and click OK. Power Pivot has
its own tab.
01
72 ITB April 2022
Neale Blackwood CPA runs A4 Accounting,
providing Excel training, webinars and consulting
services. Questions can be sent to a4@iinet.net.au
EXAMPLE
We will use four Excel tables, one each for
Sales, Dates, Products and Customers. All four
tables have been defined as tables using the
Format as Table option on the Home ribbon.
TERMINOLOGY
In Excel tables, we refer to “columns” and
“rows”. In databases, these are called “fields”
and “records”, respectively.
There are two common table types in the
Data Model.
• Fact or Data table: This table tends to have
the largest number of records, and it may
be transaction-based. This table typically
has the values you want to report on. In
our case, the Sales table is the Fact table.
• Dimension or Lookup table: This type of
table has fewer records and is related to a
specific subject. Each record is dedicated
to a single item and has a unique identifier.
The Dates, Customers and Products tables
are all Dimension tables. There is typically a
02
LOADING THE TABLES
Click a cell in the Sales table. In the Power
Pivot tab, click the Add to Data Model icon. This
opens the Power Pivot window. This window can
stay open while you continue to work in Excel. If
you have two screens, you can have Power Pivot
on one screen and Excel on the other. Repeat
this step for each of the other three tables.
In the Power Pivot window in the Home tab,
click the Diagram View icon (right-hand side
of tab). This shows a field (column) listing for
each table (see Figure 01).
We need to create a relationship between
each Dimension table shown at the top in
Figure 01 and the Sales table shown at the
bottom. To create a relationship for the Dates
table, use the mouse to click, hold and drag
the Dates name from the tblDate listing to the
Dates name in the tblSale listing. Repeat this
step for the Product ID field in tblProduct and
the Customer ID field in tblCustomer.
Dimension tables are typically arranged
above the Fact tables. Figure 01 shows the
view after the relationships are created.
Because we have a Dates table (also known as
a Calendar table), we need to define it as a Dates
table. This makes many date-based calculations
automatic in Power Pivot. In the Diagram View,
click the tblDate table, click the Design tab and
click the Mark as Date Table icon. Make sure the
Dates field is selected and click OK. Now that
we have relationships between the tables, we