Implementing Self-Service BI with PowerPivot for Excel 2010

Excerpted from Microsoft Official Course 10337, Updating Your Microsoft SQL Server 2008 BI Skills to SQL Server 2008 R2,this lesson is part of a three-day, 300-level course focusing on the new features of SQL Server 2008 R2 for BI specialists who have 3-4 years of experience with SQL Server. This course is offered exclusively by Microsoft Learning Solutions Partners and delivered by Microsoft Certified Trainers. See additional course requirements, the complete syllabus, and upcoming course dates and locations here.

Information workers and data analysts use Excel as a BI tool because it enables them to access the information that they need to perform data analysis through a familiar, easy-to-use interface. However, Excel has limitations as a BI tool, so for more complex analysis, developers typically create custom applications for users. The PowerPivot for Excel add-in makes it possible for information workers to create more powerful applications for themselves, so they are less dependent on developers, and they can create the applications that they need much faster.

PowerPivot for Excel significantly expands the capabilities of Excel as a BI tool. PowerPivot for Excel includes the following improvements:

• Enables users to connect to a wide range of data sources by using built-in providers and provides a simple mechanism for refreshing data so that it remains up to date.
• Enables users to create PivotTable tables that are based on multiple underlying tables without having to create large flattened tables that can negatively affect performance
• Delivers excellent performance, even when users perform complex analysis, because of the new VertiPaq architecture. VertiPaq features an in-memory, column-based store for data, and all processing is done in-memory. It also features a powerful compression algorithm that significantly reduces the memory footprint of the data.
• Features the new DAX language. DAX extends the capabilities that existing functions in Excel offer and adds new functions that enable you to work with data as you would in a relational database
• Provides intuitive features such as slicers and a customizable display that enable you to create visually compelling, easy-to-use applications
• Enables you to perform further analysis by using the full range of data-mining options through integration with the Data Mining Add-in for Excel

PowerPivot for Excel Benefits

Note: PowerPivot for Excel is available in 32-bit and 64-bit versions, and you must install the appropriate version for your installation of Excel 2010. The maximum in-memory size of a PowerPivot workbook when using the 32-bit version is around 800 MB. When using the 64-bit version of PowerPivot, the maximum in-memory workbook size is 4 GB.

Question: What limitations have users in your organization encountered when they use Excel as a BI tool?

Data Connection Options

One of the challenges that information workers face is to obtain access to the data that they need because that data is often stored on multiple systems and in multiple formats. PowerPivot for Excel provides a wide range of heterogeneous data sources to help to ensure that you can include all of the relevant data in your analysis, no matter where it is located.

When you create a connection, both the connection configuration information and the imported data are stored in the workbook itself. It is not necessary for workbooks to maintain an active connection to their data sources. This reduces the impact on source data stores and makes workbooks in PowerPivot for Excel portable, so you can freely share and move workbooks between machines.

Data Access Benefits

The available data sources in PowerPivot for Excel include an instance of the SQL Server database engine, SQL Server Analysis Services cubes, Microsoft SQL Azure™, Excel, Microsoft Office Access®, text files, workbooks in PowerPivot, SQL Server 2008 R2 Reporting Services reports, SharePoint lists, data feeds, and databases running on Oracle, Teradata, Sybase, Informix, and IBM DB2 database management systems. In addition to this extensive list, there are also OLE/DB and Open Database Connectivity (ODBC) providers that enable imports from any OLE/DB or ODBC source.

When you import from a database, you can filter tables to exclude columns that you do not need for your analysis. You should always try to ensure that you do not import unnecessary data into the workbook in PowerPivot for Excel to ensure optimal performance.

Importing Data by Using Copy and Paste

You can import data into a PowerPivot table by using copy and paste. For example, you can copy a range of cells from a workbook in Excel and select the To New Table option on the Home tab of the PowerPivot window to paste it into a new table. This can be useful if you want to add data to your workbook quickly without setting up a connection, or if you need to add data that you cannot import by using a data provider. However, unlike data that you access from a data source, data that you add by using the copy and paste method is static and you cannot refresh it later.

Question: Which data sources do users need to access in your organization? Do people experience problems with data access?

Data Relationships

When you import data from a table in a relational database, you can choose to automatically import additional tables that have a relationship with that table. PowerPivot for Excel identifies related tables by analyzing the foreign key relationships that exist among them. However, data analysts frequently need to analyze data that comes from multiple sources and for which explicit relationships are not defined. You can manually create, edit, and delete relationships as required in the workbook in PowerPivot for Excel.

You create and manage relationships by using the Create Relationship and Manage Relationship buttons on the Table tab of the PowerPivot window. When you create a relationship, you must specify the two tables that you want to relate and the common column that contains the key values. Relationships between tables in workbooks in PowerPivot for Excel are many-to-one; therefore, when you create a relationship, you must specify the columns and tables for each end of that relationship. You use the top Table and Column boxes to supply the table and column that are at the “many” end of the relationship, and you use the lower Table and Column boxes to specify the table and column that are at the “one” end of the relationship.

Linked Tables

You can incorporate data from workbooks in Excel into PowerPivot by using linked tables. Unlike the copy and paste and data provider methods of importing data, you can configure a linked table to maintain a “live link” to the original workbook. This means that any changes that you make to it will automatically be propagated to the workbook in PowerPivot for Excel when the user opens the PowerPivot window.

To create a linked table from a workbook in Excel, you should format the data as a table, highlight the table, and then click the Create Linked Table option on the PowerPivot tab. You can configure linked tables to refresh automatically or to refresh manually. For manual refreshes, you can use the Update All option to refresh all linked tables or the Update Selected option to update only the highlighted linked table.

PivotTable Tables and PivotChart Charts in PowerPivot for Excel

You easily can add PivotTable tables and PivotChart charts to your workbooks in PowerPivot for Excel. PivotTable tables enable you to explore data and access the information that you need with just a few clicks. You can easily add and remove summary values, labels, and filters by using drag-and-drop operations in the task pane. PivotChart charts add visual impact to your applications. You can create them by using the task pane in the same way as you do when you create PivotTable tables, and there are extensive formatting options that enable you to customize the appearance of the applications that you create.

PowerPivot Slicers

You can add slicers to workbooks in PowerPivot for Excel that enable you to slice and dice data quickly and easily. Slicers lay out the filter values as clickable areas in the workbook in PowerPivot for Excel, so you can easily apply and remove filters with just a single click.

You can add vertical and horizontal slicers by using the task pane. They function in the same way; the difference is that vertical slicers are positioned vertically in the workbook and horizontal slicers are positioned horizontally. When you design an application, you can arrange slicers in relation to the other components to provide the best visual experience.

Lab: Implementing Self-Service BI with PowerPivot

Note: To perform this lab with full-hands on access, attend the full course at a Microsoft Learning Partner.

Exercise 1: Creating Tables in PowerPivot for Excel

Scenario

Information workers and decision-makers at Adventure Works want to be more empowered to access and analyze data for themselves rather than relying on IT-built online analytical processing (OLAP) cubes and reports. You believe that PowerPivot will help to meet this need. You want to evaluate PowerPivot to obtain an understanding of its features, benefits, and capabilities so that you can ensure adequate provisioning and supply the necessary training and support that the new software will require. You also want to assess how PowerPivot will work alongside existing BI applications.

To accomplish this, you will create a demonstration application by using a sample set of data from the Adventure Works data warehouse. However, before you do this, you will evaluate PowerPivot for Excel by testing different data import methods.

The main tasks for this exercise are as follows:

1. Load data into PowerPivot for Excel
2. Manage relationships manually
3. Import a new table by using copy and paste
4. Create a linked table

Task 1: Load data into PowerPivot for Excel

1. Ensure the 10337A-DEN-DC and 10337A-MIA-SQL virtual machines are both running, and then log on to 10337A-MIA-SQL as ADVENTUREWORKS\Student with the password Pa$$w0rd.

2. Start Excel 2010 and on the PowerPivot tab of the ribbon, launch the PowerPivot window.

3. In the PowerPivot for Excel window, click From Database and use the following values to create a connection to the AdventureWorksDW2008R2 SQL Server database:

• In the Friendly connection name box, type PowerPivotAW
• In the Server name box, type MIAMI
• In the Log on to the server box, ensure that Use Windows Authentication is selected
• In the Database name list, select AdventureWorksDW2008R2

4. On the Choose How to Import the Data page, click Select from a list of tables and views to choose the data to import.

5. Select the FactInternetSales table, and then filter it to remove the UnitPriceDiscountPct, DiscountAmount, CarrierTrackingNumber, and CustomerPONumber columns.

6. Click Select Related Tables to automatically select the related tables for the FactInternetSales table. Six tables should be selected.

7. Select the DimGeography table.

8. Change the friendly names of the following tables by using the following values, and then finish the wizard:

• DimCurrency: Currency
• DimCustomer: Customer
• DimDate: Date
• DimGeography: Geography
• DimProduct: Product
• DimPromotion: Promotion
• DimSalesTerritory: Sales Territory
• FactInternetSales: Internet Sales

9. On the title bar, click the Save button and save the workbook as InternetSalesPowerPivot.xlsx in the E:\Labfiles\Lab02 folder

Task 2: Manage relationships manually

1. In the PowerPivot for Excel window (not the Excel workbook), create another SQL Server connection with the friendly name PowerPivotAWProduct that connects to the AdventureWorksDW2008R2 database on MIAMI to import the DimProductSubcategory table. Change the friendly name of the table to Product Subcategory and then finish the wizard.

2. In the PowerPivot for Excel window, on the Design tab of the ribbon, use the following settings to create a relationship between the Product table and the Product Subcategory table:

• In the Table list, click Product
• In the Column list, click ProductSubcategoryKey
• In the Related Lookup Table list, click Product Subcategory
• In the Related Lookup Column list, click ProductSubcategoryKey

3. Save the workbook.

Task 3: Import a new table by using copy and paste

1. In Excel 2010, open the AuxiliaryData.xlsx workbook in the E:\Labfiles\Lab02\Starter folder.

2. In the AuxiliaryData.xlsx workbook, in the DimProductCategory worksheet, highlight the cells A1 to E5, and then press CTRL+C.

3. In the PowerPivot for Excel window, on the Home tab, click Paste. Then, in the Paste Preview dialog box, click OK. This creates a new tab labeled Table in the PowerPivot for Excel window.

4. Right-click the Table worksheet tab, and rename the tab as Product Category.

5. In the PowerPivot for Excel window, on the Design tab of the ribbon, use the following settings to create a relationship between the Product Category table and the Product Subcategory table:

• In the Table list, click Product Subcategory
• In the Column list, click ProductCategoryKey
• In the Related Lookup Table list, click Product Category
• In the Related Lookup Column list, click ProductCategoryKey

6. Save the workbook.

Task 3: Import a new table by using copy and paste

1. In the AuxiliaryData.xlsx workbook, click the LinkedTable worksheet, and then copy cells A1 to B6.

2. In the InternetSalesPowerPivot.xlsx workbook, on Sheet1, click cell A1, and then paste the cells from the AuxiliaryData.xlsx workbook.

3. In the InternetSalesPowerPivot.xlsx workbook, format cells A1 to B6 (which you just pasted) as a table using any style that you want. Ensure that the My table has headers check box is selected.

4. In the InternetSalesPowerPivot.xlsx workbook, on the PowerPivot tab of the ribbon, click Create Linked Table, verify that the table is added to the PowerPivot for Excel window with the label table1, and then return to the InternetSalesPowerPivot.xlsx workbook.

5. In the Key column, change the values as follows:

• Cell A2: 11
• Cell A3: 12
• Cell A4: 13
• Cell A5: 14
• Cell A6: 15

6. In the PowerPivot for Excel window, verify that the values that you just changed in the linked table have also been updated in the Table1 table.

7. In the PowerPivot for Excel window, on the Design tab, use the following settings to create a relationship between the InternetSales table and the Table1 table:

• In the Table list, click Internet Sales
• In the Column list, click SalesTerritoryKey
• In the Related Lookup Table list, click Table1
• In the Related Lookup Column list, click Key

8. Save the workbook.

Exercise 2: Using a PivotTable Table in PowerPivot for Excel

Scenario

You will continue your assessment of PowerPivot for Excel by using a PivotTable table and slicers to view and manipulate PowerPivot data.

The main tasks for this exercise are as follows:

1. Create a PivotTable table that uses PowerPivot data
2. Use slicers

Task 1: Create a PivotTable table that uses PowerPivot data

1. In the InternetSalesPowerPivot.xlsx workbook (NOTE: Not the PowerPivot for Excel window), on the PowerPivot tab of the ribbon, create a new PivotTable table in a new worksheet.

2. In the PowerPivot Field List pane, select the SalesAmount field in the InternetSales table and verify that it is added to the Values area of the table.

3. Select the EnglishProductCategoryName field in the Product Category table and verify that it is added to the Row Labels area of the table.

4. Select the EnglishProductSubcategoryName field in the ProductSubcategory table and verify that it is added to the Row Labels area of the table.

5. Save the workbook.

Task 2: Use slicers

1. In the PowerPivot Field List task pane, in the Choose fields to add to a report list, drag the following fields from the Customer table to the Slicers Vertical box.

• Gender
• MaritalStatus

2. In the Gender slicer, click F, and in the MaritalStatus slicer, click S. Note that the figures in the PivotTable table are updated to match the gender and marital status that you selected.

3. In the MaritalStatus slicer, click Clear Filter. Note that in the PivotTable table, the Grand Total field now contains the total for all female customers.

4. In the Gender slicer, click Clear Filter. Note that in the PivotTable table, the Grand Total field now contains the total for all customers.

5. Right-click the MaritalStatus slicer, and then click Slicer Settings.

6. In the Slicer Settings dialog box, review the options, in the Caption box, change MaritalStatus to Marital Status, and then click OK.

7. Click any blank space in the Marital Status slicer, and on the ribbon, in the Slicer Tools section, view the Options tab.

8. On the ribbon, in the Slicer Styles section, click the green Slicer Style Light 3 layout. Repeat this process to change the color style of the Gender slicer.

9. In the PowerPivot Field List task pane, in the Slicers Vertical box, drag Marital Status to the Slicers Horizontal box. Note that in the worksheet, the positioning of the slicers in relation to the PivotTable table changes.

10. Save the workbook.

 

Want more? Attend the full course at a Microsoft Learning Solutions partner near you and learn how to:

• Describe the new and enhanced features in SQL Server 2008 R2 that relate to Business Intelligence, describe how SQL Server 2008 R2 can be integrated with Microsoft SharePoint technologies in a BI infrastructure, and describe the options available for implementing large-scale data warehousing solutions built on SQL Server 2008 R2
• Use SQL Server PowerPivot to create self-service BI solutions
• Use new and enhanced Reporting Services tools to create and manage self-service reporting solutions
• Use new report features to create compelling reports
• Use StreamInsight to create an application that captures and analyzes streams of event data
• Use Master Data Services to enable data consistency across the enterprise to help increase the accuracy of BI solutions
• Integrate data from SQL Azure into a SQL Server BI solution
• Describe the new features of SQL Server 2008 R2, and use them to manage BI applications

All Microsoft Official Courses—including this one--are delivered by Microsoft Certified Trainers (MCTs)—industry-recognized experts—and offered through a network of more than 1,500 Microsoft Certified Partners for Learning Solutions (Learning Solutions partners) in more than 120 countries and regions throughout the world.