Excel

In addition to hard-copy reports and the daily line-of-business applications, most decision makers are comfortable with the applications that make up Microsoft Office. Spreadsheets were some of the first tools for doing what we now call ad hoc analysis and reporting. In almost every organization, decision makers depend heavily on spreadsheets as data repositories and tools for analysis. Wouldn't it make sense to allow our business intelligence information to be incorporated and manipulated in this comfortable and ubiquitous environment as well? Microsoft provides us the means to do this in Excel with the PivotTable and PivotChart Wizard.

Creating Pivot Tables and Pivot Charts

The PivotTable and PivotChart Wizard takes us step-by-step through the process of creating a pivot table and/or pivot chart. It first enables us to set the data source to be used for the pivot items. Fortunately, the data source can be an OLAP cube in an Analysis Services database. Once the data source is set, we can either lay out the report while we are still in the wizard or have the wizard create an empty pivot table, and then populate it after the wizard is complete.

Connecting to the Data

Pivot tables and pivot charts require a data source to work from. One option is to use data from cells right inside the spreadsheet. Pivot tables and pivot charts can also access data from external sources. This second option, of course, is the one we are most interested in. The wizard steps us through the process of setting up access to an external data source, which, in our case, is Analysis Services.

MDX Under the Hood

Like the MDX query screen on the Business Intelligence Development Studio Browser tab, the pivot table and pivot chart let us manipulate Analysis Services data. As you might guess, under the hood, the pivot table and pivot chart are sending MDX queries to Analysis Services. These tools provide an easy-to-use interface, so our decision makers do not need to know anything about the ins and outs of MDX. Behind the scenes, the SELECT … ON COLUMNS, …ON ROWS syntax is still being generated and utilized.

Layout

The pivot table layout is done in a manner similar to the drag-and-drop approach we saw on the MDX query screen on the Business Intelligence Development Studio Browser tab. One advantage of the Excel pivot table is that it not only includes row and column dimensions, but it can also include data in the pages dimension. We can, therefore, use these tools to look at three dimensions at a time.

As stated earlier, the pivot table layout can be created either as part of the PivotTable and PivotChart Wizard or after the wizard completes. In most cases, the layout is created after exiting the wizard. Waiting until after the wizard is complete provides a cleaner and more capable report creation environment. The one time we may want to create the layout in the wizard is in a situation where the underlying data has gotten large and unwieldy. By creating the layout in the wizard, we can specify a field to serve as the page grouping. Once this is done, data is retrieved one page at a time, eliminating the need to retrieve the entire data set before displaying results.

Pivot Table

The Excel pivot table is similar to the Browser tab in the Business Intelligence Management Studio in its implementation of drag-and-drop query creation. In format, the Excel pivot table is similar to the custom CellSet viewer we created in our ADOMD.NET example in Chapter 16. After all, our CellSet viewer provided for a page dimension just like the pivot table. The difference is, our CellSet viewer supported only one hierarchy level on each dimension. The Excel pivot table allows for multiple hierarchy levels on each dimension. In addition, our CellSet viewer supported only one value in each cell. The Excel pivot table allows multiple values.

Learn By Doing—Creating an Excel Pivot Table

Feature Highlighted

• Creating a pivot table in Microsoft. Excel

Business Need The vice president (VP) of production for Maximum Miniatures, Incorporated would like to do some of his own analysis of the information in the MaxMinManufacturingDM cube. He is a big Excel user and is comfortable with this program. To fulfill the VP's needs, we show him how to create a pivot table in Excel, pulling data from the MaxMinManufacturingDM cube.

Steps

1. Open Microsoft Excel.

2. Select Data | PivotTable and PivotChart Report as shown in Figure 17-1. The PivotTable and PivotChart Wizard dialog box appears.

3. Select the External Data Source radio button to use data from outside the spreadsheet as shown in Figure 17-2.

4. Click Next. The Step 2 of 3 page of the wizard appears as shown in Figure 17-3.

5. Click Get Data. The Choose Data Source dialog box appears. Select the OLAP Cubes tab as shown in Figure 17-4.

6. Initially, <New Data Source> is the only item in the list. With this item selected, click OK. The Create New Data Source dialog box appears.

7. Enter MaxMinManufacturingDM for the name of the data source.

8. Select Microsoft OLE DB Provider for Analysis Services 9.0 from the OLAP provider drop-down list. The Create New Data Source dialog box appears as shown in Figure 17-5.

9. Click Connect. The Multidimensional Connection 9.0 dialog box appears as shown in Figure 17-6.

10. Leave the Analysis Server radio button selected. Enter the name of the Analysis Services server hosting the MaxMinManufacturingDM database.

11. Enter a User ID and Password to use when accessing this server or leave these fields blank to use the credentials of the current user.

12. Click Next. The Select the Database page appears.

13. Select the MaxMinManufacturingDM database as shown in Figure 17-7.

14. Click Finish. You return to the Create New Data Source dialog box.

15. Select the Max Min Manufacturing DM cube as the cube that contains the data you want. The Create New Data Source dialog box appears as shown in Figure 17-8.

16. Click OK to exit the Create New Data Source dialog box. You return to the Choose Data Source dialog box. The MaxMinManufacturingDM data source you just created appears in the list of OLAP Cubes data sources as shown in Figure 17-9.

17. Make sure the MaxMinManufacturingDM data source is selected and click OK. You return to Step 2 of the PivotTable and PivotChart Wizard with a note that data fields have been retrieved as shown in Figure 17-10.

18. Click Next. Step 3 of the PivotTable and PivotChart Wizard appears as shown in Figure 17-11.

19. Clicking the Layout button takes you to the Layout page of the wizard as shown in Figure 17-12. We can use this page to complete the layout of the pivot table within the wizard. An example is shown in Figure 17-13. However, the layout is rather small. In fact, we cannot even see the full name of the dimension or hierarchy unless we mouse over the item and wait for the tool tip to appear. For this exercise, we will complete the layout in the table itself. (Click Cancel to exit the Layout page, if necessary.)

20. Clicking the Options button takes you to the PivotTable Options dialog box as shown in Figure 17-14. We can use this dialog box to change the formatting and totaling options for the pivot table. We use the default settings for this exercise. (Click Cancel to exit the PivotTable Options dialog box, if necessary.)

21. Click Finish. The Pivot Table layout appears in the Excel spreadsheet as shown in Figure 17-15.

22. In the PivotTable Field List, locate the Time Hierarchy. Drag the Time Hierarchy and drop it on the Drop Page Fields Here area of the layout as shown in Figure 17-16.

23. This creates a Time Hierarchy drop-down window as shown in Figure 17-17. Using this drop-down window, we can select the members of the Time Hierarchy we want to use for the page dimension.

24. Check the Select Multiple Items check box. We can now select multiple dimension members.

25. Expand the 2005 item in the list.

26. Uncheck the 2005Q3 and 2005Q4 items in the list. The Time Hierarchy drop-down window appears as shown in Figure 17-18.

27. Click OK.

28. In the PivotTable Field List, locate the Product Hierarchy. Drag the Product Hierarchy and drop it on the Drop Row Fields Here area of the layout.

29. Rows are created for the highest level in the hierarchy, the Product Type. Expand the Product Type drop-down window.

30. Expand the entry for Guiding Lights. None of the product subtypes under this product type are checked.

31. Click the check box for Guiding Lights. Notice the Guiding Lights check box now has a double checkmark and all the product subtypes are checked as shown in Figure 17-19.

32. Click OK. Rows have been created for all the product types and product subtypes under Guiding Lights as shown in Figure 17-20.

33. In the PivotTable Field List, locate the Plant Hierarchy. Drag the Plant Hierarchy and drop it on the Drop Column Fields Here area of the layout.

34. Expand the Country drop-down window. Click United States, so its check box contains a double checkmark.

35. Click OK. With some arranging, the layout appears as shown in Figure 17-21.

36. In the PivotTable Field List, locate the Accepted Products measure. Note the difference between the icon for the measure and the icon for the hierarchy members. Drag the Accepted Products measure and drop it on the Drop Data Items Here area of the layout. The Accepted Products label appears above the Product Type label in the layout as shown in Figure 17-22. This keeps it out of the way of the column headings.

37. Drag the Rejected Products measure and drop it in the area where you dropped the Accepted Products measure. Both measures are included in the layout. The labels for the measures have moved to the right of the row labels and total lines were added for each as shown in Figure 17-23.

38. Now that we are done placing items on the layout, we can close the PivotTable Field List window. We can close the window or click the Hide Field List button in the PivotTable toolbar as shown in Figure 17-24. We can bring back the PivotTable Field List window by clicking this toolbar button a second time.

39. Select one of the cells containing the Accepted Products measure label. Change the text to # Accepted. When we exit the modified cell, all occurrences of the measure label are changed as shown in Figure 17-25.

40. Change the Rejected Products measure label to # Rejected.

41. Let's format the text for the # Accepted cells. Select one of the cells containing a # Accepted amount.

42. Click the Field Settings button on the PivotTable toolbar as shown in Figure 17-26. The PivotTable Field dialog box appears.

43. Click the Number button. The Format Cells dialog box appears.

44. Select Number in the Category list. Set Decimal Places to 0. Check the Use 1000 Separator (,) check box.

45. Click OK to exit the Format Cells dialog box.

46. Click OK to exit the PivotTable Field dialog box.

47. Repeat Steps 41–46 to format the # Rejected cells.

48. Click the Format Report button in the PivotTable toolbar. The AutoFormat dialog box appears as shown in Figure 17-27. The format selected in the AutoFormat dialog box is applied to the pivot table.

    Note 

    Complete all pivoting and manual formatting before applying an AutoFormat to your pivot table.

49. Select the format indicated in Figure 17-27.

50. Click OK. The pivot table appears as shown in Figure 17-28.

51. Select Save from the Excel toolbar and save the spreadsheet in an appropriate location.

Figure 17-1: Starting the PivotTable and PivotChart Wizard

Figure 17-2: Step 1 of the PivotTable and PivotChart Wizard

Figure 17-3: Step 2 of the PivotToble and PivotChart Wizard

Figure 17-4: The Choose Data Source dialog box

Figure 17-5: The Create New Data Source dialog box

Figure 17-6: The Multidimensional Connection 9.0 dialog box

Figure 17-7: Selecting a database in the Multidimensional Connection 9.0 dialog box

Figure 17-8: The Create New Data Source dialog box with an Analysis Services database and cube selected

Figure 17-9: The Choose Data Source dialog box with the new data source

Figure 17-10: Step 2 of the PivotTable And PivotChart Wizard with data retrieved

Figure 17-11: Step 3 of the PivotTable and PivotChart Wizard

Figure 17-12: The Layout page of the PivotTable and PivotChart Wizard with an empty layout

Figure 17-13: The Layout page of the PivotTable and PivotChart Wizard with a completed layout

Figure 17-14: The PivotTable Options dialog box

Figure 17-15: The empty PivotTable layout in the Excel spreadsheet

Figure 17-16: Defining the page dimension of the PivotTable

Figure 17-17: Using the drop-down window to select dimension members

Figure 17-18: Selecting 2005Q1 and 2005Q2 for the page dimension

Figure 17-19: The double checkmark, indicating all members selected at the next lower level in the hierarchy

Figure 17-20: The PivotTable layout with Product Types and Product Subtypes

Figure 17-21: The Plant Hierarchy added to the PivotTable layout

Figure 17-22: The Accepted Products measure added to the PivotTable layout

Figure 17-23: Both the Accepted Products and Rejected Products measures in the PivotTable layout

Figure 17-24: The Hide Field List button in the PivotTable toolbar

Figure 17-25: The Accepted Products measure label changed to # Accepted

Figure 17-26: The Field Settings button in the PivotTable toolbar

Figure 17-27: The AutoFormat dialog box

Figure 17-28: The PivotTable with an AutoFormat applied

Pivot Chart

Learn By Doing—Creating a Pivot Chart

Feature Highlighted

• Creating a pivot chart in Microsoft Excel

Business Need our intrepid VP of production is extremely happy with the information he has been able to discover using the pivot table in Excel. Now, he would like to create a chart from this information to use at an upcoming presentation. We can show him how to create a pivot chart in Excel.

Steps

1. Select the Sheet2 tab in the spreadsheet used in the first Learn By Doing exercise in this chapter.

2. Select Data | PivotTable and PivotChart Report from the Main menu. Step 1 of the PivotTable and PivotChart Wizard appears.

3. Select the External Data Source radio button for the data you want to analyze. Select the PivotChart Report radio button for the type of report you want to create.

4. Click Next. Step 2 of the PivotTable and PivotChart Wizard appears.

5. Click Get Data. The Choose Data Source dialog box appears.

6. Select the OLAP Cubes tab, and then select the MaxMinManufacturingDM data source.

7. Click OK to exit the Choose Data Source dialog box. You return to Step 2 of the PivotTable and PivotChart Wizard.

8. Click Next. Step 3 of the PivotTable and PivotChart Wizard appears.

9. Click Finish. A new sheet called Chart1 is created with the chart layout as shown in Figure 17-29. Sheet2, where we began the creation of the pivot chart, is still there. It contains a pivot table layout. A pivot chart requires a pivot table as its source. As we are defining the pivot chart on Chart1, we are also defining the pivot table on Sheet2.

10. In the PivotTable Field List, locate the Dim Plant dimension. Drag the Dim Plant dimension and drop it on the Drop Page Fields Here area of the layout.

11. Expand the Dim Plant drop-down window.

12. Expand the All entry.

13. Select the Maximum Miniatures - Fridley entry.

14. Click OK.

15. In the PivotTable Field List, locate the Time Hierarchy. Drag the Time Hierarchy and drop it on the Drop Category Fields Here area of the layout.

16. In the PivotTable Field List, locate the Product Hierarchy. Drag the Product Hierarchy and drop it on the Drop Series Fields Here area of the layout.

17. In the PivotTable Field List, locate the Total Products measure. Drag the Total Products measure and drop it on the Drop Data Items Here area of the layout. The chart appears as shown in Figure 17-30.

18. Hide the PivotTable Fields List.

19. In the Chart toolbar drop-down list, select Category Axis. We can now set the properties of the category axis (or X axis).

20. Click the Format Axis button in the Chart toolbar. The Format Axis dialog box appears.

21. Select the Custom radio button in the Lines area.

22. Select the fourth line from the top in the Weight drop-down list. The Format Axis dialog box appears as shown in Figure 17-31.

23. Select the Font tab.

24. Select Bold in the Font Style list.

25. Click OK.

26. In the Chart toolbar drop-down list, select Chart Area. We can now set the properties of the area surrounding the chart.

27. Click the Format Chart Area button in the Chart toolbar. The Format Chart Area dialog box appears as shown in Figure 17-32.

28. Check the Shadow check box.

29. Click Fill Effects. The Fill Effects dialog box appears.

30. Select the variant in the upper-left of the Variants area.

31. Click OK to exit the Fill Effects dialog box.

32. Click OK to exit the Format Chart Area dialog box.

33. In the Chart toolbar drop-down list, select Plot Area. We can now set the properties of the chart background.

34. Click the Format Plot Area button in the Chart toolbar. The Format Plot Area dialog box appears. It is almost identical to the Format Chart Area dialog box.

35. Click Fill Effects. The Fill Effects dialog box appears.

36. Select the variant in the upper-right of the Variants area.

37. Click OK to exit the Fill Effects dialog box.

38. Click OK to exit the Format Plot Area dialog box.

39. In the Chart toolbar drop-down list, select Value Axis. We can now set the properties of the value axis (or Y axis).

40. Click the Format Axis button in the Chart toolbar. The Format Axis dialog box appears.

41. Select the Custom radio button in the Lines area.

42. Select the fourth line from the top in the Weight drop-down list.

43. Select the Font tab.

44. Select Bold in the Font Style list.

45. Select the Number tab.

46. Select Number from the Category list.

47. Set Decimal Places to 0. Check the Use 1000 Separator (,) check box.

48. Click OK.

49. In the Chart toolbar drop-down list, select Series Guiding Lights. We can now set the properties of the Guiding Lights series.

50. Click the Format Data Series button in the Chart toolbar. The Format Data Series dialog box appears.

51. Check the Shadow check box.

52. Select the color as shown in Figure 17-33.

53. Click OK.

54. In the Chart toolbar drop-down list, select Series Mythic World and click the Format Data Series button in the Chart toolbar. The Format Data Series dialog box appears.

55. Check the Shadow check box.

56. Select the color two squares above the color selected for the Guiding Lights series.

57. Click OK.

58. In the Chart toolbar drop-down list, select Series Warriors of Yore and click the Format Data Series button in the Chart toolbar. The Format Data Series dialog box appears.

59. Check the Shadow check box.

60. Select the color directly above the color selected for the Mythic World series.

61. Click OK. In the Chart toolbar drop-down list, select Series Woodland Creatures and click the Format Data Series button in the Chart toolbar. The Format Data Series dialog box appears.

62. Check the Shadow check box.

63. Select the color directly above the color selected for the Warriors of Yore series.

64. Click OK. The chart appears as shown in Figure 17-34.

65. Select 3-D Column Chart from the Chart Type drop-down window in the Chart toolbar as shown in Figure 17-35. An error dialog box may appear with a message about combing 2-D and 3-D chart types. If this dialog box appears, click Yes. The chart appears as shown in Figure 17-36.

66. Activate the Year drop-down window at the bottom of the chart. Click the 2005 entry, so it has a double checkmark, selecting all the members at the next level down in the hierarchy.

67. Click OK. The chart now shows the values for each quarter in 2005 as shown in Figure 17-37.

68. Click the Data Table button in the Chart toolbar. A data table showing the chart's underlying data appears at the bottom as shown in Figure 17-38.

69. Select the Sheet2 tab. This is the pivot table serving as the source for our pivot chart as shown in Figure 17-39.

70. Click Save in the Excel toolbar.

Figure 17-29: The PivotChart layout

Figure 17-30: A PivotChart

Figure 17-31: The Format Axis dialog box

Figure 17-32: The Format Chart Area dialog box

Figure 17-33: Choosing a color in the Format Data Series dialog box

Figure 17-34: The completed PivotChart with stacked bars

Figure 17-35: The 3-D Column Chart in the Chart Type drop-down window

Figure 17-36: The PivotChart with 3-D columns

Figure 17-37: The PivotChart with a drilldown into the quarters of 2005

Figure 17-38: The PivotChart with a data table

Figure 17-39: The PivotTable underlying the PivotChart

Additional Features

Microsoft Office 2003 and its Excel spreadsheet system allows for a workbook to be comprised of spreadsheets with Pivot Table and Chart pairs. So, a workbook could be the viewer for several cube sources. Microsoft also has provided an Excel Accelerator for Reporting, which adds functionality to format Excel reports. This allows for locking down columns and rows in the spreadsheet, and performing drilldown using the outline bar.

Another capability of Excel is to save the interactive spreadsheet using a pivot table or a pivot chart to a web page. This enables a user with a browser to perform interactive drilldown and drill across with integrated charts and other Office Web component functions, such as top count, filters, sorting, and even creating new math formulas. Office Web Components (OWC) are growing in popularity as they are exposed through Microsoft's Portal product, Sharepoint Portal Services. A web part on a page in the portal is linked to an OLAP cube and uses Excel's charting, drilling, and filtering functions.