Accessing and Analyzing Data with Microsoft Excel (Bpg-Other)

Organizations that record and store data over long periods of time, or organizations that record and store hundreds of megabytes, gigabytes, or even terabytes of data face the daunting task of trying to make sense of the data so that they can inform and improve their business decisions. For instance, sales teams might have data gathered over several years that is dispersed throughout their corporate offices as well as in sales offices throughout several regions. A retail organization might need to figure out which products have sold the best during a peak shopping season and which poorly selling products should be discounted or discontinued in the future. Manufacturing organizations might need to analyze historical defect counts and other factors that could require the business to retrain workers or retool machinery. Insurance organizations might want to add new lines of coverage or face the reality of raising policy premiums because of the quantity and severity of past claims.

PivotTable reports and PivotChart reports can be great data analysis tools for these types of data analysis scenarios. However, when working with large data sources, data sources distributed geographically, or data collected over long periods of time, creating several reports, linking them together, and synchronizing their data can be time-consuming. And viewing such reports on line can be difficult because of a limited screen area in which to show these reports and perspectives on the data all at once. Displaying multiple business dimensions or measures on a PivotTable report or PivotChart report can result in visual clutter or even be confusing. Microsoft Data Analyzer addresses these issues and provides tools to show trends over time and locate similar members in a dimension for business data comparisons.

You should use Data Analyzer when you want to see a high-level, executive summary of your data. You can then export the data from Data Analyzer to PivotTable reports when you want to diagnose what is causing specific data trends or anomalies. For instance, you could use Data Analyzer to determine the sales for a particular product over the last three years, which you think have declined because of either poor marketing or by selling the product in the wrong geographical market. From there, you could use a PivotTable report to look at the details of each year’s sales figures, comparing them to the amount spent on advertising campaigns in the geographical market and the volume of products stocked in each store in that market.

Microsoft Data Analyzer was specifically created to access OLAP data on an OLAP server or in an offline cube file. However, you can use the techniques described at the end of Chapter 8, “Analyzing OLAP Data with Microsoft Excel,” to create an offline cube file in Excel from non-OLAP data. You can then use Data Analyzer to view the offline cube file’s data.

Exploring the Data Analyzer User Interface

Before you connect to and analyze OLAP data with Microsoft Data Analyzer, you should become familiar with its special user interface components. The application’s main window is shown in Figure 9-1. The data shown comes from a sample offline cube file (Airline.cub) provided with Data Analyzer.

Figure 9-1: The Microsoft Data Analyzer main window.

Main User Interface Components

The Data Analyzer user interface consists of five main components, which include the menu bar, the toolbar, the status bar, the navigation bar, and a group of one or more dimension panes.

• The menu bar, located directly beneath the title bar, contains familiar Office-style menus such as File, Edit, View, Tools, and Help.

• The toolbar, located beneath the menu bar, contains shortcuts to menu commands.

• In Data Analyzer, the bar lengths and pie-slice sizes typically indicate numerical measurements, such as sales volumes, units produced, and so on. The colors of the bars and pie slices typically represent percentages or ratios such as profit margins, progress against sales quotas, manufacturing defect ratios, and so on. The status bar, located at the bottom of the screen, displays information about the current data source, including lengths and colors. From left to right, the status bar shows the OLAP server name or path to an offline cube file, the catalog name (the name of an OLAP database or offline cube file), the cube name, what the length of the bars or the size of the pie slices represents, and what the colors of the bars or the pie slices represent. (An OLAP database can contain more than one cube. In the case of offline cubes, the offline cube’s file name is the same as the catalog name.)

• The navigation bar, located directly above the status bar, contains one button for each OLAP dimension that’s displayed. Clicking these buttons (in this example Destinations, Report Date, and Types Of Aircraft) hides or displays the associated dimension.

• Dimension panes, a sample of which is shown in Figure 9-2, are located between the navigation bar and the toolbar.

  Figure 9-2: A Data Analyzer dimension pane.

Dimension Pane Components

Each dimension pane in Data Analyzer corresponds to a dimension in the OLAP source data. In addition to displaying data in bars, pie slices, or grids, the dimension pane includes components such as the handle, the label, the length scale, the Display Type toolbar, and the dimension toolbar.

• The thin line at the top of the dimension pane, called the handle, can be used to drag the dimension pane to a new screen location. Next to the handle are Minimize, Maximize, and Close buttons that are included in typical Microsoft Office application windows.

• Below the handle is a label showing the dimension’s name and the current level. The names of the dimension and the level will be similar if the top level of a dimension’s hierarchy is displayed, as is the case with the pane shown in Figure 9-2 (the Types Of Aircraft dimension and the Type Of Aircraft level).

• The length scale, located directly below the dimension and level name, can be used to change the zoom level of the data. Sliding the magnifying glass icon to the left of the length scale zooms in on the level of data, showing smaller values, while sliding the magnifying icon to the right of the length scale zooms out on the level of data, showing values at the higher end of the range. In the sample, sliding the magnifying glass icon all the way to the left zooms in from 100K/200K to 20K/40K; sliding the magnifying glass icon all the way to the right zooms out from 100K/200K to 200K/400K. Length scales can be turned on or off by clicking Dimension Length Scales on the View menu.

• The Display Type toolbar, located at the bottom of the dimension pane, provides buttons with which you can switch the dimension pane’s view to display bars, a grid, or a pie chart. To show the toolbar, rest your mouse pointer on the eye icon, and then click Bars, Grid, or Pie Chart.

• The dimension toolbar, located directly below the dimension and level name and to the left of the length scale, contains buttons to change the dimension pane’s display characteristics. Dimension toolbars can be turned on or off by clicking Dimension Toolbars on the View menu.

Dimension Toolbar Buttons

From top to bottom, the buttons on the dimension toolbar include the following:

• Drill Up decreases the level of data details displayed by moving up one level in the dimension’s hierarchy. In the sample, you can drill up from the Type Of Aircraft level to the All level.

• Drill Down increases the level of detail displayed by moving down one level in the dimension’s hierarchy. For example, you could drill down from the All level to the Type Of Aircraft level.

• Default Members displays the default members as defined in the OLAP cube. For example, the OLAP cube creator can specify that the Type Of Aircraft level (instead of the All level) is the level that is initially displayed.

• Filter By All Visible Members selects all of the visible members in the dimension. You’ll learn about how to select members later in this chapter.

• Hide Members Not In Filter hides the members that are not selected.

• Filter By Criteria allows you to define criteria by which to select members to display.

• Reverse Filter selects members that are not selected and removes members that are selected from the selection.

• Hide Empty hides members that have no data.

• Length sorts members by bar length, pie slice size, or grid figure.

• Color sorts members by bar or pie-slice color.

• Name sorts members by name.

• Natural sorts members by any default sort order defined by the OLAP cube creator before the data source connection was first made.

• Properties can be used to set miscellaneous dimension pane properties, such as whether to respect the default sort order as defined by the OLAP cube creator or define your own sort order.

Your Turn

In this exercise, you will familiarize yourself with basic components of the Data Analyzer user interface.

1. Start Data Analyzer. If the Microsoft Data Analyzer Startup dialog box appears, click Cancel to close it.

2. On the toolbar, click the Open button.

3. Locate and click the file Airline.max, and then click Open.

   In a default installation of Data Analyzer, this file is located at C:\Program Files\Microsoft Data Analyzer\Data Analyzer 3.5\. The Airline.max file contains information for connecting to the Airline.cub offline cube file in the same folder as the Airline.max file.

4. On the navigation bar, click Types Of Aircraft to hide the Types Of Aircraft dimension pane.

5. On the status bar, notice the labels Length: Total Revenue and Color: Profitability. The bar lengths (or pie-slice sizes) represent the total revenue; the bar and pie-slice colors represent the profitability percentage.

6. On the Report Date dimension pane, on the dimension toolbar, click the Drill Down button. This displays the next level in the Report Date dimension, moving from the Year level to the Month level. The Report Date dimension pane’s text should now read Report Date: Month to reflect this operation.

7. The bars in the Report Date dimension pane are sorted by month with the earliest month (01/2001) at the top. On the Report Date dimension pane’s dimension toolbar, click Length to sort the bars by length with the longest bar at the top. The bar for 07/2001 is now at the top.

8. In the Report Date dimension pane, rest your mouse pointer on the eye icon and then click Pie Chart to switch to a pie chart display. Click Grid to switch to a grid display. Click Bars to return to a bars display.

9. On the File menu, click Close.