By Colin Wilcox

Do you spend a lot of time analyzing PivotTable® reports and trying to answer business intelligence questions? If so, you can save time by using Data Analyzer to generate preformatted reports in Excel. This is part 1 of a two-part column that introduces Data Analyzer and walks you through a business intelligence exercise.

I recently had the chance to watch a team of financial analysts at work. I saw a classic business-intelligence process: The analysts connected to a huge database of sales information that included online analytical processing (OLAP) cubes. (Put briefly, a cube takes historical data from your databases and aggregates it into a structure that permits sophisticated analysis. For more information about cubes, see Introducing the Technology Behind Data Analyzer.) Then, the advisors used Data Analyzer to scan the cubes for information about slow sales or lagging profits. From there, they used Excel to create PivotTable reports for use by management. Management used the data to help remedy any problems.

You can do the same thing with Data Analyzer and Excel, and you don't need huge amounts of data. This is the first of a two-part column that provides a brief business-intelligence exercise that explains how to use Data Analyzer and Excel together to:

• Use Data Analyzer to find problem areas in data.
• Create PivotTable reports from the problem data by exporting the data easily from Data Analyzer into Excel.

To follow the steps in this section, you need Excel 2000 or later and Data Analyzer version 3.5. If you don't have Data Analyzer 3.5, you can order the 120-day trial version from the Microsoft Data Analyzer Web site. For additional information or help with installation, please see Frequently asked questions About Data Analyzer.

Analyzing the data for problems

Data Analyzer contains a sample data cube and a sample view. A view is a file that Data Analyzer uses to present data visually. Before we get started with the exercise, let's take a tour of the view for this example.

Start Data Analyzer, and in the Microsoft Data Analyzer Startup dialog box, double-click C:\Program Files\Microsoft ... \Airline.max (the full path of this file is C:\Program Files\Microsoft Data Analyzer\Data Analyzer 3.5, with a typical installation of Data Analyzer). This opens a sample view containing data for a fictitious airline. Here's what you see:

If you're unfamiliar with a Data Analyzer view, here's a quick tour (otherwise, go on to the exercise below):

• Bar charts represent quantities  In this case, they represent the gross revenues earned by each route and type of aircraft in the airline. The quantities that the bar graphs represent will vary depending on how you or your IT department set up your data cubes.
• Colors assigned to each bar represent a ratio  In this case they represent profitability, with red indicating low or negative profitability and green representing high or positive profitability. However, like the bar charts, the meanings that the colors convey vary from cube to cube.

  Keep these facts in mind as you proceed: Colors represent a range of numbers. Typically, green represents the high end, red represents the low end, and shades of yellow and green represent values in between. Here's where most people get confused: Red does not always represent a negative value, and green does not always represent a positive value. For example, if you use colors to represent a range of numbers from -50 to -10, Data Analyzer represents -10 as green because it's the highest value in the range of numbers.

• Panes in the view represent dimensions  Dimensions are the categories that give meaning to the data—for example, Destinations and Types of Aircraft are dimensions in the view shown above. The individual data points in each dimension are called members.

Now that we've walked through a view, let's analyze the data to find any problem areas:

1. In the Destinations dimension pane, double-click the West Europe member bar. This filters out the data from the other routes and shows more detailed data (members) for this route.
2. Point to the scroll bar on the right side of the Destinations dimension pane. It changes to a set of colored stripes. You can use the toolbar to quickly see if any of the individual members in the dimension show low profitability. Notice that you see some near the bottom of the pane.
3. Scroll down the items in the Destinations dimension pane and point to the Lisbon member bar. An information tip appears and tells you that the Lisbon route shows 7.13 profitability for 2001. Do the same for the remaining three routes in the dimension. You can see that the Düsseldorf route lost money (a loss of 11.8 percent).
4. Click the Düsseldorf member bar, and note the changes in the Types of Aircraft dimension pane. First, the jumbo and super jumbo member bars appear as gray and as two-dimensional, meaning that those types of aircraft don't fly on this route. Second, you can see that of the three types of aircraft that do fly to Düsseldorf, commuter airplanes earn the most revenue. However, when you point your mouse cursor at the commuter member bar, you also see that the aircraft posted a 23.03 percent loss.

Creating reports from your findings

So far, you have located a problem, and you found it in less time than you would have using column-and-row spreadsheets or PivotTable reports. Next, you decide to create a report for your supervisor. Follow these steps:

1. Press and hold the CTRL key and click the Lisbon, Düsseldorf, Leipzig, and Bonn member bars. This filters out all data except the data for those four routes.
2. Point to the member bars in the Types of Aircraft dimension pane. You can see that the regional aircraft also lose money, that some routes fly jumbo jets, and the jumbo jets are profitable. Here's a key point to remember: You would not have seen the relationships between the aircraft types and the various routes as quickly or as easily using conventional spreadsheets and reporting techniques.
3. On the main toolbar, click the Export to Microsoft Excel PivotTable Report .
4. Click Next to close the introductory screen, and then click Next again to accept the default placement of the various report elements.
5. Move all the available measures from the Available Measures pane to the Selected Measures pane, and then click Finish.

The wizard starts Excel and places the report data in a PivotTable report in a new workbook. If you click the Region Name field in the report, you see that you have the option of restoring some or all of the filtered data to the report. You decide to leave the report in a compact state for now, but you also decide to highlight a key piece of data—the profitability measure—so that your boss and other report consumers can find it easily. To do this, return to Excel and follow these steps:

1. Use your mouse to highlight the report.
2. On the Format menu, click Conditional Formatting.
3. Ensure Cell Value Is appears in the first list.
4. From the second list, select equal to.
5. Type Profitability in the right-hand box.
6. Click Format.
7. Click the Patterns tab, select the light yellow color pattern, and click OK twice to close the Conditional Formatting dialog box.

Now, when other users add or remove report items, the profit values will always be easier to find. And you just created a compelling business intelligence report, quickly.

In the next Power User column

Once you have that easy-to-read Excel report, what else can you do with it? Quite a bit! The next column, Export Data Analyzer views to PivotTable reports, shows you how to:

• Share reports with your coworkers in a way that helps protect your data.
• Answer "why" questions about your data.
• Explore OLAP cubes directly from Excel.

Keep sending those e-mails!

We look forward to receiving your e-mails at pwruser@microsoft.com. We really want this to be your column, so please send us your comments and favorite handcrafted Office solutions. Remember, we will not be able to feature every Office solution that we receive, we will not have the time to respond to all of your e-mail, and we are not technical support representatives. But we may feature your solution in an upcoming column.

More information

• If you're new to business intelligence and Data Analyzer, see Welcome to the Data Analyzer Tutorial.
• For links to advanced information about Data Analyzer, see the articles at Office XP Data Analyzer.
• For more information about PivotTable reports, see the articles at PivotTable Reports and Charts.

About the author

Colin Wilcox writes for the Office Help team. In addition to contributing to the Office Power User Corner column, he writes articles and tutorials for Microsoft Data Analyzer.