OTN's Servlet-based Surveys Part 3- Analyzing Survey Results August 1999

Surveys are deployed and people respond to them. Over time, data accumulates in the survey response tables. How do you turn that raw data into business information? Here are some approaches:

Approach	Example	Solution
Enterprise reports. Corporate-standard, fixed-format reports are designed and deployed centrally, then run again and again throughout the enterprise.	Monthly sales by region.	Oracle Reports
Ad hoc queries. Users create flexible reports to extract data that's important to them at the time, while avoiding complex database structures and procedures.	Customer attitudes towards specific products.	Oracle Discoverer
Online analytical processing (OLAP). Integrate data from disparate sources into a coherent whole�a data mart or data warehouse. Specialists build multidimensional models that reveal the impact of alternatives. End-users plug specific values into the models to answer what-if questions.	What will happen to sales in Asia if we raise prices relative to our competition next quarter?	Oracle Express

OTN's Approach

1. Define a goal, then gather data. To learn what members think about various Oracle products, OTN placed surveys in areas of the web site that presented white papers and other information about those products. The survey response tables store the data
2. Relate survey results to membership data. People provide information about themselves when they become OTN members, and that information is stored in tables. OTN created a schema linking these tables to the survey response tables.
3. Analyze results using Oracle Discoverer to answer questions like these: How many people have responded to a particular question? Have response trends changed over time? How do responses vary by user attributes (such as company, location, or job title)?

Discoverer's Components

• User Edition is designed for business professionals who do not have a computer programming or database background. The User Edition is an easy-to-use read-only data access tool.
• End User Layer shields end users from the complexity of the database. Because the End User Layer only deals with the metadata in the metalayer, a Discoverer user cannot affect the database.
• Administration Edition builds and maintains the End User Layer, and provides tools for designing and presenting subsets of data called business areas that end users access from the User Edition.

1. Before you start Discoverer, use SQL*Net or Oracle Net8 Easy Config to create a service (database alias) named Survey with user ID/password of survey/survey.
2. If an End User Layer (EUL) does not exist, use the Administration Edition to create one.
3. In the Administration Edition, use the Load Wizard to define metadata for a business area.
4. Use the User Edition to create a workbook. A workbook contains one or more pages called worksheets that display data according to your criteria.
5. Use the User Edition to analyze the data in the workbook.

Creating an End User Layer

1. If no End User Layer exists when you start Discoverer Administration Edition, the EUL Manager dialog box opens.
2. Click Create an EUL.
3. In the Create EUL panel, accept the default values and click Finish to close the dialog box. Discoverer creates and populates tables and views, then displays a dialog box.
4. Click Connect as EUL owner to close the dialog box and connect to the database. Discoverer opens the Load Wizard.

Using the Load Wizard

1. Discoverer opens the Load Wizard when you start the Administration Edition.
2. Click Create a new business area. Load Wizard: Step 1 adds the question, "Where do you want to load the metadata from?"
3. The next step is to identify the metadata, or source tables, that contain the information for the business area. Click the radio button to load metadata from the on-line dictionary. This option loads tables and views from the standard Oracle dictionary.
4. Click Next to display the panel for Step 2.
5. From the Select a Database Link list, choose <Default Database>.
6. From the list of users for the selected database, click SURVEY, then click Next to display the panel for Step 3.
7. From the list of available schema objects, choose SURVEY, then click the right-arrow to move it to the list of selected schema objects. Click Next to display the Step 4 panel.
8. In the Step 4 panel, click Next to accept the default values and display the Step 5 panel.
9. In the Step 5 panel's Name field, type SurveyBusinessArea. In the Description field, type Analyze survey results.
10. Click Finish to close the Load Wizard. Discoverer generates files, tables, and data for the new business area.

Creating a Workbook

Step 1

1. When you start the User Edition, Discoverer displays a dialog box. In the Username field, type survey. In the Password field, type survey. In the Database field, type Survey. (This step assumes a database alias named Survey exists for the survey tables.)
2. Click Connect. Discoverer connects to the dtaabase, then displays the first screen of the Workbook Wizard.
3. Click Create new Workbook. The Wizard adds the question, "How do you want to display the results?"
4. Click Page-Detail Crosstab, then click Next to display Step 2.

1. From the drop-down list, choose SurveyBusinessArea. (This step assumes the business area was created using the Administration Edition). Discoverer updates the list of available items.
2. Click the plus sign to the left of the item Response Details to expand it, then click the plus sign to the left of the item Qsn Id to expand it, then choose Detail. Click the right-arrow to move Response Details-Qsn Id-Detail to the list of selected items.


3. Follow the same procedure to add Response Details-Choice Id-Detail to the list of selected items
4. Follow the same procedure to choose Response Master-Sur Id, then click the right-arrow. Discoverer opens the Join Folders dialog box
5. Click Response Master -> Response Details to choose this join (which Discoverer generated when the EUL was created), then click OK to close the dialog box.
6. Discoverer adds Response Master-Sur Id the Selected list. Click Next.


7. A dialog box displays the message, "No data can be displayed in the crosstab because there are no rows. To add a row, drag an item from the column area." Click OK to close the dialog box and continue with Step 3.

1. Drag Qsn Id from the data point area to the vertical axis item area. A dialog box displays a message to confirm your action. Click Yes to close it.
2. Drag Sur Id to the Page Items area.
3. Drag Choice ID to the horizontal axis item area to replace Data Points: Choice Id. A dialog box displays a message to confirm your action. Click Yes to close it.


4. Click Next to display Step 4.

1. From the View Conditions For list, choose Sur Id, then click New to open the New Condition dialog box.
2. Define a condition to select a specific survey. From the Item list, choose Sur Id; from the Condition list, choose =; and in the Value(s) field, type the ID number of the survey you want to select (example: 22).
3. Click OK to close the dialog box.
4. Click Next to display Step 5.

1. From the View Calculations For list, choose Choice Id, then click New to open the Edit Calculation dialog box.
2. In the Name field, type ChoiceCount.
3. In the Calculation field, type COUNT(Response Details.Choice Id).
4. Click OK to close the dialog box.
5. Click Finish to execute the query and display the results in the new workbook.


6. Choose File -> Save to save the workbook. Name it SurveyWB.

Analyzing Survey Results

Opening the Survey Workbook

1. When you start the User Edition, a dialog box to connect to Oracle Discoverer appears. In the Username field, type survey. In the Password field, type survey. In the Database field, type Survey. (This step assumes a database alias named Survey exists for the survey tables.)
2. Click Connect. The first screen of the Workbook Wizard appears.
3. Click Open an Existing Workbook.
4. Click Recently Used List, then choose SurveyWB
5. A query causes Discoverer to find the most recent data to fill in the worksheet. Discoverer evaluates the query to determine how much time it will take to open the workbook and shows you an estimate. Click Yes to see the data. A dialog box shows the progress and elapsed time while Discoverer finds the data. At the end of the process, the workbook appears.

Graphing Survey Results

1. With your workbook of survey data open, choose Graph -> New Graph to display the Graph Wizard.
2. In the Step 1 panel, choose Horizontal Bar for the graph type, then click Next.
3. In the Step 2 panel, choose 3D for the format, then click Next.
4. In the Step 3 panel, choose Show Legend, then click Finish. Discoverer displays the graph. The following figure shows the results for question 41 of a survey: one person chose response 7, two people chose response 8 and one person chose response 9.

Installing the Survey Application

Using the Survey Application

Analyzing Survey Results

How it Works

Questions or comments? Post a message in OTN's Sample Code discussion forum or send email to the author.

Case Study: OTN's Servlet-based Surveys
Author: Robert Hall, Oracle Corporation
Date: August 1999

This document is provided for information purposes only and the information herein is subject to change without notice. Please report any errors herein to Oracle Corporation. Oracle Corporation does not provide any warranties covering and specifically disclaims any liability in connection with this document.
Oracle is a registered trademark and Enabling the Information Age is a trademark or registered trademark of Oracle Corporation. All other company and product names mentioned are used for identification purposes only and may be trademarks of their respective owners.

Oracle Corporation
World Headquarters
500 Oracle Parkway
Redwood Shores, CA 94065
USA

Worldwide Inquiries:
+1.650.506.7200
Copyright © Oracle Corporation 1999, 2000
All Rights Reserved