4
Lesson 3: Analyzing your data

It will probably take you about 45 minutes to complete this lesson.

In lesson 2 you learned how to create your own Discoverer workbook. In this lesson, we return your copy of the Video Tutorial Workbook to learn how to use Discoverer's powerful analysis tools to perform ad hoc analysis. For example, you will use Discoverer to answer typical business questions, such as:

• Why are sales higher in particular regions?

• What other factors contribute to higher sales?

• How do I analyze data visually using graphs? 

This lesson consists of the following exercises:

• "Exercise 1: Sorting rows and columns"

• "Exercise 2: Pivoting rows and columns"

• "Exercise 3: Drilling in and out of detail"

• "Exercise 4: Adding totals to worksheets"

• "Exercise 5: Adding percentages to worksheets"

• "Exercise 6: Adding mathematical calculations to worksheets"

• "Exercise 7: Adding a ranking calculation to a worksheet"

• "Exercise 8: Graphing worksheet data"

• "Lesson summary"

Exercise 1: Sorting rows and columns

In this exercise you will learn how to sort worksheet data in Discoverer.

Discoverer displays worksheet data arranged into rows and columns, similar to how data is displayed on a spreadsheet. In addition to basic sorting (e.g. A-Z, 1-9), Discoverer provides more sophisticated group sorting, which enables you to remove repeating values and automatically display sub-totals for each group.

To sort data on a worksheet:

1. Connect to the Video Store Tutorial database and open the Video Tutorial Workbook.

   Hint: See Lesson 1: "Exercise 1: Starting Discoverer Plus" and "Exercise 2: Opening the tutorial workbook".

2. Display the Tabular Layout worksheet.

   When the worksheet is displayed, notice that it is sorted alphabetically by Region (i.e. in the order Central, East, West).

3. From the Page Items area, select 2000 from the Year drop down list.

   Figure 4-1 Tabular Layout worksheet

   
   
   Text description of the illustration a1.gif

   Instead of sorting by Region, you want to sort by Profit SUM, so that you can analyze performance by arranging Profit SUM figures in descending order, (i.e. highest first).

4. Choose Tools | Sort to display the Edit worksheet: Sort tab.

   Figure 4-2 Edit Worksheet: Sort tab

   
   
   Text description of the illustration a2.gif

5. Select the Region item, then click Delete to remove the existing sort.

6. Click Add and select Profit SUM from the Add drop down list.

   The Add drop down list shows items available to the worksheet. Choosing an item from the Add drop down list causes Discoverer to sort using that item.

   Figure 4-3 Adding a sort item

   
   
   Text description of the illustration a3.gif

7. Select Low to High from the Direction drop down list.

   Figure 4-4 Setting the sort order

   
   
   Text description of the illustration a4.gif

8. Select None from the Group drop down list.

9. Click OK to save the details and display the worksheet.

Figure 4-5 Worksheet sorted on Profit SUM

Text description of the illustration a5.gif

The data is now sorted by Profit SUM, so that you can easily see that the Video Sale Department in the East Region has the highest profits in the year 2000 ($109,637).

Find out more about what you can do with Discoverer's sorting features by experimenting with different sorts on different items. Go back to step 8 and select Group from the Group drop down list instead of None to see how that affects the worksheet. You will see how Discoverer removes repeated group item names and automatically add totals to the worksheet.

When you have finished, if you want to return to the original Video Tutorial Workbook workbook, close the current workbook without saving it.

In this exercise you have learned how to sort data in Discoverer. Now you are ready to use Discoverer's pivoting capabilities to rearrange worksheet data.

Exercise 2: Pivoting rows and columns

In this exercise, you will learn how to rearrange the tutorial worksheet Tabular Layout to display data for one region over many years rather than one year over many regions.

Changing the positions of page items and axis items is called pivoting. In Discoverer, you can pivot items at any time to produce reports in exactly the layout you need. This ability to effortlessly pivot worksheet items is one of the many things that make Discoverer so powerful.

1. Display the Tabular Layout worksheet, if it is not already displayed.

   Figure 4-6 Tabular Layout worksheet

   
   
   Text description of the illustration a1.gif

   Notice that the worksheet shows figures for all regions (Central, East, and West) over one year (i.e. 2000). Notice also that the Year item is positioned in the Page Items area.

   Remember that we want to rearrange this worksheet so that it only displays data for one region over many years. Discoverer's pivoting capabilities make this task easy.

2. Choose Sheet | Table Layout to display the Edit Worksheet dialog at the Table Layout tab.

   Figure 4-7 Edit Worksheet: Table Layout tab

   
   
   Text description of the illustration a5a.gif

   By now you have figured out that you do most of your worksheet editing in Discoverer with the Edit Worksheet dialog. As you would expect, you use the Table Layout tab on the Edit Worksheet dialog to pivot items.

3. Drag and drop the Calendar Year item from the Page Items area down to the left of the Region item on the body of the worksheet.

   When you are moving Items, a black bar indicates where the column will appear.

4. Drag and drop the Region item from the top axis to the Page Items area (where the Calendar Year item used to be).

   Figure 4-8 Edit Worksheet: Table Layout tab after items have been repositioned

   
   
   Text description of the illustration a6.gif

5. Click OK to close the Edit Worksheet dialog and display the worksheet.

   Notice that the worksheet now shows all the years on a single page. Notice also that the worksheet displays data for only one region (Central), which is positioned in the Page Items area.

   Figure 4-9 Tabular Layout worksheet with Region item in the Page Items area

   
   
   Text description of the illustration a7.gif

6. To display data for a different region, select a new value from the Region drop down list in the Page Items area.

   Notice how the worksheet is updated automatically when you select a new Region.

   Having rearranged the tutorial worksheet, you are now ready to explore Discoverer's drilling capabilities.

Exercise 3: Drilling in and out of detail

In this exercise you will learn how to drill into and out of data in Discoverer to change the level of detail that you display on a worksheet.

Now that you see data for each department group sorted by year, you might wonder 'Are sales better in some quarters than others?' Right now your worksheet only shows a summary of each year. To answer this question, you need to drill into the data and show detailed sales information by quarter.

You can drill on any item that has a drill icon next to it. On the worksheet, notice a small triangle next to the Year item.

Figure 4-10 Drill icon on Tabular Layout worksheet

Text description of the illustration a8.gif

This is a drill icon, which indicates that you can drill deeper into this item to display more detail (e.g. quarterly, or weekly data).

To drill into and out of data:

1. Click the drill icon next to the Year item heading to displays drill options.

2. Select Calendar Quarter from the drill options to display quarter level detail on the worksheet.

   Figure 4-11 Drill options on Year item

   
   
   Text description of the illustration a9.gif

   Discoverer adds a new column to the worksheet for the Quarter item. Notice that the profit data is also now broken down for each Quarter. Notice also the drill icon next to the Quarter item heading, which means that you can drill down even further to display more detail.

   Figure 4-12 Tabular Layout worksheet

   
   
   Text description of the illustration a10.gif

3. To drill out of the quarterly data back to the original worksheet, (or collapse the data), click the drill icon next to the Quarter item heading and select Calendar Year from the list of options.

   Note: When you want to remove detail data, you can also click the drill icon and select Collapse.

   Discoverer removes the Quarter item from the worksheet and re-displays the consolidated profit figures at Calendar Year level.

   Having explored Discoverer's drilling capabilities, you are ready to aggregate the worksheet data using total items in Discoverer.

Exercise 4: Adding totals to worksheets

In this exercise you will learn how to aggregate worksheet data by calculating totals (e.g. sum, average, minimum). In Discoverer, you do this by adding a total item to a worksheet.

1. Display the Crosstab Layout worksheet, if it is not already displayed.

   Figure 4-13 Crosstab Layout worksheet

   
   
   Text description of the illustration a11.gif

   Notice that the worksheet contains profit figures by region and year. In this exercise, we want to calculate profit totals for each year.

2. Choose Tools | Totals to display the Edit Worksheet dialog at the Totals tab.

3. Click New to display the New Total dialog.

   Figure 4-14 New Total dialog

   
   
   Text description of the illustration a12.gif

4. Click the 'Which data point would you like to create a total on?' drop down list and select Profit SUM.

5. Click the 'What kind of total do you want?' drop down list and select 'f(x) Sum'.

   Notice that Discoverer offers a large range of other totals to choose from, such as Average, Maximum, and Minimum. In this case, you want to select 'f(x) Sum' to add up the Profit SUM item values.

6. Select the 'Grand total at bottom' radio button from the 'Where would you like your total to be shown?' options.

   Notice that because we also have numeric data running laterally over the crosstab worksheet, the New Total dialog also has a 'Grand total on right' radio button. If we selected the 'Grand total at bottom' radio button we would calculate a total for each region.

7. Click OK to save the details and close the New Total dialog and return to the Edit Worksheet dialog.

   Notice that the new total, called 'Grand Total Rows Sum for Profit SUM', is displayed in the totals list. The selected check box next to the item indicates that it is turned on (i.e. active).

   Figure 4-15 Edit Worksheet dialog: Totals tab

   
   
   Text description of the illustration a13.gif

8. Click OK to close the Edit Worksheet dialog and display the worksheet.

   Discoverer refreshes the worksheet and displays a new set of totals at the bottom of the worksheet. Here, we see a total profit value for each year. For example, the total profit value for the year 2000 is $231,222.

   Figure 4-16 Crosstab Layout worksheet with total item

   
   
   Text description of the illustration a14.gif

By using Discoverer's range of totals, you can create powerful business reports. Repeat the exercise and experiment with different styles of total and types of total. For example, you might average profit figures for each region.

Having calculated totals in Discoverer, you are ready to create percentages.

Exercise 5: Adding percentages to worksheets

In this exercise you will learn how to add a percentage to calculate how each department contributed to annual profits. You will find out which departments are performing well and which are not performing well.

To answer these questions, we will add a new column to the worksheet to calculate this percentage value for you.

To add a percentage item:

1. Display the Tabular Layout worksheet, if it is not already displayed.

2. Choose Tools | Percentages to display the Edit Worksheet dialog at the Percentages tab.

3. Click New to display the New Percentage dialog.

   Figure 4-17 New Percentage dialog

   
   
   Text description of the illustration a15.gif

4. Type Percentage of Annual Profit in the What do you want to name this percentage? field.

   You want Discoverer to find the grand total of the profits for all departments, and then calculate the percentage that each department contributed to the grand total.

5. Choose 'Profit SUM' from the Which data point do you want to base your percentage on? drop down list.

6. Choose 'Subtotal at each change in' from the Calculate as a percentage of drop down list, then select 'Calendar Year' from the pull-down list below.

7. Choose the 'Calculate percentage only for current page Items' radio button from the Which page Items do you want to include? options.

   Discoverer calculates a total percentage for Page Items in your worksheet. In this case, you have only one Page Item, which is Region. Discoverer calculates the total percentage for all regions.

8. Select the Show subtotal and subtotal percentage check box.

9. Clear the Show the percentage of the grand total for each subtotal check box.

10. Click OK to return to close the New Percentage dialog and return to the Percentages tab.

    Figure 4-18 Percentages list

    
    
    Text description of the illustration a16.gif

    The new percentage item Percentage of Annual Profit is displayed in the Percentages list. The check box next to the item is selected by default.

11. Click OK to close the Percentages tab and return to the worksheet.

    Figure 4-19 Tabular Layout worksheet with percentage item

    
    
    Text description of the illustration a17.gif

    Discoverer displays the new percentage on the worksheet as a new column called Percentage of Annual Profit.

    Looking at the new Percentage of Annual Profit item, we can see that the Video Sale department in the East region contributed most to annual profit (i.e. 34%). We can also see that the Video Rental department in the West region contributed least to annual profit (i.e. 7%).

    As you can see, Discoverer does all of the difficult calculation work for you.

    Having calculated totals on the tutorial worksheet, you are ready to create a new worksheet item based on a mathematical calculation.

Notes:

• If you want to remove the Percentage of Annual Profit item from the worksheet, choose Tools | Percentages to display the Edit Worksheet dialog at the Percentages tab, then clear the check box next to the Percentage of Annual Profit item.

Exercise 6: Adding mathematical calculations to worksheets

In this exercise you will learn how to add a mathematical calculation to a worksheet to enable you to create powerful reports.

Discoverer includes a huge range of predefined text handling and mathematical functions that you can use to build custom calculations. You can also use analytical functions to perform complex statistical analysis such as:

• ranking (e.g. creating league tables)

• windowing (e.g. calculating a moving average)

• banding (e.g. to create histograms)

Do you need to find the profit margin for each department? Do you want to calculate the commission earned by your sales people? Do you want to calculate sales tax and show it in the worksheet? Using Discoverer's calculations capability, you can create custom calculations that meet your own unique business needs.

In this exercise, you will create a custom calculation to find the sales tax due for each department's profit.

To create a mathematical calculation:

1. Display the Tabular Layout worksheet, if it is not already displayed.

2. Choose Tools | Calculations to display the Edit Worksheet dialog at the Calculations tab.

   Figure 4-20 Edit Worksheet: Calculation tab

   
   
   Text description of the illustration a18.gif

3. Click New to display the New Calculations dialog.

   Figure 4-21 New Calculation dialog

   
   
   Text description of the illustration a19.gif

4. Type Sales Tax in the What do you want to name this calculation? field.

   You want Discoverer to the calculate sales tax for each department's profit.

5. In the Show list, select Profit SUM then click Paste.

   Notice that 'Video Analysis Information.Profit SUM' moves to the Calculation box.

   Note: Remember that Video Analysis Information is the name of the business area that we are using.

   To calculate a sales tax of eight percent, you will multiply the Profit SUM item by 0.08.

6. Click inside the Calculation box and position the cursor immediately after the text 'Video Analysis Information.Profit SUM'.

7. Type * 0.08 (i.e. the multiplication symbol '*' followed by 0.08).

   Figure 4-22 Calculation box containing the Sales Tax calculation

   
   
   Text description of the illustration a19aa.gif

8. Click OK to save the details and close the New Calculation dialog.

   The new calculation item is displayed in the Calculations list. The check box next to the item is selected by default, which if you remember means that it is turned on (or active).

   Figure 4-23 Edit Worksheet: Calculation tab

   
   
   Text description of the illustration a20.gif

9. Click OK to close the Calculations tab and return to the worksheet.

   Discoverer displays the new calculation on the worksheet as a new column called Sales Tax.

   Figure 4-24 Tabular Layout worksheet with Sales Tax calculation

   
   
   Text description of the illustration a21.gif

   You can now see that the sales tax for the Video Rental department in the Central region is 2012.58.

   You want to reformat the Sales Tax item so that sales tax figures are displayed with the currency symbol.

10. Choose Sheets | Format to display the Format tab.

11. Select Sales Tax from the item list and click Format Data.

12. Display the Number tab, and choose Currency from the Categories list.

13. Click OK to close the Number tab, then click OK again to close the Format tab.

Notice that the sales tax figures on your report have the currency symbol.

As you can imagine, the ability to create custom calculations to enhance your reports is one of the analysis features that make Discoverer so powerful.

Having learned how to create a mathematical calculation, you are now ready to learn how to create a more complex analytic function.

Notes:

• If you want to remove the Sales Tax item from the worksheet, choose Tools | Calculations to display the Edit Worksheet dialog at the Calculations tab, then clear the check box next to the Sales Tax item.

Exercise 7: Adding a ranking calculation to a worksheet

In this exercise you will learn how to add a league table calculation to a worksheet to enable you to create powerful reports.

In this exercise, you want to calculate the league table position of cities, based on profits in the year 2000. To do this, you will create a league table calculation using a ranking function. The ranking function (RANK) is an analytic function, which means that it computes an aggregate value based on a group of rows, returning multiple rows for each group.

To create a ranking calculation:

1. Display the Tabular Layout worksheet, if it is not already displayed.

   Before you create the calculation, you will remove the totals from the worksheet so that they are not included in the league table, then pivot the Department item to the Page Items area. Finally, you will drill down to City data.

2. Choose 2000 from the Year drop down list in the Page Items area.

3. Choose Tools | Totals to display the Edit Worksheet dialog at the Totals tab.

4. Clear the check box next to all totals in the totals list, then click OK to return to the worksheet.

5. Click the drill icon next to the Region column heading and select City from the drill options.

   The City item is added to the worksheet.

   Figure 4-25 Tabular Layout worksheet drilled to City item

   
   
   Text description of the illustration piv2.gif

6. Choose Tools | Calculations to display the Edit Worksheet dialog at the Calculations tab.

7. Click New to display the New Calculation dialog.

   Figure 4-26 New Calculation dialog

   
   
   Text description of the illustration a19a.gif

8. Type Cities league table in the What do you want to name this calculation? field.

9. Click inside the Calculation field and enter the following calculation text:

   RANK() OVER(PARTITION BY "Calendar Year" ORDER BY Profit SUM DESC)

   Figure 4-27 New Calculation dialog

   
   
   Text description of the illustration a19_2.gif

10. Click OK to save the details and close the New Calculation dialog.

    The new calculation item is displayed in the Calculations list. The check box next to the item is selected by default, which if you remember means that it is turned on (or active).

    Figure 4-28 Edit Worksheet: Calculation tab

    
    
    Text description of the illustration a19_3.gif

11. Click OK to close the Calculations tab and return to the worksheet.

    Discoverer displays the new calculation on the worksheet as a new column called 'Cities league table'.

    Figure 4-29 Tabular Layout worksheet with Cities league table calculation

    
    
    Text description of the illustration a19_4.gif

    To make the data easier to analyze, in the final step you sort the worksheet on the 'Cities league table' item.

12. Choose Tools | Sort to display the Edit Worksheet dialog at the Sort tab.

13. Select Region in the sort list, then click Delete.

14. Click Add, then select 'Cities league table' from the drop down list.

15. Click OK to save the details and return to the worksheet.

    Figure 4-30 Tabular Layout worksheet with sorted Cities league table calculation

    
    
    Text description of the illustration a19_5.gif

    Now that data is sorted on 'Cities league table', you can easily see that the highest ranked row is New York, Video Sale department (i.e. $44,269).

Exercise 8: Graphing worksheet data

In this exercise you will learn how to analyze worksheet data visually using graphs. You will create a bar chart to compare profit figures for the Video Sales and Video Rentals departments in 1998, 1999, and 2000.

1. Display the Crosstab Layout worksheet, if it is not already displayed.

   Figure 4-31 Crosstab worksheet

   
   
   Text description of the illustration a22.gif

2. Choose Graph | New Graph to start the Graph Wizard.

   Figure 4-32 Graph Wizard: Graph Type dialog

   
   
   Text description of the illustration a23.gif

3. Select the Bar option from the Graph type list.

4. Select the Bar option from the Graph subtype list.

   Note: Make sure that the 3D Effect check box is selected. 3D is an acronym of three-dimensional.

5. Click Next to display the Graph Wizard: Titles, Totals, and Series page of the Graph Wizard.

   Figure 4-33 Graph Wizard: Titles, Totals, and Series dialog

   
   
   Text description of the illustration a24.gif

6. Type Profits 1998, 1999,and 2000 in the title field.

   Note: Make sure that the Show Title check box is selected.

7. Click Next to display the Graph Wizard: X-Axis page of the Graph Wizard.

   Figure 4-34 Graph Wizard: X-Axis dialog

   
   
   Text description of the illustration a25.gif

8. Type Region in the title field.

   Note: Make sure that the Show X-Axis Title check box is selected.

9. Click Next to display the Graph Wizard: Y-Axis page of the Graph Wizard.

10. Type Profits in the title field.

    Note: Make sure that the Show Y-Axis Title check box is selected.

11. Click Next to display the Graph Wizard: Plot Area page of the Graph Wizard.

    Figure 4-35 Graph Wizard: Plot Area

    
    
    Text description of the illustration a26.gif

    Notice that data columns (known as series) are assigned default colors (e.g. Profit SUM, 1998 is blue). Here, we will change the default color for Profit SUM, 1998.

12. Click the color block next to Profit SUM, 1998 to display the color picker pane, then select a red shade from the color options.

    Figure 4-36 Color picker dialog

    
    
    Text description of the illustration a27.gif

    The column Profit SUM, 1998 is now represented in red.

13. Click Next to display the Graph Wizard: Legend page of the Graph Wizard.

    Figure 4-37 Graph Wizard: Legend page

    
    
    Text description of the illustration a31.gif

14. Choose Top from the Location pull-down list to display the legend at the top of the graph.

    The Sample pane is updated to show you how the graph will look.

15. Click Finish to close the Graph Wizard and return to the worksheet.

    Figure 4-38 Crosstab Worksheet with graph

    
    
    Text description of the illustration a28.gif

Depending on the default position setting, your graph is displayed either as part of the worksheet or in a separate window.

Hint: If your graph is not visible on your worksheet, choose Graph | Display Graph and select Right of Data option.

Notes:

• Now that you have created your graph, you can edit it on screen by clicking elements and dragging them to another location. Or, you can right-click elements to display editing menus. Click to the right or left of the legend to display its blue editing box, click a corner point, and then drag it to re-size the legend. Or, to re-position the legend, drag the whole editing box to a new position.

  Figure 4-39 Editing the graph legend

  
  
  Text description of the illustration a29.gif
• You can also right click graph elements to access editing options, for example to display the Graph Wizard. Right-click over one of the columns to display the editing menu, then select Series Color to display the color picker panel.

  Figure 4-40 Using the right-click menu to edit graph objects

  
  
  Text description of the illustration a30.gif

• If you have time, go back to the Graph Wizard and experiment with different types of graph with different styles of worksheet. You will find that Discoverer provides an extensive range of graphs and charts that can enhance even the most complex report.

Having created your graph, you can now print it or export it as part of your worksheet. For more information, see "Lesson 4: Sharing your data".

Lesson summary

In this lesson you:

• sorted worksheet data

• rearrange worksheet data

• drilled into worksheet data

• added percentages to a worksheet

• add calculations to a worksheet

• created a graph of worksheet data

Now you are ready to find out how easy it is to export your reports in Discoverer, described in "Lesson 4: Sharing your data".