Self-Service Analytics for Contoso
A solution scenario using Microsoft Business Intelligence applications, including SQL Server 2008 R2, Microsoft SharePoint Server 2010, Microsoft Excel 2010, and PowerPivot for Excel.
Authors:
Michael Blythe
Steve Hord
Frederique Klitgaard
Mary Lingel
Nathaniel Scharer
Heidi Steen
Date published:
June 2010
Summary:
This business intelligence (BI) solution scenario describes the steps that employees of the fictional company Contoso take as they analyze sales and promotions data and share that analysis with others in the company. In the scenario, sales data is first analyzed in Excel. Additional sales and promotions data is then imported into PowerPivot for Excel, and the data is further analyzed. After the analysis is complete, it is published to the PowerPivot Gallery in SharePoint and distributed via Reporting Services, enabling others in the company to interact with the analysis and develop additional insights. The accompanying sample data lets you follow along with this document.
© 2010 Microsoft Corporation. All rights reserved.
Microsoft, Excel, SharePoint, SQL Server, and Windows are trademarks of the Microsoft group of companies.
All other trademarks are property of their respective owners.
Contents
Self-Service Analytics
Analyze Data on the Desktop
Install Excel 2010 and PowerPivot
Analyze UK Sales Data in Excel
Step1: Display data in a PivotTable report
Step2: Use slicers to filter PivotTable data
Step3: Analyze PivotTable data
Step4: Calculate values in a PivotTable report
Step5: Emphasize data trends
Analyze Additional Sales Data in PowerPivot
Step 1: Learn about PowerPivot
Step 2: Import Data into PowerPivot
Step 3: Review and Create Relationships between Tables
Step 4: Perform Analysis of Sales Data
Next Steps
Share Analysis with Others
Install and Configure Servers to Share Data
Share Data in a SharePoint PowerPivot Gallery
Add Promotions Data to PowerPivot, and Complete Analysis
Step 1: Import additional data
Step 2: Create a PivotTable and Add Measures
Step 3: Analyze the PivotTable data
Share Data in a Report
Step 1: Install, configure, or verify that Reporting Services is installed
Step 2: Open Report Builder from the PowerPivot document
Step 3: Select the PowerPivot data for this report
Step 4: Configure Parameters
Step 5: Create a Report Title
Step 6: Choose a data visualization
Step 7: Add a Legend
Step 8: Save the report to the PowerPivot Gallery
Step 9: Make a quick change
Step 10: Create a subscription and schedule
Summary
Self-Service Analytics
Anna works for the UK branch ofthe global retailer Contoso.Contoso has operations in North America, Europe, and Asia with brick and mortar, ecommerce, and multi-channel retailing components. The company also manufactures several product lines, with manufacturing operations in Asia.
Anna is known for her Microsoft Excel skills and needs tools that “let me efficiently manipulate and interact with the data so that I can provide actionable data and data-driven insights for all my stakeholders." Anna’s manager recently got his hands on an Excel workbook with sales data and would like her to take a look at it. He is interested in seeing howlaptop sales are faring in some of the stores, but he also encourages her to dig in and identify any trends that catch her eye.
Navigating the Scenario
This scenario is divided into two sections: analyzing data on the desktop; and sharing analysis with others. We encourage you to complete the entire tutorial, but you can choose to focus just on analyzing data.The two sectionsare each divided into steps and sub-steps. Each sub-step contains information about a task that you will complete and includes links to online topics. In some cases, the topics just provide background information and general guidance, and in other cases they contain additional step-by-step instructions. After you are done with an online topic, always return to this document to see what’s next.
Audience Assumptions
Thescenario assumes some understanding of Excel, including familiarity with functions, PivotTables, and PivotCharts. If you have limited experience with Excel, we encourage you to try the scenario, but you might need to do some additional work to follow along. Many topics in this scenario include links to additional content.
If you want to share analysis with others in the second part of the scenario,you or someone in your organization should have familiarity with installing server products like Microsoft SharePoint Server 2010. If you have an IT department, check with it first; it even have the necessary products installed. We provide detailed installation instructions and a list of settings to verify that any current installations are configured correctly.
Prerequisites
To follow along with this scenario, you will need the Self-Service Analytics Sample Data.The sample data is from the Contoso SQL Server database and is stored in Access databases and Excel worksheets. All of the workbooks, databases, and files that are referenced in this are available from the same page where you downloaded this document.
This scenario explains how to install all of the products that you will use. For desktop analysis, the following products are required:
- Microsoft Excel 2010
- Microsoft SQL Server 2008 R2 PowerPivot for Microsoft Excel2010
If you also want to share data by using SharePoint Server and reports, the following products are required:
- SharePoint Server 2010
- SQL Server 2008 R2 Enterprise Edition
Analyze Data onthe Desktop
In this section of the scenario, Anna installs Excel and PowerPivot for Excel, bringsin data from various sources,and analyzes that data by using PivotTables, PivotCharts, formulas, and new Excel features like Slicers and Sparklines. Because PowerPivot is an Excel add-in, Anna has access to all of Excel’s analytic features regardless of whether she brings data into a standard Excel worksheet or the separate PowerPivot window.
Install Excel 2010 and PowerPivot
Anna has a new computer and needs to install Microsoft Office and PowerPivot before she can open the spreadsheet from her manager.
Task / DescriptionInstall Office 2010 / Perform a default install of Office (including Word, Outlook, and so on), or just install Excel and Office Shared Features. We recommend the 64-bit version of Excel if your computer supports it.
For more information, see Office Online.
Install PowerPivot for Excel / Install the appropriate version of PowerPivot: if you installed 32-bit Excel, install 32-bit PowerPivot; and if you installed 64-bit Excel, install 64-bit PowerPivot.
For more information, see:
- Install the PowerPivot Add-In for Excel (video)
- Install the PowerPivot Add-In for Excel (text, including hardware and software requirements)
Analyze UK Sales Data in Excel
After installing Excel and PowerPivot, Anna decides to take a look at the data that is available in the spreadsheet from her manager. She realizes fairly quickly that the data is limited to computer and video sales in the UK, but that there is enough detail for her to analyze laptop sales in several stores. If she wants to look into broader trends, she will need some additional data.
After Anna opens the workbook that contains the Contoso data she wants to analyze, she starts by creating a PivotTable, and then steps through the process to analyze the data for laptop sales.Once completed, the analysis will look like the following:
Step1: Display data in a PivotTable report
After opening the workbook in Excel, Anna is ready to create a PivotTable that displays the sales data that she wants to analyze.
Task / DescriptionCreate a PivotTable report / By creating a PivotTable report, you can summarize data, analyze it, and explore the data to in-depth levels of detail. PivotTable analysis enables you to make informed decisions about critical data in your enterprise.
To follow the scenario:
- In Excel, open UK_FilteredSalesData.xlsx, select all the data and then create a PivotTable report on a separate worksheet.
- In the PivotTable field List, select StoreName, Product Category Name, Product Subcategory Name, Product Name, Sales Quantity, and Sales Amount.
- Quick start: Insert a PivotTable report
- Create or delete a PivotTable or PivotChart report
Pivot data by changing the field layout / After you add fields to your PivotTable, you can pivot the data by changing its field layout. By using the PivotTable Field List, you can add, rearrange, or remove fields to show the exact data you want to analyze.
To follow the scenario:
- To pivot the data, drag Sales Date to the Column Labels area of the PivotTable Field List.
Step2: Use slicers to filter PivotTable data
To focus on the data she wants to analyze, Anna decides to use the new slicer feature in Excel 2010. She likes the way it lets her filter the data without obscuring what exactly is and is not displayed in the PivotTable report. Slicers are especially useful to compare the data of two or more selected items.
Task / DescriptionAdd slicers to filter PivotTable data / In Excel 2010, you have the option to use slicers to filter PivotTable data. Slicers provide buttons for quick filtering and clearly indicate the current filtering state, which makes it easy to see what exactly is shown in a filtered PivotTable report.
To follow the scenario:
- Create a slicer for StoreName, Product Category Name, and Product Subcategory Nameby clicking Insert Slicer on the ribbon (PivotTable Tools, Options tab, Sort & Filter group).
- Use slicers to filter PivotTable data
- Video: Use slicers to filter PivotTable data
Apply slicers to show only areas to be analyzed / Slicers appear on the worksheet alongside the PivotTable, in a layered display if you have more than one slicer. To filter the PivotTable data, you simply click one or more of the buttons in any of the slicers that are displayed.
To follow the scenario:
- In the StoreName slicer, click Contoso Baildon Store, hold down the CTRL key, and then click Contoso Carlisle Store, Contoso Edinburgh store, and Contoso Glasgow store.
- In the Product Category Name slicer, click Computers.
- In the Product Subcategory Name slicer, click Laptops.
- Use slicers to filter PivotTable data
- Video: Use slicers to filter PivotTable data
Step3: Analyze PivotTable data
To analyze the data in-depth, Anna understands that she must drill down to different levels of detail, then group, sort, and filter the data as needed.She turns repeated labels on so that she can easily see where the values belong, without having to scroll back to the top.
Task / DescriptionExpand items to display details / To drill down into the data for in-depth analysis, you can expand or collapse to any level of data detail, and even for all levels of detail in one operation. You can also expand or collapse to a level of detail beyond the next level.
To follow the scenario:
- Expand the data for the Contoso Baildon Store by clicking the Plus sign.
Repeat item labels to make data easier to scan / When a PivotTable has a large amount of numerical data, repeating item and field labels can be very helpful. With repeated labels, you will know exactly what you are looking at without having to scroll back to a summary row.
To follow the scenario:
- Repeat item labels for laptops by right-clicking any Laptops field, and then clicking Field Settings. On the Layout & Print tab, select Repeat item labels, and then click Show Item Labels in tabular form.
- Repeat item labels in a PivotTable report
- Video: Repeat item labels in a PivotTable report
Group items / To isolate a subset of items for more refined analysis of your data, you can group numeric, date, time, and even a selection of specific items.
To follow the scenario:
- Group dates by quarters by right-clicking any date field (for example cell B1), click Group, select Quarters, and clear theMonthsselection.
Sort items / If you want to rearrangeitems so that you can more easily find them, you can change their sort order.
To follow the scenario:
- Try sorting the Laptops data from Z to Aby right-clicking the Laptops field in the Product Subcategory slicer,, and then clicking Sort, and then Sort Z to A.
Filter items / When you want to focus your analysis on a subset of your data while hiding everything else, you can filter items by specific criteria.
To follow the scenario:
- Filter data to show only fourth-quarter data by clicking the Column Labels filter and then selecting Qtr 4.
- Set filtering to Allow multiple filters per field (PivotTable Tools, Options tab, PivotTable group, Options command, Totals & Filters tab), and then right-click any laptop field to filter by Label Filters that contain the word Black (case-sensitive), and then by Value Filters that show Sum of Sales Amount that is greater than 10000.
Step4: Calculate values in a PivotTable report
To compare the numbers, and see what is going on, Anna uses the Show Values As feature to display values in different ways.
Task / DescriptionEnter additional value fields / You can add the same value field to a PivotTable more than once, which is useful when you want to show the actual value and other calculations, such as a running total calculation, side by side.
To follow the scenario:
- Add another Sales Amount column by dragging the Sales Amount field from the PivotTable Field List to the Values area, placing it right below the first Sales Amount field. In the PivotTable, change the name of the new column (Sum of Sales Amount 2) to % of Grand Total.
Display different calculations in a value field / Instead of writing your own formulas in calculated fields, you can quickly display different calculations for a value in any value field, for example, you can calculate running totals or percentages of other values.
To follow the scenario:
- Change the values in the new column so that they show as a percentage of the grand total amount by right-clicking any of the values, clicking Show Values As, and then clicking % of Grand Total.
- Show different calculations in PivotTable value fields
- Calculate values in a PivotTable report
- Video: Use the Show Values As feature in a PivotTable report
Step5: Emphasize data trends
Finally, Anna applies conditional formatting to highlight the analysis results. This feature is useful because it shows any trends in the data.
Task / DescriptionApply conditional formatting / Conditional formatting uses data bars, color scales, and icon sets to highlight data so that you can visually explore and analyze that data, detect critical issues, and identify patterns and trends.
To follow the scenario:
- Apply conditional formatting to the sales percentages by selecting them (without the summary values) in the column, and then clicking a color scales type that you want (Home tab, Styles group, Conditional Formatting button, Color Scales command).
- Add, change, find, or clear conditional formats
- Quick Start: Apply conditional formatting
- Video: Apply conditional formatting
Analyze Additional Sales Data in PowerPivot
The analysis that Anna was able to perform in Excel enabled her to pinpoint some interesting trends, but the analysis was limited by the amount of data in the worksheet. One of Anna’s colleagues, Tim from IT, suggests that Anna use tables from a local copy of the corporate data warehouse. Anna has seen a PowerPivot demo and is excited about the idea of importing data into PowerPivot and continuing her analysis in Excel. She decides to familiarize herself with the basics of PowerPivot, and then dig right in. The completed analysis will look like the following:
Note: After Step 1 below, all the tasks in this section are covered in the PowerPivot tutorial, using the same data. You can follow along in this paper, or you can simply complete that tutorial and then come back to this paper and start on the next section: “Next Steps”. In either case, start with the existing UK_FilteredSalesData workbook that you created in the previous section. If you plan to go directly to the tutorial, follow these steps before starting the tutorial: