Beginning Power BI with Excel 2013_ Self-Service Business Intelligence Using Power Pivot, Power View, Power Query.
Contents at a Glance
- About the Author
- About the Technical Reviewers
- Acknowledgments
- Introduction
■ Part 1: Building Models in Power Pivot
- Chapter 1: Introducing Power Pivot
- Chapter 2: Importing Data into Power Pivot
- Chapter 3: Creating the Data Model
- Chapter 4: Creating Calculations with DAX
- Chapter 5: Creating Measures with DAX
- Chapter 6: Incorporating Time Intelligence
- Chapter 7: Data Analysis with Pivot Tables and Charts
■ Part 2: Building Interactive Reports and Dashboards
with Power View
- Chapter 8: Optimizing Power Pivot Models for Power View
- Chapter 9: Creating Standard Visualizations with Power View
- Chapter 10: Creating Interactive Dashboards with Power View
■ Part 3: Exploring and Presenting Data with Power Query
and Power Map
- Chapter 11: Data Discovery with Power Query
- Chapter 12: Geospatial Analysis with Power Map
- Chapter 13: Mining Your Data with Excel
- Chapter 14: Creating a Complete Solution
■ Index
Chapter 1
Introducing Power Pivot
The core of Microsoft’s self-service business intelligence (BI) toolset is Power Pivot. The rest of the tools, Power View,
Power Query, and Power Map, build on top of a Power Pivot tabular model. In the case of Power View this is obvious
because you are explicitly connecting to the model. In the case of Power Query and Power Map it may not be as
obvious because the Power Pivot tabular model is created for you behind the scenes. Regardless of how it is created, to
get the most out of the tool set and gain insight into the data you need to know how Power Pivot works.
This chapter provides you with some background information on why Power Pivot is such an important tool and
what makes Power Pivot perform so well. It instructs you on the requirements for running Power Pivot and how to
enable it. The chapter also provides you with an overview of the Power Pivot interface and provides you with some
experience using the different areas of the interface.
After reading this chapter you will be familiar with the following:
• Why use Power Pivot?
• The xVelocity in-memory analytics engine
• Enabling Power Pivot for Excel
• Exploring the Data Model Management interface
Why Use Power Pivot?
You may have been involved in a traditional BI project consisting of a centralized data warehouse where the various
data stores of the organization are loaded, scrubbed, and then moved to an OLAP (online analytical processing)
database for reporting and analysis. Some goals of this approach are to create a data repository for historical data,
create one version of the truth, reduce silos of data, clean the company data and make sure it conforms to standards,
and provide insight into data trends through dashboards. Although these are admirable goals and are great reasons to
provide a centralized data warehouse, there are some downsides to this approach. The most notable is the complexity
of building the system and implementing change. Ask anyone who has tried to get new fields or measures added to
an enterprise-wide warehouse. Typically this is a long, drawn-out process requiring IT involvement along with data
steward committee reviews, development, and testing cycles. What is needed is a solution that allows for agile data
analysis without so much reliance on IT and formalized processes. To solve these problems many business analysts
have used Excel to create pivot tables and perform ad hoc analysis on sets of data gleaned from various data sources.
Some problems with using isolated Excel workbooks for analysis are conflicting versions of the truth, silos of data, and data security.
So how can you solve this dilemma of the centralized data warehouse being too rigid while the Excel solution is
too loose? This is where Microsoft’s self-service BI tool set comes in. These tools do not replace your centralized data
warehouse solution but rather augment it to promote agile data analysis. Using Power Pivot you can pull data from
the data warehouse, extend it with other sources of data such as text files or web data feeds, build custom measures,and analyze the data using pivot tables and pivot charts. You can create quick proofs of concepts that can be easily
promoted to become part of the enterprise wide solution. Power Pivot also promotes one-off data analysis projects
without the overhead of a drawn-out development cycle. When combined with SharePoint, Power Pivot, workbooks
can be secured and managed by IT, including data refresh scheduling and resource usage. This goes a long way to
satisfying IT’s need for governance without impeding the business user’s need for agility.
Here are some of the benefits of Power Pivot:
• Functions as a free add-in to Excel
• Easily integrates data from a variety of sources
• Handles large amounts of data upward of tens to hundreds of millions of rows
• Uses familiar Excel pivot tables and pivot charts for data analysis
• Includes a powerful new Data Analysis Expressions (DAX) language
• Has data in the model that is read only, which increases security and integrity
When Power Pivot is hosted in SharePoint, here are some of its added benefits:
• Enables the sharing and collaboration of Power Pivot BI Solutions
• Can schedule and automate data refresh
• Can audit changes through version management
• Can secure users for read-only and updateable access
Now that you know some of the benefits of Power Pivot, let’s see what makes it tick.
0 Comments