Paperback(Second edition)
-
SHIP THIS ITEMIn stock. Ships in 6-10 days.PICK UP IN STORE
Your local store may have stock of this item.
Available within 2 business hours
Related collections and offers
Overview
Product Details
| ISBN-13: | 9781615470532 |
|---|---|
| Publisher: | Holy Macro! Books |
| Publication date: | 06/01/2018 |
| Edition description: | Second edition |
| Pages: | 186 |
| Product dimensions: | 8.20(w) x 10.80(h) x 0.50(d) |
About the Author
Read an Excerpt
CHAPTER 1
Concept: Introduction to Data Modelling
The data modelling engine that is used inside Power Pivot for Excel is the same one used in Power BI. Data modelling is not a term that is often familiar to business users as it is normally the domain of IT BI professionals. But this is no longer the case, thanks to the introduction of Power Pivot for Excel and Power BI.
What Is Data Modelling?
Data modelling is the process of taking data from various sources; loading, structuring, and relating data logically to other data; and enhancing, embellishing, and generally preparing the data for use. The objective is to be able to use the data without having to write a custom query every time you want to look at a different subset of data.
The data modelling process includes:
• Determining the optimal structure and shape of the source data to analyse, including whether to bring in all the data, full data, or summary data.
• Loading the data from the source into the data model (Power Pivot for Excel in this case).
• Defining the logical relationships between the various tables (which is similar to what you do with VLOOKUP() in Excel, except the data stays in the source table in Power Pivot).
• Defining data types (e.g., specifying whether a column of data is numeric or a column of currency values or a column of text fields).
• Creating new insights from the source data so that you can analyse concepts that don't exist natively in the source data but that can be calculated or created inside the data model. For example, if you have a table of transactional data with cost price and sell price, you can extend the data model to include calculations for margin, margin percentage, etc., even though these concepts are not explicitly in the source data. Once you have modelled these new facts in the data model, they can be reused over and over by people using your workbook.
• Giving meaningful names to your new business insights (i.e., to your measures).
When you learn the DAX language and join your tables of data in Power Pivot for Excel, you are actually learning data modelling. The term data modelling can be a little bit scary, but there is no reason to be concerned. By the time you have finished this book, you will be well on your way to being an accomplished data modeller using Power Pivot. Just use the techniques covered in this book and keep in mind that what you are actually doing is learning to be a data modeller. Having said that, however, I should also point out that there is a big difference between being able to do it and being an expert. Becoming an expert takes years of practice, experience, and lifelong learning.
Pivot Tables vs. Power Pivot
Some people wonder what the difference is between Power Pivot and pivot tables, so I'm going to start by explaining. Read on, and you'll have it sorted out in no time.
What Is a Pivot Table?
A pivot table is a summarisation and visualisation tool. The job of a pivot table is to connect to a data source and create on-the-fly totals and subtotals to help you and others make sense of data. The larger the set of data and the more granular the data, the more useful a pivot table becomes. Because pivot tables are embedded right inside Excel, with them you get all the other benefits of Excel as well.
Data Sources for Pivot Tables
Historically, there have been two main types of data sources that you can connect to with a pivot table: flat tables and data cubes.
Connecting to a Single Flat Table
To connect to a single flat table inside Excel, click in the table, select Insert, PivotTable, and off you go. There are some limitations with this approach, however:
• It is very common to have to do a lot of VLOOKUP()s (or similar operations) to be able to join data from different data sources into a single flat table.
• Excel has a 1 million row limit. In fact, though, if you are using lots of VLOOKUP()s in a single flat table, you will reach performance limits well before you ever hit 1 million rows.
These two issues have historically prevented Excel from being a scalable BI tool. But Power Pivot changes that, as you'll see in a few moments.
Connecting to a Data Cube
A less common but very powerful use of pivot tables is to connect directly to a reporting cube such as a SQL Server Analysis Services multidimensional cube directly from Excel. Many large enterprises have multidimensional data cubes available for reporting. Allowing Excel users to connect directly to a cube and use a pivot table for reporting is super easy and convenient. But this is a relatively rare use case compared to the general use of Excel and the more common single-table use of pivot tables.
Enter Power Pivot
Power Pivot doesn't change anything about pivot tables, but it changes everything when it comes to the data that pivot tables connect to. Power Pivot adds a third (and, in my view, the best) method of connecting to source data. Excel has limited ability to manage large sets of data for reporting purposes, whereas Power Pivot has no theoretical database size limit.
Power Pivot is a data modelling tool that allows you to prepare your data in a way that pivot tables can use. Data modelling is the process of preparing data so it can be used in reporting tools (such as a pivot table) without the need to write new database queries every time.
Power Pivot is a Microsoft SQL Server Analysis Services tabular database that is bundled with Microsoft Excel via a COM add-in. Excel manages Power Pivot databases, so the experience is seamless and transparent to the end user. You can use a user interface in Excel to build Power Pivot databases directly inside Excel.
Power Pivot allows you to:
Import data from many different data sources.
Logically join separate tables of data together so the data works together without the need for VLOOKUP() formulas.
Enhance the underlying raw data so that you can create new derived concepts (measures) from that data. For example, if the source data has sell price and cost price, it is possible to create the measures Margin $ and Margin % and make them available for use in reports.
Assign appropriate business names to the measures.
Apply to the data business formatting that will be applied throughout the data model.
Once a Power Pivot data model has been built and configured, the end user can use the data repeatedly to quickly build multiple pivot table reports inside Excel.
CHAPTER 2Concept: Loading Data
Before you can start to write DAX and use Power Pivot, you need to load some data. Power Pivot always loads a complete copy of the source data into the data model as the first step in the process. Once it's loaded, you can share your workbooks with others, and there is no need for anyone else to have direct access to your source data.
When you load data, you have to decide which data to import, including which tables, which columns in each table, and also what "shape" the data should be when imported. In the following "Here's How," you will simply load data that has been prepared for you. But you need to be aware that the process of deciding which data to load is an important part of the data modelling process. This decision has been made for you for this book.
Preparing for Data Load
You can download a copy of the sample AdventureWorks database used in this book from http://xbi.com.au/learndax. You should download the database now, unzip it, and place it in a location that is easy for you to find.
Here's How: Enabling Power Pivot in Excel
The Power Pivot menu may or may not be visible in your version of Excel. Follow these steps to see if it is visible and, if not, enable it:
1. Open a new Excel workbook. Look for the Power Pivot tab in the ribbon, shown in the figure below. If you see it, you don't need to follow the rest of these steps.
2. If you don't see the Power Pivot tab, select File, Options, Add-Ins.
3. Scroll to the bottom of the window and select COM Add-ins from the Manage list. Then click Go.
4. In the COM Add-ins dialog that appears, check the Microsoft Power Pivot for Excel check box and then click OK.
What if I Can't Find the Power Pivot Add-in? If you are using Excel 2013 or later and you can't see the Power Pivot add-in, then I have some bad news for you: Your version of Excel does not include Power Pivot, and you will need to purchase a different version of Excel to get it. For more information about Power Pivot versions, go to http://xbi.com.au/versions.
5. On the PowerPivot tab, click Manage.
Take a moment to look at the Windows taskbar, shown in the figure below. Hover your mouse over Excel in the taskbar and notice that there are now two separate windows: the traditional Excel window (see #1 below) and the Power Pivot window (#2).
Note: Power Pivot is a separate application that is completely embedded inside Excel. Throughout this book, I often tell you to switch between Excel and Power Pivot. When I say this, I mean you should switch between the two windows shown above. If at any time you can't see the Power Pivot window because it is not open, you can open it by going to the Power Pivot tab in Excel and clicking Manage.
Direct Load to Power Pivot or Power Query?
Power Pivot for Excel has always allowed you to load data directly into the Power Pivot data model from within the Power Pivot window, as shown below.
Since Excel 2016, Microsoft has embedded Power Query directly in Excel, under the Data, Get & Transform Data menu.
Note: At this writing, there are several software builds of Excel 2016 available, and your version may look different to the image above. If your Data tab looks different to what is shown here, just take a few moments to look more closely at it and become clear about where the Power Query features are located.
This change of name to Get & Transform Data is unfortunate, in my view, and I prefer to use the name Power Query.
Note: I use the term Power Query in this book, but remember that you launch Power Query from the Get & Transform Data menu.
With the introduction of Power Query, you have a choice to either load data directly into Power Pivot using the legacy Power Pivot approach or use Power Query (from the Get & Transform Data menu) instead. There are a couple reasons to prefer using the newer Power Query approach over the legacy approach:
• When you load data using the legacy Power Pivot approach, it is not possible to change a data source. For example, if you load data into a table in Power Pivot from a spreadsheet and later decide to source the data from SQL Server instead, it is not possible to simply repoint the table to the new and different type of data source. Instead, you must first delete the table and then re-import the data from the beginning. This doesn't sound so bad at first, but it means that all the relationships need to be re-created and that any measures stored in the table will be lost — and these are big problems.
• Power Query allows you to easily manipulate data during load in ways that are not possible using the legacy Power Pivot approach.
Note: As of this writing, loading data using Power Query takes longer than loading the same data using the legacy Power Pivot approach. It is not so long that it is unworkable, but it can take 15 to 20 seconds longer to load for some reason.
The next "Here's How" section describes how to load data using the legacy Power Pivot approach. I suggest you read it and follow along on your machine so you know how. For the rest of this book (and when you start building your own data models), I recommend that you use the Power Query data load approach, which is described in a later "Here's How" section.
Here's How: Data Load Using Power Pivot
This section describes how to load the following tables from the AdventureWorks Access database using the legacy Power Pivot data load approach:
• Sales
• Products
• Territories
• Calendar
• Customers
Follow these steps to load data into a workbook for use in Power Pivot:
1. In the Power Pivot window, select Home, From Database (see #1 below), From Access (#2).
2. Browse to the location of the sample database you downloaded and unzipped earlier in this chapter and then click Next.
3. Accept the default option in the Table Import Wizard dialog (as shown below) and then click Next.
4. Select the five views at the bottom of the list by placing a check mark in the box next to each one. (Note the different icons for queries/views and for tables.)
5. Click Finish, and the wizard imports your data.
When you close the Table Import Wizard, you see the five tables you have just imported in the Power Pivot window. There should be five new tabs, one for each of the tables you just imported. Each of the tables is a complete copy of the data you imported from the source files (an Access database, in this example). You don't need the source files again until you are ready to refresh the data — typically when the data changes at some time in the future. This is one of the many great things about Power Pivot: You can simply refresh the data when the data changes, and your workbooks are updated with the new data.
Here's How: Data Load Using Power Query
This section describes how to load the following tables from the AdventureWorks Access database using the Power Query data load approach:
• Sales
• Products
• Territory
• Calendar
• Customers
Then you will prepare these tables for use in Power Pivot.
Follow these steps to load data into a workbook for use in Power Pivot using Power Query:
1. If necessary, open another new blank Excel workbook.
2. Click Data (see #1 below), Get Data (#2), From Database (#3), From Microsoft Access Database (#4).
3. Browse to the location of the sample database you downloaded and unzipped earlier in this chapter and then click Import.
4. Click the Select Multiple Items check box (see #1 below) and select the five views at the top of the list by placing a check mark in the box next to each one (#2).
5. Click Load (#3).
The data loads directly into the Power Pivot data model.
Note: There are different icons for queries/views and for tables. The icons at the top of the list in the figure below (e.g., next to Calendar and Customers) indicate views/queries, and the icons at the bottom (e.g., next to Budget and BudgetPeriod) indicate tables.
There is also a Load To option, as shown in the image below. If you select this option, you have a number of choices for loading the data to the data model, to an Excel table, or both. You can do the exercise again if you want to see how this works.
Tip: The sample data in this book has been well prepared for learning how to use Power Pivot. However, you should not assume that any other source database has the correct table structure for Power Pivot; in fact, few databases do.
Note: If you click the Edit button instead of Load (as shown in the image above), you launch into the Power Query Editor, where you can transform the data prior to loading it into Power Pivot. Power Query is beyond the scope of this book, but I have a comprehensive online training course specifically designed to teach you how to use this powerful tool. You can learn more about that training course at http://xbi.com.au/powerquerytraining.
Also notice that the tables at the bottom of the import navigator shown above have names like dimProduct and fctSales, where dim indicates dimension, and fct indicates fact. It is very common for database tables to have prefixes like this. Business users can think of a dimension table as a lookup table and a fact table as a data (or transactions) table. The fact that there are two different types of tables — lookup tables and data tables — is a very important concept in Power Pivot, and you will learn a lot more about this as you work through this book.
It is best practice for Excel users to remove the dim and fct prefixes from the table names before importing these tables into Power Pivot. These prefixes have meaning to IT folk and help identify the type of table, but given that these table names will be visible to business users who use your Power Pivot reports, it is a good idea to remove the prefixes during import.
After completing the import, you should see the Queries & Connections pane appear, confirming that the data has been loaded into tables in Power Pivot.
Using Your Data in Power Pivot
Regardless of whether you used Power Query or Power Pivot to load data, you can see the loaded data by navigating to the Power Pivot window. In Excel, go to the Power Pivot menu and click Manage, or simply find and select the Power Pivot window in the taskbar (if it is already open). After navigating to the Power Pivot window, ensure that you are in Data view by selecting Data View on the ribbon in the Power Pivot window (see #1 below). There is a second view of the data (Diagram view; #2 below) that shows the relationship between tables. (More on that shortly.)
(Continues…)
Excerpted from "Supercharge Excel"
by .
Copyright © 2018 Tickling Keys, Inc..
Excerpted by permission of Holy Macro! Books.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.
Table of Contents
Supercharge Excel,
Introduction,
1: Concept: Introduction to Data Modelling,
2: Concept: Loading Data,
3: Concept: Measures,
4: DAX Topic: SUM(), COUNT(), COUNTROWS(), MIN(), MAX(), COUNTBLANK(), and DIVIDE(),
5: Concept: Filter Propagation,
6: Concept: Lookup Tables and Data Tables,
7: DAX Topic: The Basic Iterators SUMX() and AVERAGEX(),
8: DAX Topic: Calculated Columns,
9: DAX Topic: CALCULATE(),
10: Concept: Evaluation Context and Context Transition,
11: DAX Topic: IF(), SWITCH(), and FIND(),
12: DAX Topic: VALUES() and HASONEVALUE(),
13: DAX Topic: ALL(), ALLEXCEPT(), and ALLSELECTED(),
14: DAX Topic: FILTER(),
15: DAX Topic: Time Intelligence,
16: DAX Topic: RELATED() and RELATEDTABLE(),
17: Concept: Disconnected Tables,
18: Concept: KPIs,
19: Concept: Multiple Data Tables,
20: Concept: Cube Formulas,
21: Moving from Excel to Power BI,
22: Next Steps on Your DAX Journey,
Appendix A: Answers to Practice Exercises,
Index,