Tennick A. Practical PowerPivot & DAX Formulas for Excel 2010
Подождите немного. Документ загружается.
24 Practical PowerPivot & DAX Formulas for Excel 2010
window, this reopens it. In addition, you could also try normal Windows methods for
switching windows. For example, you can click either window on the task bar or press
alt-tab—but be careful; you can have multiple Excel and PowerPivot windows open at
the same time, so make sure you select the right one.
Once you’re in PowerPivot, there are two tabs labeled Home and Design leading to
two ribbons. If you’ve not done any work yet in your PowerPivot window, many of the
buttons in the two ribbons are disabled (and later you may see a third Linked Table
ribbon). The formula bar is also disabled and there are no tabs in the window. You’ll see
the first tabs shortly.
The PowerPivot window (from now on, I’ll often simply call it PowerPivot) is
where you assemble the data you wish to analyze. It’s where you start turning data into
intelligence. Mostly, you’ll assemble the data by importing it from another source or
from multiple sources, although you can also copy and paste or link to data. The beauty
of PowerPivot is that you can create real business intelligence and display it in a pivot
table back in Excel, even if your source data is far from perfect. The next few sections
show how to assemble your data within PowerPivot.
There are three different ways to get data into PowerPivot. One, you can import
data from one or more external sources. You do this from the Get External Data
group in the Home ribbon of PowerPivot. Two, from the Clipboard group on the same
ribbon you can paste in data (possibly from an existing Excel list or table) that you’ve
previously copied to your Clipboard. Three, you can link back to an Excel table (a
range or list gets automatically converted into an Excel table) through the Excel Data
group on the PowerPivot ribbon in Excel. The first two methods are available from the
PowerPivot window, and the third method is accessed from the Excel window.
It’s possible to use any of these methods, or to use more than one. It’s also possible
to employ any method more than once, perhaps bringing in data from multiple
disparate sources. In effect, you can consolidate data from heterogeneous sources
into one place, a PowerPivot window. With a few mouse clicks you can have your
very own data mart within minutes—a central repository for all of the relevant
data without any complex Extract, Transform, and Load (ETL) operations. Before
PowerPivot came along, assembling a data mart was quite often a daunting task.
Now you can have data from text files, Access databases, Excel spreadsheets, data
feeds, SQL Server Reporting Services (SSRS) reports, and high-end relational
database management systems such as SQL Server or Oracle or DB2, all joined
together. You can even add to this data from a multidimensional cube hosted in SQL
Server Analysis Services (SSAS)—so you can have multidimensional and relational
data working together, which is quite tricky to do without PowerPivot. And there’s
more! How about adding data from another established PowerPivot for SharePoint
Chapter 2: PowerPivot: Overview 25
workbook, or from your big SQL Server in the sky, SQL Azure? You can even paste
from a Word table.
Let’s work through the steps involved in assembling some or all of this data. You
won’t necessarily have all of the data sources mentioned. In that case, try the ones that
are relevant to you. But in order to follow the steps in this book on your computer,
rather than just read about them, it would be helpful if you have the Northwind
database in either its SQL Server or Access or Excel incarnation. Details of how to
download Northwind for Access or SQL Server are given in the introduction. There
is also, as of this writing, a Northwind data feed that you might use instead. I have
also created an Excel version—again, download details are mentioned earlier. Should
you not have any of these versions of Northwind, it should be relatively easy to adapt
the instructions that follow to your own data sources. Please note that, because of
structure and data changes, you’ll need the pre-Access 2007 copy of Northwind to
follow the examples here (that is, Northwind.mdb, not Northwind.accdb). You can
load Northwind.mdb into Access 2010 or Access 2007 and convert it to Northwind.
accdb—that will also work fine. Just be careful that you don’t save and overwrite any
original Northwind.accdb that you may have.
The data in the Access and SQL Server and Excel and data feed Northwinds is
identical (at least for the tables and columns we’ll be working with), so once you’ve
done the import, the steps in this book should work for everyone. In essence, this means
that you don’t have to have SQL Server—even if the full official name of PowerPivot for
Excel is Microsoft SQL Server PowerPivot for Excel 2010. You do need SQL Server
(in order to have SSAS, or SQL Server Analysis Services) if you wish to view your
PowerPivot pivot reports in SharePoint, using a Web browser, rather than in Excel. You
will also need to enable Excel Services in SharePoint. This book is about PowerPivot
for Excel (where you create everything in the first place) rather than PowerPivot for
SharePoint, where you can deploy the results and make them available through your
SharePoint Web site if you wish. So all you need is Excel 2010, with PowerPivot for
Excel 2010 installed, and preferably one of the four possible Northwinds.
The next four sections demonstrate how to import Northwind data from SQL
Server and Access and Excel and a data feed in turn. You only need to do one of these,
whichever is appropriate to your situation, although for future reference it might be
beneficial to read the other three. If you wish to try more than one, and you have the
relevant Northwinds, please remove the tables from PowerPivot first. Otherwise, you
will end up with tables being duplicated. To delete a table, you right-click its tab and
choose Delete. You need to be in the PowerPivot window, not the Excel one (unless you
are first linking to an Excel table), to import or delete data. Here goes.
26 Practical PowerPivot & DAX Formulas for Excel 2010
Importing from SQL Server
If you’re wondering why we’re going to use Northwind rather than Adventure Works
(the classic sample databases for SQL Server 2005 and 2008) or the newer Contoso
sample database, it’s so that readers who don’t have SQL Server can use an Access,
Excel, or Northwind data feed, and everyone can follow along with the same practicals.
An Access version of Adventure Works is not readily available for download, and an
Excel. version would be pretty big. Northwind contains everything we need to learn
about every aspect of PowerPivot for Excel and its associated DAX language.
If you don’t have SQL Server and the Northwind database, then maybe try the next
section, “Importing from Access,” or the subsequent sections on importing from Excel
or a data feed. For future reference, I recommend that you read this section on SQL
Server anyway, even if you are not in a position to try it just yet. To import some tables
from SQL Server Northwind:
1. Click the From Database button in the Get External Data group of the Home
ribbon in PowerPivot. This opens the menu shown in Figure 2-5.
2. In this menu choose From SQL Server to open the Table Import Wizard. You
first need a Server name and a Database name. Enter the name of your SQL
Server in the Server Name field, and choose Northwind from the Database
Name drop-down. In Figure 2-6, you can see I’ve been lazy and used the dot
shorthand for localhost (in a production environment, it’s good practice to enter
the server name explicitly). It’s a good idea to click the Test Connection button
before you click Next. If the connection fails, you may have to switch from Use
Windows Authentication to Use SQL Server Authentication and enter your SQL
Server login and password (optionally, saving your password; this is particularly
important if you later deploy to PowerPivot for SharePoint). If the connection is
taking too long, for example, if a server is unavailable, you can cancel the operation
Figure 2-5 Choosing an external data source
Chapter 2: PowerPivot: Overview 27
at any time. Note the Friendly Connection Name; this is the name by which the
connection will be known in PowerPivot. By default, it includes your server and
database names—you can change these if you wish.
3. The next dialog is where you choose how to import your data. The default is
to select tables and/or views as your data sources. If you wish to manipulate the
data in any way, you can write your own SQL queries. If you’re an old hand at
relational databases, you may be tempted to denormalize tables by writing inner
joins. With PowerPivot, this is not necessary as some very simple DAX (easier
than the SQL!) will do this for you. Accept the default to choose from a list of
tables and views, as shown in Figure 2-7, and click Next.
Figure 2-6 Entering a server name and choosing a database name
28 Practical PowerPivot & DAX Formulas for Excel 2010
4.
You are now at the Select Tables and Views stage of the Table Import Wizard,
as shown in Figure 2-8. There’s quite a lot going on here. Before we discuss
that, select the check boxes for the Categories, Customers, Employees, Order
Details, Orders, and Products tables. The very top check box allows you to select
or deselect all tables and views en masse. You can sort the tables by clicking the
column header. You can also see the Schema name for your tables and views and
a Friendly Name. If you wish, you can alter the Friendly Name—this is the name
that will appear in PowerPivot and any pivot tables or pivot charts that you create
back in Excel. However, it’s possible to change the name later, in the PowerPivot
Figure 2-7 How to import
Chapter 2: PowerPivot: Overview 29
window by renaming tables. The Select Related Tables button will find all tables
directly related to a currently selected table. This is very handy when your source
database has referential integrity between tables and is complex, or if you’re not
sure about how the tables relate together—maybe there’s a lookup table that
contains essential data that you’re not aware of.
5. If you click the Preview & Filter button, it opens the Preview Selected Table
dialog of the Table Import Wizard (see Figure 2-9), on the currently selected
table. This is so you can check that your data is arranged as you want it. Here,
you can remove columns from the table by unselecting check boxes for the
columns. In addition, you can set up filters and sorting. A filter is the equivalent
Figure 2-8 What to import
30 Practical PowerPivot & DAX Formulas for Excel 2010
of a SQL Where clause, and a sort is the equivalent of a SQL Order By clause.
Filtering and sorting are covered in the next chapter, so for now, cancel out of
the dialog box if you opened it. Please note that if multiple tables are selected,
the Preview & Filter button is disabled. To complete the import of tables,
click Finish.
6. The Importing dialog box (see Figure 2-10) informs you that all six tables have
been successfully imported. All you have to do now is to click Close and you are
returned to the PowerPivot window, which now looks a little different. It contains
a tab for each of the six tables. Before examining the PowerPivot window and its
tables, let’s see how to import the same data from Access, and Excel, and data feed
sources—Access first.
Figure 2-9 Previewing data
Chapter 2: PowerPivot: Overview 31
Importing from Access
If you don’t have the Access Northwind.mdb database (or Northwind.mdb saved as
Nortwind.accdb) and you weren’t able to complete the previous SQL Server import, try
the next section, “Importing from Excel,” instead. However, I strongly recommend that
you read this short section anyway—it may prove useful in the future.
To import some tables from Access Northwind:
1. In PowerPivot, click From Database in the Get External Data group of the Home
ribbon. Select From Access in the resulting menu (see Figure 2-5 in the earlier
section on importing from SQL Server).
Figure 2-10 A successful import
32 Practical PowerPivot & DAX Formulas for Excel 2010
2.
The dialog box that opens is different from that for SQL Server. Instead of
specifying a server and a database, you browse to find the Access database file
(see Figure 2-11). When you click the Browse button, by default it’s only going
to find Access .accdb databases. In order to locate Access .mdb databases, change
the drop-down entry in the Open dialog to Microsoft Office Access 97-2003
Database (*.mdb). Once you’ve found your Northwind.mdb, click Open. You may
want to click Test Connection before choosing Next.
3. The subsequent dialog is where you decide whether to select tables and/or views
from a list or to write your own SQL queries to extract the data. This dialog is
identical to the one seen during a SQL Server import, as shown in Figure 2-7
earlier. For now, accept the default and click Next.
Figure 2-11 Connecting to an Access database
Chapter 2: PowerPivot: Overview 33
4. The Table Import Wizard is now at the Select Tables and Views dialog as in
Figure 2-12. This is subtly different from the SQL Server equivalent. This time
there is no column for Schema—Access databases do not support schemas. Turn
on the check boxes for six tables: Categories, Customers, Employees, Order
Details, Orders, and Products. Then click Finish.
5. The last dialog in the Table Import Wizard is called Importing. You saw this
earlier for a SQL Server import in Figure 2-10. Click Close in this dialog. You are
returned to the PowerPivot window, which now has six tabs, one for each table.
Just before we examine the PowerPivot window, here’s a quick look at importing
the same data from Excel itself.
Figure 2-12 What to import