PowerPivot for Business Intelligence Usi - Ralston_ Barry.pdf

(64334 KB) Pobierz
The eXperT’s Voice ® in office and sharepoinT
PowerPivot
for Business Intelligence
Using Excel and Sharepoint
Business Intelligence Solutions for the Desktop
Barry Ralston
783633210.050.png 783633210.061.png 783633210.069.png 783633210.078.png 783633210.001.png 783633210.012.png 783633210.022.png 783633210.032.png 783633210.034.png
 
 
 
 
 
 
 
783633210.035.png 783633210.036.png 783633210.037.png 783633210.038.png 783633210.039.png 783633210.040.png 783633210.041.png 783633210.042.png 783633210.043.png 783633210.044.png
 
783633210.045.png 783633210.046.png 783633210.047.png 783633210.048.png
 
 
 
 
 
 
 
 
 
 
 
 
783633210.049.png
 
 
 
783633210.051.png 783633210.052.png 783633210.053.png 783633210.054.png 783633210.055.png 783633210.056.png 783633210.057.png 783633210.058.png 783633210.059.png 783633210.060.png 783633210.062.png 783633210.063.png 783633210.064.png 783633210.065.png
 
783633210.066.png
 
783633210.067.png
 
783633210.068.png
 
783633210.070.png
 
783633210.071.png 783633210.072.png 783633210.073.png 783633210.074.png 783633210.075.png 783633210.076.png 783633210.077.png 783633210.079.png 783633210.080.png 783633210.081.png 783633210.082.png
 
783633210.083.png 783633210.084.png 783633210.085.png 783633210.086.png 783633210.087.png 783633210.002.png 783633210.003.png 783633210.004.png 783633210.005.png 783633210.006.png 783633210.007.png 783633210.008.png 783633210.009.png 783633210.010.png 783633210.011.png 783633210.013.png 783633210.014.png 783633210.015.png 783633210.016.png 783633210.017.png 783633210.018.png 783633210.019.png 783633210.020.png 783633210.021.png
 
For your convenience Apress has placed some of the front
matter material after the index. Please use the Bookmarks
and Contents at a Glance links to access them.
783633210.023.png 783633210.024.png 783633210.025.png 783633210.026.png
■ CONTENTS
Contents at a Glance
About the Author ......................................................................................................................... xi
■
■
About the Technical Reviewer .................................................................................................... xii
■ Acknowledgments ..................................................................................................................... xiii
■ Chapter 1: Getting Started with PowerPivot for Excel ................................................................. 1
Chapter 2: Hello World, PowerPivot Style................................................................................... 11
■
Chapter 3: Combining Data Sources ........................................................................................... 25
■
Chapter 4: Data Analysis Expressions ........................................................................................ 43
■
Chapter 5: A Method to the Madness.......................................................................................... 65
■
Chapter 6: Installing PowerPivot for SharePoint ........................................................................ 93
■
Chapter 7: Collaboration, Version Control, and Management .................................................. 127
■ Chapter 8: PowerPivot As a Data Source.................................................................................. 153
■ Chapter 9: PowerPivot and SQL Server Reporting Services ..................................................... 189
■
■ Chapter 10: PowerPivot and Predictive Analytics .................................................................... 219
Chapter 11: Tips, Tricks, and Traps.......................................................................................... 265
■
Index......................................................................................................................................... 277
■
iv
783633210.027.png 783633210.028.png 783633210.029.png 783633210.030.png
 
C H A P T E R 1
■ ■ ■
Getting Started with
PowerPivot for Excel
A journey of a thousand miles begins with a single step.
—Lao-tzu
When I began working in business intelligence almost 18 years ago, the overarching goal was to create a
subject-oriented data store that could be used by an ordinary business worker without SQL skills to
answer questions and confirm hypothesis. Great work was done by my teammates and I to move data
from data storage structures designed for transaction capture into dimensional models designed from
the start for processing analytical queries. The analytical data store, in the form of a data mart, data
warehouse, or otherwise will maintain a vital purpose in business decision-making.
However, there is more to supplying data to the business decision-making process than simply
creating a central data store for analysis. Because of the time lag required to design, construct, and test,
the data in one of these formal structures, sanctioned by an information technology department, will
always lag behind the needs of users. Your organization’s information workers, people for whom a part
of their job is making decisions based on data they gather and format, are already finding ways to work
around this lag and get their jobs done, via massive Microsoft Excel spreadsheets or Microsoft Access
databases. Fortunate organizations have someone filling this gap, combining the data from the
sanctioned, corporate database with other data to make informed decisions.
Because of the explosion of data available (cash register scans, weather trends, etc.), the job of
information workers is becoming increasingly difficult. The information worker may be the CEO of a
small business trying to forecast demand for their products to justify expansion or an accounting clerk
trying to slice the monthly TPS report in a new way to understand software delivery issues.
Filling the Gap with PowerPivot for Excel
PowerPivot for Excel takes advantage of technologies that are a part of SQL Server 2008 R2, to enable an
information worker to manipulate, filter, and sort millions of data rows on a commodity PC. Because of
this, PowerPivot for Excel is uniquely positioned to fill the gap between the corporate data store and
other related data, which is required for a complete decision picture. Data can be combined from any of
the sources below into a single PowerPivot for Excel solution, for analysis without knowledge of
Structured Query Language (SQL) or Multidimensional Expressions (MDX).
1
783633210.031.png
CHAPTER 1 ■ GETTING STARTED WITH POWERPIVOT FOR EXCEL
•
SQL Server relational database
•
Microsoft Access database
•
SQL Server Analysis Services
•
SQL Server Reporting Services (SQL 2008 R2)
•
ATOM data feeds
•
Text files
•
Microsoft SQL Azure
•
Oracle
•
Teradata
•
Sybase
•
Informix
•
IBM DB2
•
Object Linking and Embedding Database/Open Database Connectivity
(OLEDB/ODBC) sources
•
Microsoft Excel
This variety of connections from PowerPivot for Excel to data sources means data that has not yet
been included (or may never be included) in the corporate data store is no longer an obstacle for
Information Worker analysis. Data from a corporate data mart hosted in SQL Server, can be combined
with a text file from a supplier and a spreadsheet maintained by the accounting department. The only
limitation on the Information Worker is the ability to relate the disparate sources; otherwise PowerPivot
for Excel will connect to the sources and transport data into the PowerPivot data store.
What You Will Need
To begin working with PowerPivot for Excel, you first need to establish a development environment.
Fortunately, the installation of PowerPivot for Excel is self-contained. The primary requirement for using
the PowerPivot add-in is having Microsoft Office Excel 2010 installed. A wide spectrum of computers,
from commodity desktops to high-end workstations can effectively run both Excel 2010 and the
PowerPivot add-in. This makes a compelling argument for PowerPivot for Excel as a tool for business
users who are not always on the leading edge of hardware acquisition. One of the most compelling
demonstrations for PowerPivot for Excel is executing a sort on a 100-million row dataset deployed on an
otherwise off-the-shelf Intel Atom-based netbook with 2 GB of RAM installed.
■ Note Unfortunately, at the time of this writing, there is no way to leverage PowerPivot for Excel within versions
of Microsoft Excel from version 2007 and earlier.
2
783633210.033.png
 

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika:

Zgłoś jeśli naruszono regulamin