Ralston B. PowerPivot for Business Intelligence Using Excel and SharePoint

Apress, 2011. - 299 p. ISBN10: 143023380XPowerPivot comprises a set of technologies for easy access to data mining and business intelligence analysis from Microsoft Excel and SharePoint. Power users and developers alike can create sophisticated, online analytic processing (OLAP) solutions using PowerPivot for Excel, and then share those solutions with other users via PowerPivot for SharePoint. Data can be pulled in from any of the leading database platforms, as well as from spreadsheets and flat files.PowerPivot for Business Intelligence Using Excel and SharePoint is your key to mastering PowerPivot. The book takes a scenario-based approach to showing you how to collect data, to mine that data through insightful analysis, and to draw conclusions that drive business performance. Each chapter in the book is focused on a specific challenge that you’ll encounter when using PowerPivot. Each chapter takes you through a solution technique that’s been proven in the real world.Covers the leading technology for bringing data analytics to the desktop
Presents real-world solutions to real-world scenarios
Written by a Microsoft Virtual Technical Specialist (VTS) for Business IntelligenceWhat you’ll learnInstall and verify the PowerPivot software
Integrated existing, available data to deliver business intelligence
Leverage Time Intelligence to report change over time
Write Data Analysis Expressions (DAX) to create custom measures
Identify and implement solutions for role-playing dimensions
Recognize and work-around PowerPivot’s missing features.About the Author.
About the Technical Reviewer.Getting Started with PowerPivot for Excel.
Filling the Gap with PowerPivot for Excel.
What You Will Need.
The 64-bit Decision.
Installing the Add-In.
A Brief Tour of PowerPivot for Excel.
A Trivial Test Case.
The Test Report.Hello World, PowerPivot Style.
The Business Scenario.
Assembling the Solution.
SQL Server As a PowerPivot Data Source.
Starting the Table Import Wizard.
Selecting the Table.
Monitoring the Import.
Reviewing the Results.
Data Refresh.
Creating the Report.
Narrowing to the Top Ten.
Behind the Scenes in PowerPivot for Excel.Combining Data Sources.
The Business Scenario.
Configuring Excel As a Data Source.
Venturing into the Date Dimension.
Understanding the Design.
Good Relationships.
PivotTables and PivotCharts.
Slicers.
Refreshing the Data.Data Analysis Expressions.
Fundamentals of DAX.
Calculated Columns.
Creating a Calculated Column.
Filtering PowerPivot Data.
Spotting Calculated Columns in the PowerPivot Field List.
Table Relationships.
Understanding the Problem.
Illustrating Relationships with Football.
Relating Tables with DAX.
Calculated Measures from Multiple Data Sources.
The Business Problem.
Separation of Facts and Dimensions.
New Measures.
Filter Context.
Begin with a PivotTable.
Compute Percent of Whole.A Method to the Madness.
Justification for a Method.
Principles.
Embrace Failure.
Think Dimensionally, Act Locally.
Transactional vs. Analytical Data Stores.
Measures, Dimensions, and Hierarchies. 67 Practices.
Take Advantage of Revision Control.
Rename Early to Keep DAX Formulas Sane.
Creating a Relationship.
Renaming an Object in a Relationship.
Examining the Result.
Choose Online Datasources When Possible.
Relate Fact Sets Through a Dimension Table.
Create a Separate Date Dimension.
Shortcut to a Rich Date Dimension.
Handling Moving Holidays.
Leverage Built-In Measures.
No Calculation.
% of Grand Total.
% of Column Total.
% of Row Total.
% Of.
% of Parent Row Total.
% of Parent Column Total.
% of Parent Total.
Difference From.
% Difference From.
Running Total In.
% Running Total In.
Rank Smallest to Largest and Rank Largest to Smallest.Customize Your PivotCharts via PivotTables.Installing PowerPivot for SharePoint.
Getting Started.
Existing SharePoint 2010 Installation.
Step One: Install SQL Server PowerPivot for SharePoint.
Step Two: Deploy the PowerPivot Solution Package.
Step Three: Start Other Required Services.
Step Four: PowerPivot Service Application.
Step Five: Enable Excel Services.
Step Six: Enable the Secure Store Service. 96 Step Seven: Enable Usage Data Collection.
Step Eight: Maximum File Upload Size.
Step Nine: Activate PowerPivot at the Site Collection Level.
Step Ten: Verify the Configuration Changes Work.
Virtual Success.
Step One: Create a New Virtual Machine.
Step Two: Add the Domain Controller Role.
Step Three: Add the Server to Your Domain.
Step Four: Install SharePoint 2010 Server.
Step Five: Install SQL Server 2008 R2 and PowerPivot.
Running PowerPivot for SharePoint on Windows 7.
The Road Map.
Step One: Install SQL Server 2008 R2.
Step Two: Install the SharePoint Prerequisites.
Edit the Config.xml File.
Install Microsoft Filter Pack 2.0.
Install WCF Hotfix KB976462.
Install ADO.Net Data Services Update.
Install the Microsoft Sync Framework.
Install SQL Server Native Client.
Install Windows Identity Foundation.
Install Chart Controls.
Install Microsoft SQL Server 2008 Analysis Services ADOMD.NET.
Enable Required Windows Features.
Step Three: Install SharePoint 2010 Server.
Step Four: Add PowerPivot for SharePoint Existing Farm.
Step Five: Final Configuration.
Fun with PowerPivot and SharePoint.
Verifying That the Installation Works.
Viewing the PowerPivot Gallery.
Potential Errors.Collaboration, Version Control, and Management.
Sharing Solutions.
Publishing.
Revision Control.
PowerPivot Gallery View Settings.
Gallery.
Theater.
Carousel.
All Documents.
PowerPivot Gallery Permissions.
Web Usage Scenarios.
Excel via the Browser.
PowerPivot from an iPad or Other Tablet.
PowerPivot Management Dashboard.
Accessing the PowerPivot Management Dashboard.
Using the Management Dashboard.
Query Response Times.
Average Instance CPU.
Average Instance Memory.
Activity.
Performance.
Workbook Activity Chart.
Workbook Activity List.
Configuring Usage Data Collection.PowerPivot As a Data Source.
PowerPivot As a Data Source for PowerPivot.
Verifying the Solution Prerequisites.
Assembling the Pieces.
Refreshing the Summary.
PowerPivot Management Dashboard As a Data Source.
Creating the Office Data Connection (.odc) File.
Writing a Report Using PowerPivot Management Data.
PowerPivot and PerformancePoint Dashboards.
Creating the Data Connection.
Creating the Analytic Chart.
Deploying to SharePoint.PowerPivot and SQL Server Reporting Services.
Consuming SSRS As a PowerPivot Data Source.
Verifying Report Access. 190 Configuring the Connection.
Using PowerPivot As an SSRS Data Source.
Creating the PowerPivot Source Worksheet.
Opening Report Manager.
Developing the Example SSRS Report.PowerPivot and Predictive Analytics.
Introducing the Data Mining Add-In for Excel.
Installing the Data Mining Add-In.
Locating the Table Analysis Tools.
Creating a SSAS Connection.
Configuring the SSAS Server.
Avoiding Connection Frustration.
Adding the SSAS Connection Within the Data Mining Add-In.
Preparing Data with PowerPivot.
Mining Some Data.
Taking Data Mining to the Cloud with Predixion Software.
Setting Up the Predixion Add-In.
Predicting Airline Delays.Tips, Tricks, and Traps.
PowerPivot Annoyances.
Disabled PowerPivot Add-In.
Calculated Column Missing.
User Experience for PowerPivot Solutions.
Connect Slicers Visually.
Lose the Grid.
Tuning PowerPivot Performance.
Slicers: Less Is More.
PowerPivot and SSAS Interaction.