The starting point for bringing OLAP data into Excel is the Data tab in the ribbon. The Data tab has a number of features related to working with tables of numbers in Excel. However, we're going to start with the section on the far left—Get External Data, highlighted in Figure 14-2. Note the listed options—importing data from Access, web-based tables (while we're not covering this here, it's very cool—check it out), and text (importing CSV files).

Figure 14.2. Importing external data into Excel

Those three options, with the options under "From Other Sources" (which we'll look at in a moment), are to create new data connections, which will be stored in the local file system. If you already have the data connection you need, then you can click the Existing Connections button to browse for it. This will allow you to look for connections in either the local file system or a SharePoint data connection library. However, since we don't have a connection already, we want to check options available under the From Other Sources drop-down, shown in Figure 14-3.

Figure 14.3. Importing data from SQL Server Analysis Services

When we select the option to import from Analysis Services, Excel will present us with a dialog for the server name and instance, as shown in Figure 14-4. If you enter the server name by itself, the connection will link to the default instance (remember that you can install multiple SSAS instances on a single server). If you need to indicate a specific instance, then use a backslash: serverinstance. On this dialog you can also indicate whether to use Windows integrated authentication or a specific user name and password.

Figure 14.4. Selecting the server in the data connection wizard

The next page in the wizard lists the databases on the server in a drop-down at the top, as shown in Figure 14-5. Once you've selected a database, it lists the cubes and perspectives in the database. Then you can select a cube or perspective for the connection. (You can also uncheck "Connect to a specific cube or table"—then you'll be prompted to select the cube or perspective when you use the connection.)

Figure 14.5. Selecting a cube

The final page of the wizard, as shown in Figure 14-6, allows you to define the connection file. This includes the file name and location, a description, and the friendly name to be displayed for the connection file. You can also add metadata keywords to be used when indexing the data connection file. We'll cover the Excel Services Authentication Settings later in the chapter.

Figure 14.6. Finishing the data connection wizard

Once you finish the data connection wizard, Excel will use the connection you created, and show the Import Data dialog, which you can see in Figure 14-7. Here you can select whether to create a PivotTable, a PivotChart, both, or neither. You can also choose whether to place the created items in the current worksheet or to create a new worksheet for them.

Figure 14.7. The import data wizard

The Properties button at the bottom of the Import Data dialog will give you options governing the connection in the workbook, as shown in Figure 14-8. The dialog will show you when the data was last refreshed (remember that in Excel you work on a static snapshot of the data until it's refreshed). You can also set properties on when to refresh the data—on a regular basis, or when the file is opened. The connection can always be refreshed manually using the Refresh button on the Data tab in the ribbon.

Figure 14.8. The Connection Properties dialog

The OLAP Server Formatting section allows you to select which aspects of formatting Excel will retrieve from the cube and automatically apply. Finally, the Definition tab is as it sounds—the definition of the connection is stored here, and you can edit it if necessary.

Once you've created the connection, Excel will create the pivot table and pivot charts for you in the workbook. Each of them has a corresponding task pane that's active when they are (click in the pivot table, the pivot table task bar displays; click in the pivot chart, the task bars for the pivot table and pivot chart display). The pivot chart does require a pivot table—if you create a pivot chart on its own (from the Insert tab), Excel will also create an attached pivot table.

When you create a data connection in Excel 2007, Excel creates the pivot table constructor for you, as shown in Figure 14-9. The constructor consists of the placeholder in the worksheet and the task pane on the right-hand side (the task pane can be moved if you choose to.) The task pane has a list of available measures and dimensions in the upper section and the table detail sections in the lower section.

Figure 14.9. A pivot table in Excel 2007

Pivot Table Task Pane

You can change the layout of the task pane by using the drop-down button at the top, as shown in Figure 14-10. Under the layout selector is a drop-down list that lets you filter the measures and dimensions by dimension group. At the bottom of the task pane is a checkbox labeled Defer Layout Update. If you're working with a large cube, some updates may take a few seconds (or longer...). The cube will be more responsive as you pare down the amount of data you're working with, so you may want to check this to hold off on updating the table while you're setting up the initial filters. You can then click the Update button to update the table when you want to, or uncheck the box when you've got a smaller data set.

Figure 14.10. Changing the layout of the task pane

The four areas at the bottom of the task pane are Report Filter, Column Labels, Row Labels, and Values. These correspond to the table, as shown in Figure 14-11. You can either check the check box for measures to show those in the pivot table, or drag them to the Values area. You'll have to drag dimension hierarchies to the other areas as needed.

Figure 14.11. Mapping the task pane areas to the pivot chart

Another aspect of assembling a pivot table that's worth noting is the ability to nest dimensions. Simply dragging another hierarchy to a row or column will nest them in the pivot table, as shown in Figure 14-12. Note that you can create the hierarchy in whichever order you choose—group by Sales Reasons first, and then by Product Categories if you need to.

Figure 14.12. Nesting hierarchies

Pivot Table Ribbons

The easiest way to work with a pivot table is with the PivotTable Tools ribbons, which appear when you click inside the pivot table. There are two ribbons—one for general options and one for design, as shown in Figures 14-13 and 14-14. The ribbons are fairly self-explanatory, but I strongly recommend browsing through them as you start working with pivot tables, as it can be easy to forget they're there and get frustrated trying to work with the pivot table itself. For example, selecting a pivot table to move it in a workbook is nontrivial, but straightforward with the Move PivotTable button on the Options ribbon.

Figure 14.13. The pivot table options ribbon

The most notable function on the Options tab is the ability to rename the pivot table. If you work with Excel named ranges a lot, you may find yourself a bit baffled as to how to name the pivot table by selecting a range on the worksheet. The easy answer is to just click inside the table and rename it on the Options tab. You can also select fields (column and row headers, value fields) and work with their settings, and expand or collapse them.

With the Group Selection and Ungroup buttons you can select members from a hierarchy and create a custom grouping. For example, if you have all 50 states listed, you may want to group them into geographic regions. To do so, just select the states you want to group together and click the Group Selection button. Repeat to create additional groups—as you go, any ungrouped members will be swept into a group called Other.

Figure 14.14. The pivot table design ribbon

The pivot table design ribbon is pretty descriptive—it provides the tools to change the appearance and layout of your pivot table. The drop-down buttons in the Layout section allow you to select whether to show grand totals and subtotals (for rows and/or columns), and whether to insert blank rows between items. The Report Layout selector offers compact, outline, or tabular layouts, as shown in Figure 14-15.

Figure 14.15. Pivot table formats

The checkboxes in the PivotTable Style Options section let you switch the styles for row and column headers on and off, and select whether to shade alternating rows or columns. Finally, the PivotTable Styles gallery provides a number of preformatted styles for pivot tables. You can preview how the style will affect your table by mousing over the styles without clicking on them. Also, you can drop down the gallery with the lower down-arrow to the right of the display, as shown in Figure 14-16.

Figure 14.16. Opening the style gallery

Formatting a Pivot Table

Formatting the contents of a pivot table is as easy as highlighting the cells you want to change the format on and using the controls in the ribbon or the context menu. Note that you won't be able to alter the structure of the pivot table—for example, trying to merge cells in a pivot table will raise an error prompting you with the options for what you're trying to do. You can also use the Cell Styles down shown, shown in Figure 14-17, for quick access to some standard styles and number formats. (I just discovered that there's a quick style on there for Currency with no decimals. I've been doing that by hand....)

Figure 14.17. The Cell Styles gallery on the Home tab of the ribbon

Another powerful option in Excel 2007 is to use conditional formatting for values. With conditional formatting, you can apply a specific style to cells based on the specific numeric value or the value in relation to other values, or even if it contains a specific text or is a date between certain dates. Conditional formatting is under the Conditional Formatting drop-down gallery in the Styles section of the Home tab on the ribbon. Figure 14-18 shows the Conditional Formatting gallery opened to the Highlight Cells Rules selection.

Figure 14.18. Conditional Formatting selectors

Selecting New Rule near the bottom of the drop-down opens a dialog that provides a more fine-grained rule-editing capability. Ultimately you have the option of entering a formula to select which cells to format, and a custom format using any of the Excel cell formatting styles.

One of my favorite format schemes is to apply data bars to a selection of cells, shown in Figure 14-19. The bars behind the numbers give a quick visual indication of the relative magnitude of the values. It is important, however, to be careful not to include any subtotal or total rows, as those will obviously be far larger and dominate the layout.

Figure 14.19. Data bars in a pivot table

Now that we understand how to create and format a pivot table, we also want to be able to use these values in other calculations (that's a major reason we're using Excel, right?). So let's take a look at the ins and outs of using pivot table values in formulas.

Formulas Using Pivot Table Values

Let's say you wanted to create a column next to your pivot table showing what the numbers would look like with 10% growth added on top. So you click in a cell, type an equal sign, and then click on a cell in the pivot table to reference it. You'll end up with something looking like this:

=GETPIVOTDATA("[Measures].[Internet Sales Amount]",$A$3,
"[Product].[Product Categories]",
"[Product].[Product Categories].[Product].&[374]")

This is the function Excel puts in place to fetch the pivot table value, and it's the reference you'll get. This will work well enough—type "*110%" afterwards and you'll see it calculates fine. The problem is when you copy that formula and try to paste it for all the values in the column. You'll get the same value for every cell. Why is that?

The GETPIVOTDATA function in this case has four arguments. These are the data field (Internet Sales Amount), a descriptor for the pivot table ($A$3, the cell reference for the table anchor), the field, and then the unique name for the specific cell. So when we copy and paste the cell reference, it keeps the unique name and we get the same value.

Couldn't we just type a cell reference for the value? Well, that would work, except that if either rows or columns contain a hierarchy, the user can expand and collapse the members of that hierarchy, giving us the result shown in Figure 14-20. Since the GETPIVOTDATA formula uses unique names to identify cells, then so long as the pointer to the pivot table is valid, the value will still work.

Figure 14.20. The dangers of using cell references with pivot tables

Note

One problem you can still run into is if the value you're referencing gets hidden. For example, in Figure 14-20, if you were referencing a specific bike model and you then closed up the subcategory, the reference would break as well (but it would be valid again if the user opened that category again).

Now that we have a feeling for putting our data together and formatting it in a pivot table, let's create some visual displays of our data with pivot charts.

Being able to visualize data is every bit as important as actually viewing the data itself. In this section we're going to create pivot charts in Excel to give a graphical view of the data in the pivot table. Figure 14-21 shows an example of such a chart.

It's important to remember that a pivot chart is always bound to a pivot table. When we talk about ProClarity and SQL Server Reporting Services charts later in the chapter, we'll look at some very powerful charting capabilities as the result of linking directly to SSAS cubes. However, Excel pivot charts are limited to what's in the pivot table.

Figure 14.21. A pivot chart showing sales by product category over twelve months

There are three ways to create a pivot chart in Excel:

There are many types of charts available in Excel, including bar charts, line charts, pie charts, and area charts. For each chart type there are various stylistic subtypes, as shown in Figure 14-22 for bar charts. Be careful not to spend too much time worrying about which type of chart to use—remember that the goal is to convey information, not win a beauty contest. For a more in-depth discussion about charting and chart types, I recommend Show Me the Numbers by Stephen Few (2004, Analytics Press).

Figure 14.22. Selecting a bar chart type

Each of the subtypes is generally a minor stylistic variation on the main chart type, but there are some significant standouts, most notably the stacked variations (where values are added together or presented as percentages).

As with the pivot table, when you create a pivot chart, Excel presents you with a number of new tabs on the ribbon to manage the chart, shown in Figure 14-23. This time we have four tabs:

Figure 14.23. The PivotChart Tools tabs in Excel

Pivot charts really are fairly straightforward—since they're bound to the underlying pivot table, most of your work will be done ensuring the data in that table is in the proper format for the chart. In Exercise 14-1 we're going to use the AdventureWorks cube to create a pivot table and pivot chart so we can get a feel for how they work together.

In my experience, Excel 2007 is awesome for pivot tables—if you need to do tabular analysis on SSAS data, Excel is a top-tier client. Unfortunately, the fact that the pivot chart is bound to a pivot table, combined with the lack of ability to manipulate data in the chart, makes it pretty limited. Later in this chapter we'll look at Reporting Services and ProClarity for more powerful charting tools. For now let's look at the other member of the Office family that understands Analysis Services—Visio.

Between the new tablix control and advanced charting engine, Reporting Services is more powerful than ever. Formatting reports is more intuitive, and far more WYSIWYG than previously.

The report designer is once again BIDS—in this case you can create either a new project with a wizard or just an empty project. Reports use data connections—either shared connections deployed to the server, or connections embedded in the report. (The benefit of a shared connection is that it's easy to modify it in either BIDS or SSMS—to point it at a different server, change a password, etc.)

Once you've created a report project, report design is fairly straightforward—create datasets for your needs, and then create regions on the report using the datasets to populate them. You have three types of data regions: tablix regions, charts, and gauges, as shown in Figure 14-41.

Figure 14.41. A report in design mode with tablix, a chart, and two gauges

Let's take a look at each of these data containers and how they can relate to visually presenting Analysis Services data.

In SQL Server 2005 Reporting Services, you had the option of creating a report with a table, as shown in Figure 14-42, or a matrix, as shown in Figure 14-43. Tables were great for straight tabular data—record after record (think green-bar reports). Matrices were used for aggregating data (a topic that should be near and dear to our hearts now)—generally think of them like pivot tables.

Figure 14.42. A table in SQL Server Reporting Services

Figure 14.43. A matrix in SQL Server Reporting Services

These two options were "good enough" for most purposes; however, most report developers would eventually learn that many reports don't fall squarely into being either tabular or a matrix, but rather a hybrid between the two. For example, a report user may want the report in 14-43 to have some summary information as well. Consider the report in Figure 14-44, which shows the profit margins by country. Veterans of SQL Server 2005 Reporting Services know this is a nontrivial task to accomplish.

Figure 14.44. Matrix with a summary column

Adding columns outside a grouping, splitting cells in the report, and adding other forms of data outside the main paradigm of "table" or "matrix" have generally meant either doing gymnastics in SQL to get a data set that roughly conforms to the required report, or writing code to generate the data values. As a report designer, we don't have the option to tell our user "your requirements don't match what the software can do. Please adjust your expectations accordingly."

In response to the limitations of the table/matrix approach in SSRS 2005, Microsoft has added a component called tablix to Reporting Services. The tablix data region combines the capabilities of the matrix and table—you can add pivot-type features to a table, table-type features to a matrix, and "mix and match." The end result is that reports like the one shown in Figure 14-45 are possible.

Figure 14.45. A complex report using tablix in SQL Server Reporting Services 2008

Note that we have a matrix (products broken down by calendar quarter), but there's also a year-over-year percentage and trend, a sparkline chart, total sales, and breakdown by top three regions. Before 2008, a chart request like this would have meant a significant amount of code behind the scenes. Tablix makes it easy.

If you've cracked open BIDS and looked for Tablix in the toolbox, you won't find it. You'll see Table and Matrix items, but no Tablix. Where is it? Well, you're looking at it. Both the table and matrix toolbox items are simply special cases of the tablix control—adding either of these gives you the same capabilities in the long run. This was an excellent design decision on Microsoft's part, as you won't ever run into a situation where you'll need a feature in a table and think "I can do this in a matrix..."

So you start with a table or matrix, and then the tablix features start to reveal themselves. For example, in our matrix with the summary columns we just have to right-click on the rightmost column to see the context menu shown in Figure 14-46. By having the ability to add columns (either inside or outside the current grouping) we've got a lot more flexibility.

Figure 14.46. Manipulating a tablix item

Let's take a moment to build a quick report using the tablix control—this will be an easy introduction to Reporting Services as well as leveraging the tablix capabilities.

For more information about tablix, a good place to start is "Understanding the Tablix Data Region" at http://msdn.microsoft.com/en-us/library/bb677552.aspx.

Now that we've looked at tabular results of numerical data, let's take a look at a more visual representation using the new charting engine in SSRS 2008.

The charting engine in SSRS 2005 wasn't too bad, but it had its share of challenges. In 2007, Microsoft licensed the reporting engine from Dundas Software. The result of this effort is the charting engine in SSRS 2008. SSRS 2008 includes new chart types, advanced support for multiple series in a single chart, secondary axes, and a host of new layout features. An advanced SSRS 2008 chart is shown in Figure 14-54.

Figure 14.54. A chart in SQL Server Reporting Services 2008

To me, one of the best enhancements is access to properties. In SSRS 2005, all charting properties were accessed through a single properties dialog, as shown in Figure 14-55. This was limiting to say the least—any little change or tweak to an axis or chart meant digging through pages of properties and hoping you had the right one—lots of trial and error.

Figure 14.55. Working with charts in SSRS 2005

In SSRS 2008, you can format each chart component by simply right-clicking on it and selecting Properties. For example, to format the y axis in SSRS 2005, you have to right-click on the chart, select Properties, select the y-axis tab, and then enter the format code for the numerical format you want (yes, you have to know the format codes...). In SSRS 2008, you simply right-click on the y axis, and select Axis Properties to open the Value Axis Properties dialog shown in Figure 14-56. Also note that as you make changes in SSRS 2008, they are shown dynamically on the chart, though the dialog may be covering the part of the chart you're changing...

Figure 14.56. Configuring an axis in SSRS 2008

The best way to understand working with charts in Reporting Services is to build one. In Exercise 14-3 we'll create a chart from dimensional data, including two different data series.

SQL Server 2005 Reporting Services introduced a client-side ad-hoc report designer, named Report Builder. It was fairly good, but suffered from the limitation that reports could be built based only on predesigned report models (basically a lightweight cube residing in the report server).

SQL Server 2008 Reporting Services brings us Report Builder 2.0, a free download from Microsoft.com. (You can find the download link at http://www.microsoft.com/sqlserver/2008/en/us/report-builder.aspx). Report Builder 2.0 allows end users to create ad-hoc reports from any number of data sources, as shown in the default connection type list in Figure 14-63.

Figure 14.63. The connection types available in Report Builder 2.0

When you create a new report, you can either create the report by hand, or use one of the two wizards (Table/Matrix or Chart). The wizards will walk you through the familiar process of creating a data connection, and building a query. If you selected Table/Matrix, then the first layout screen will be a little different, as shown in Figure 14-64, but the rest is pretty straightforward.

Figure 14.64. Table/Matrix layout in Report Builder 2.0

For the most part, Report Builder works exactly like BIDS for designing reports (at least in basic data access, layout, and formatting). Once a user finishes a report design, the user can save it back to the Report Server or SharePoint library where reports are published.

That finishes up the quick tour of Reporting Services and Report Builder. Let's take a look at our next stop for displaying dimensional data—Microsoft Office SharePoint Server (MOSS) 2007.

I've always found KPI lists to be a little odd. They seem very powerful at first—a web-based scorecard key performance indicator feature that can be hooked to any number of data items (including SharePoint list data). However, when you've worked with KPIs in other products, the KPIs in MOSS come up very short.

MOSS KPIs can use data from several sources, as shown in Figure 14-67. Once you select a data source, you'll be given a definition page appropriate for the data source.

Figure 14.67. KPI List data sources

The definition page for SSAS data requires you to select a data connection from a library on the SharePoint server. Once you select a data connection to an Analysis Server, MOSS will provide a list of KPIs on the server, as shown in Figure 14-68. You can then give the indicator a name, and a URL for the KPI to use as a link to details.

Figure 14.68. Selecting a KPI in MOSS

That's it—everything about the KPI must come from the definition in SSAS. This is, of course, a blessing and a curse. It follows our "one version of the truth" philosophy to the letter. However, any changes we need in a KPI (for example, tweaking a display format) require making changes to the cube. KPIs are then stored in a SharePoint list, and dashboard presentation is about configuring a view for the KPI list.

As I mentioned, it is an odd little beast. It does exactly one thing. I feel it would be a good tool for SSAS KPIs as a lightweight indicator, or for creating KPIs from data stored in SharePoint lists. But they're not useful for much else.

Excel Services is a very robust platform—the server enables processing large, complex Excel spreadsheets on server hardware, and also publishing Excel spreadsheets to a thin client. This enables consumption of Excel spreadsheets as analytics reports.

Excel Services is a pretty robust subject, and the configuration alone is fairly complex. For that reason I'm going to leave covering the topic to the experts, and recommend Professional Excel Services by Shahar Prish (Wrox, 2007) if you want to dig more deeply into using Excel Services.