11
Lesson 10: Creating summary folders to optimize performance

Lesson 10: Creating summary folders to optimize performance

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

In this lesson you will use the Summary Wizard to create summary folders:

• using the Automated Summary Management (ASM) facility (creates the summary folders for you)

• manually (using items from the EUL)

Although the New Video Stores business area is now ready for end users, it might be inefficient to allow frequently repeated end user queries to access the detail tables in a production database. It can take a long time to return query results if the detail tables contain very large numbers of rows.

To improve query efficiency, Discoverer Administrator enables you to create summary folders. A summary folder references pre-aggregated data that you know your end users will want to analyze in a worksheet. Discoverer queries can be directed to the pre-aggregated data, avoiding aggregating large volumes of detail data each time a query runs, and therefore optimizing query efficiency.

Note: As the Discoverer manager, you are advised to use Discoverer's ASM facility to create summary folders. For more information about ASM, click Help.

This lesson consists of the following exercises:

• "Exercise 1: Creating a summary folder using Automated Summary Management (ASM)"

• "Exercise 2: Manually creating a summary folder"

Exercise 1: Creating a summary folder using Automated Summary Management (ASM)

In this exercise you will use the Summary Wizard to create summary folders using Automated Summary Management (ASM).

ASM is a Discoverer facility that creates and manages summary folders for you.

You can run ASM in the following ways:

• using the Load Wizard

  When using the Load Wizard: Step 4 dialog you can select the Summaries based on folders that are created check box (for more information, click Help).

• using the Summary Wizard

To create a summary folder using ASM:

1. Choose Insert | Summary to display the Summary Wizard dialog.

Figure 11-1 Summary Wizard dialog

Text description of the illustration sumasm1.gif

2. Select the Have Discoverer recommend and create the best summaries radio button.

3. Click Next to display the Summary Wizard: Step 2 dialog.

4. Click Start for ASM to analyze folders in the End User Layer (EUL).

   ASM starts by analyzing folders in the current EUL so that it can propose a suitable set of summary folders to create.

   Discoverer displays a progress bar indicating the current step and the number of folders analyzed.

Figure 11-2 Summary Wizard: Step 2 dialog

Text description of the illustration sumasm2.gif

When folder analysis is complete, Discoverer displays the message Analyzing completed. Press Next to continue.

5. Click Next to display the Summary Wizard: Step 3 dialog.

Figure 11-3 Summary Wizard: Step 3 dialog

Text description of the illustration sumasm3.gif

The above dialog displays a graph that indicates:

• the query performance gain that will be achieved by creating the suggested summary folders

• the database space that the new summary folders will use.

This dialog enables you to set the size of table space that you want ASM to fill with summary folders. Both the graph pointer and the Create summaries to fill field enable you to alter the size of the table space that Discoverer will use.

Note: Do not reduce the values to either the Table space to use for summaries field or the Create summaries to fill field.

Note: The estimated performance gain displayed above is calculated partly from query statistics gathered from end user queries and partly from data about the database tables. The performance gain can be very large in normal use because Discoverer collects statistics from end user queries that have previously been executed. However, Discoverer might not yet have collected any end user query statistics.

6. Click Recommended Summaries to display the Recommended Summaries dialog

   The Recommended Summaries dialog displays the summary folders suggested by ASM.

7. Click one of the recommended summary folders to display the source folders used to create the currently selected summary folder.

Figure 11-4 Recommended Summaries dialog

Text description of the illustration sumasmr.gif

8. Click Cancel to display the Summary Wizard: Step 3 dialog again.

9. Click Finish for ASM to create summary folders automatically.

   Discoverer displays a progress dialog while it creates the summary folders.

   Discoverer then displays the Automated Summary Creation dialog indicating that summary folder creation is complete.

10. Click OK.

11. Display the Workarea: Summaries tab to view the summary folders that ASM has just created.

Figure 11-5 Workarea: Summaries tab

Text description of the illustration sumasmc.gif

ASM has created summary folders that will improve end user query performance. To make sure that end users have the most appropriate summary folders, you must run ASM regularly.

For more information about ASM, click Help.

In this exercise you used the Summary Wizard to create summary folders using Automated Summary Management (ASM).

Exercise 2: Manually creating a summary folder

In this exercise you will use the Summary Wizard to create summary folders manually by specifying summary folders that are based on items that exist in the EUL.

To manually create a summary folder:

1. Choose Insert | Summary to display the Summary Wizard dialog.

Figure 11-6 Summary Wizard dialog

Text description of the illustration sumwz1.gif

2. Click the I want to specify the summaries myself radio button.

3. Click Next to display the Summary Wizard: Step 2 dialog.

4. Select the From items in the End User Layer radio button.

Figure 11-7 Summary Wizard: Step 2 dialog

Text description of the illustration sumwz2.gif

5. Click Next to display the Summary Wizard: Step 3 dialog.

6. Choose the New Video Stores business area from the drop down list.

7. Move the following items in the Video Analysis folder from the Available items list to the Selected Items list:
   • Region

   • Department

   • Transaction Date

   • Year

   • Month

8. Move the SUM and COUNT functions, for each of the following data point (numeric) items, in the Video Analysis folder from the Available items list to the Selected items list:
   • Sales

   • Cost

   • Profit

9. Before going any further, make sure that the Summary Wizard: Step 3 dialog appears like the figure below.

Figure 11-8 Summary Wizard: Step 3 dialog

Text description of the illustration sumwz3.gif

10. Click Next to display the Summary Wizard: Step 4 dialog.

Your next task is to choose the summary combinations that you want Discoverer to build and manage.

Note: Summary combination are individual sets of axis and measure items that together make up a summary folder. If an end user runs a query with the same items as those specified in a summary combination, the query will run against pre-aggregated summary data instead of the detail data in the database. Consequently, the query performance is improved because it uses the pre-aggregated data instead of scanning all the detail rows.

In the Summary Wizard: Step 4 dialog, each numbered column represents a summary combination.

11. Click Add Combination to create a new combination column.

12. Select the following items in the new column (marked 1):
    • Region

    • Department

    • Transaction Date

13. Clear the check boxes next to Year and Month in column 1.

14. Before going any further, make sure that the Summary Wizard: Step 4 dialog appears like the figure below.

Figure 11-9 Summary Wizard: Step 4 dialog

Text description of the illustration sumwz4.gif

15. Click Next to display Summary Wizard: Step 5 dialog.

Your next task is to schedule the initial summary folder refresh and periodic refresh intervals then name the summary folder and schedule its build.

Note: The refresh schedule establishes the time frame in which Discoverer automatically updates the data in the summary folder. It is important for you, as the Discoverer manager, to set up a periodic refresh that ensures that the aggregated summary data is current at the periods required by end users.

Figure 11-10 Summary Wizard: Step 5 dialog

Text description of the illustration sumwz5.gif

16. Clear the Automatically refresh this Summary Folder, starting on: check box.

17. Click Next to display the final page of the wizard, the Summary Wizard: Step 6 dialog.

Figure 11-11 Summary Wizard: Step 6 dialog

Text description of the illustration sumwz6.gif

18. Type Video Analysis by Region and Department in the Name field.

19. Leave the Description field empty.

20. Select the Build immediately radio button.

21. Click Finish for Discoverer to create the new summary folder.

    Discoverer displays a progress bar while creating the summary.

22. Display the Workarea: Summaries tab and click the + symbol next to the New Video Stores business area to view the new summary folder.

23. Right click the summary folder that you just created.

24. Choose Properties from the popup menu.

25. Make sure that the Available for queries property is set to Yes.

26. Click OK.

You have now created a summary folder manually using Discoverer's Summary Wizard. End user queries that match any of the summary combinations can use this summary folder and benefit from the performance gain that it offers.

In this exercise you used the Summary Wizard to create a summary folder manually by specifying that the summary folder is based on items that exist in the EUL.

Lesson summary

In this lesson you used the Summary Wizard to create summary folders:

• using the Automated Summary Management (ASM) facility (creates the summary folders for you)

• manually (using items from the EUL)

In the next chapter you can remove remove all of the work that you completed as part of this tutorial.