The latest version of PowerPivot, released alongside SQL Server 2012, offers a range of new features, including:
A diagram view that shows tables and relationships in a schema-like diagram. By using this interface, users can manage tables, relationships, and hierarchies through simple drag-and-drop operations.
Support for multiple relationships between tables.
The ability to sort one column by the values in another column. This can be useful when, for example, you want to sort an alphabetic column, such as DayOfWeek, by a numerical column, such as DayNumberOfWeek, so that it displays correctly in Excel worksheets.
Many new DAX functions including SWITCH, DISTINCTCOUNT, TOPN, and a set of functions that support parent-child relationships.
Reporting properties to enable the workbook to support Power View reports.
PowerPivot for SharePoint
PowerPivot for SharePoint provides a central, managed portal through which analysts can securely share and collaborate on their PowerPivot for Excel workbooks. It enables users to locate the workbooks that they need easily, and reduces the wasted effort of users creating duplicate workbooks because they do not realize that a similar workbook already exists. PowerPivot for SharePoint helps administrators to establish governance over workbooks, which might be stored on the computers of many different users throughout an organization. It also makes it easier to provision appropriate resources to optimize application performance. Key features of PowerPivot for SharePoint include:
A portal through which information workers can upload PowerPivot for Excel workbooks.
Server-side processing and rendering in the Microsoft SharePoint® farm so that users can view a workbook in a browser with levels of performance that are similar to when they view a workbook locally in Excel.
A thumbnail gallery that enables users to browse and preview applications on the portal.
Automatic data refresh for workbooks.
The option to open workbooks in Excel so that users can download and edit workbooks locally.
Integrated SharePoint security that enables users to define permissions on workbooks.
Centralized administration through the SharePoint Central Administration website.
Health checks and monitoring through PowerPivot Management Dashboard.
Usage data collection so that administrators and developers can identify key applications and data sources.
Integration with SQL Server Reporting Services, which enables you to create standard reports and Power View reports that use PowerPivot for Excel workbooks as data sources.
Demonstration: Using PowerPivot
In this demonstration, you will see how to create, edit, and share a PowerPivot workbook. Create a PowerPivot Workbook
1. If you did not complete the previous demonstration in this module, start the MIA-DC1 and MIA- SQLBI virtual machines, and log into the MIA-SQLBI virtual machine as
ADVENTUREWORKS\Student with the password Pa$$w0rd. Then in the D:\Demofiles folder, right- click Setup.cmd and click Run as administrator. When prompted, click Yes.
2. Start Microsoft Excel 2010, and on the ribbon, click the PowerPivot tab.
3. On the ribbon, click PowerPivot Window. Note that the workbook window is still open and you can switch between the windows by clicking the Excel icon on the task bar or by clicking the Switch to Workbook button in the title bar of the PowerPivot window.
4. In the PowerPivot window, on the ribbon, in the Get External Data area, click From Database, and then click From SQL Server.
5. In the Table Import Wizard dialog box, change the Friendly connection name to Adventure Works DW; in the Server name box, type localhost; select Use Windows Authentication, and in the Database name list, click AdventureWorksDW. Then click Next.
6. On the Choose How to Import the Data Page, choose the option to select from a list of tables and views, and click Next.
7. On the Select Tables and Views page, select the FactResellerSales table and then click Select Related Tables.
8. Review the tables that were selected, and unselect DimCurrency and DimPromotion. Then select DimGeography, DimProductSubcategory and DimProductCategory (which were not
10. Click the row for the DimProductCategory table, and then click Preview and Filter. Then in the Preview Selected Table dialog box, uncheck the SpanishProductCategoryName,
FrenchProductCategoryName, and Image column headers and click OK. This removes columns you do not plan to use in the data model.
11. Click Finish to create the data model, and when the data for all tables is imported, click Close. 12. Click each tab at the bottom of the PowerPivot window to view the tables in the model. Then, on the
ribbon, in the View section, click Diagram View. This shows the relationships between the tables in the data model.
13. On the ribbon, in the View section, click Data View to return to the tabbed tables, and click the ProductCategory tab.
14. Double-click the column header for EnglishProductCategoryName and change the name to Category.
15. Close the PowerPivot window (but not the Excel workbook). Create a PivotTable from a PowerPivot Data Model
1. In Excel, on the PowerPivot tab of the ribbon, click the PivotTable drop-down button and click PivotTable.
2. In the Create PivotTable dialog box, select Existing Worksheet, and click OK.
3. In the PowerPivot Field List pane, expand the ResellerSales table and select SalesAmount. The total sales amount value is added to the PivotTable.
4. Expand the ProductCategory table and select Category. The sales amount is now aggregated by product category. Note that PowerPivot has automatically used the relationship from ResellerSales to ProductCategory through the Product and ProductSubcategory tables in order to aggregate the data.
5. Close Excel without saving the workbook. Create a Hierarchy
1. In the D:\Demofiles\Mod04 folder, double-click Demo.xlsx to open it in Microsoft Excel. 2. On the ribbon, click the PowerPivot tab, and then click PowerPivot Window. The PowerPivot
window may open behind the workbook, so minimize the workbook so you can see it.
3. In the PowerPivot window, on the ribbon, in the Get External Data area, click the Refresh drop- down button and click Refresh All. In the Data Refresh dialog box, when all tables have been refreshed, click Close.
4. In the PowerPivot window, on the ribbon, in the View area, click Diagram View.
5. Right-click the Sales Territory table header, click Create Hierarchy, type Location, and then press Enter.
6. In the Sales Territory table, click Sales Territory Group, drag it onto the Location hierarchy, and then release it.
7. In the Sales Territory table, click Country, drag it onto the Location hierarchy, and then release it. 8. In the Sales Territory table, click Sales Territory Region, drag it onto the Location hierarchy, and
then release it.
Analyze Data with Slicers
1. In the Demo.xlsx workbook, click the Demo worksheet tab.
2. Click cell B2. On the ribbon, on the PowerPivot tab, click PivotTable, and then click PivotTable. 3. In the Create PivotTable dialog box, click Existing Worksheet, and then click OK.
4. In the PowerPivot Field List, expand Reseller Sales, and then select Total Profit to add this column to the Values area.
5. In the PowerPivot Field List, expand Sales Territory, and then select Location. Note that this creates an expandable hierarchy in the PivotTable.
6. Expand Date, and then drag Calendar Year to the Slicers Horizontal area.
7. In the slicer, click 2001, and then wait for the data in the PivotTable table to update. The data is now filtered to show profit figures for 2001.
8. Repeat the previous step for 2002, 2003, and 2004. 9. Click the Clear Filter button.
10. Save and close Demo.xlsx.
Share a PowerPivot Workbook in SharePoint
1. Start Internet Explorer and navigate to http://mia-sqlbi.
2. On the Adventure Works Portal - Home page, in the Libraries list, click PowerPivot Gallery. 3. On the ribbon, click the Documents tab, click Upload Document, and then click Upload
Document.
4. In the PowerPivot Gallery – Upload Document dialog box, click Browse.
5. In the Choose File to Upload box, navigate to D:\Demofiles\Mod04, click Demo.xlsx, and then click Open.
6. In the PowerPivot Gallery – Upload Document dialog box, click OK. If the PowerPivot Gallery – Demo.xlsx dialog box appears, click Save.
7. When the large Demo icon appears in the PowerPivot gallery, click it to open the workbook in Excel Services in the SharePoint site.
8. Click the 2001 slicer and verify that you can interact with the shared workbook.
9. At the top of the workbook page, in the breadcrumbs trail, click Adventure Works Portal. Then close Internet Explorer.