Oracle Discoverer HandsOn Training

1
Oracle Discoverer Hands-On Training

• Created by College Information Systems from the
  Oracle Discoverer Tutorial
• Presented by Tara Welby
• Thursday, July 29, 2004

2
Agenda

• Getting started
• Getting the data you want
• Analyzing your data
• Sharing your results
• Review
• Next Steps

This symbol indicates a hands-on exercise
This is a hands-on demonstration of Discoverer.
The Start
3
Discoverer End User Layer (EUL)

• A layer of information that Oracle Discoverer
  uses to hide the complexities and details of the
  underlying database. The End User Layer makes it
  easier and faster to create queries because it
  organizes the data to reflect particular business
  areas. The same data can also be used for more
  than one business situation. The End User Layer
  is defined using Discoverer Administrator.
• Oracle Corporation

4
The Discoverer Work Area

• Business Area
• Logical group of tables/views
• Workbook
• Similar to Excel workbook
• 3-ring binder
• Name appears at top in brackets
• Worksheet
• Pages of a workbook
• Names appears on tabs at bottom
• Layouts
• Tabular
• Crosstab - Shows the data in a more aggregate
  form that is usually better for data analysis
  than tables.

Getting Started
5
The Discoverer Work Area (cont.)

• Query
• Used to obtain data from the database
• Each worksheet has the results of a query
• SQL is written by Discoverer (or by user)
• Items
• Identified by row and column headings
• Similar to a cell in Excel
• Page items
• Similar to Excel cross tab drop down
• Identified by column heading above others

Getting Started
6
The Workbook Window

• Tool Bar
• Analysis Bar
• Formatting Bar
• Page Axis
• Top Axis
• Left Axis
• Data Points
• Worksheet Tabs
• Tab Scroll Buttons
• Page Scroll Bar (Horizontal)
• Page Scroll Bar (Vertical)

Getting Started
7
Connect to the Database

• Username
• Password

Getting Started
8
Workbook Storage

• My Computer
• Stored to local drive
• Database
• For storing and sharing reasons, BSC will save
  workbooks to the database
• Scheduling Manager
• Discoverer allows for workbook scheduling. This
  feature is not currently available at BSC.
  Future availability TBD.
• Recently Used List
• Lists workbooks previously opened

Getting Started
9
Open an Existing Workbook

• Workbook Wizard appears after connect
• Click Open an Existing Workbook
• Screen will expand
• Click Database

Getting Started
10
Select Workbook to Open

• Click Video Tutorial Workbook
• Click Open
• Choose to Run Query

Getting Started
11
To Run or Not To Run?

• A query causes Discoverer to find the most recent
  data to fill in the worksheet.
• Normally click Yes
• Click No if dont want to see the data in the
  worksheet. Example to create a new worksheet and
  dont need to see data on the existing.
• Discoverer evaluates the query to determine how
  much time it will take to open the workbook and
  shows an estimate
• This estimate is not always accurate, and may be
  extreme
• Click Yes to see the data

Getting Started
12
Four Types of Display

• Table
• Page-Detail Table
• Crosstab
• Page-Detail Crosstab
• Page axis
• Top axis
• Side axis

Getting Started
13
Switch Between Tabular and Crosstab
Getting Started
14
Select Cells

• Tabular - click row number or column heading
• Crosstab - click marker
• Click upper left cell to select all cells

Getting Started
15
Select Axis Items

• To select all items on an axis, click the axis
  item marker
• An arrow will appear indicating the axis has been
  selected.
• Markers will not display on printouts

Getting Started
16
View Help

• There are 2 types of help
• Help Topics / General Help
• Context Sensitive Help
• View Help Topics
• Menu Help Help Topics
• View Context Sensitive Help
• Menu Sheet Edit Sheet
• Click the Help button

Getting Started
17
Questions

• What are the 4 types of display?
• Where will BSC save workbooks?

Getting Started
18
Add/Modify Title

• Edit title
• Menu Sheet Edit Title
• Add text variables
• Click in title where you want to add the variable
• Click the insert button
• Will show with

Getting Data
19
Format

• Edit column headings and page items
• Format
• Select item, Menu Format Headings or Menu
  Format Data
• Font
• Alignment
• Background color
• Add bars to graphically note numeric sizes
  (checkbox on number tab)
• Text
• Select item, Menu Item Properties
• You can also set defaults for future workbooks.

Getting Data
20
Set Default Formats

• Menu Tools Options
• Click Formats Tab
• Change Data Format
• Click OK to keep changes
• The changes will show on the next new workbook
  you create
• (To change for current workbook, right click on
  the object and choose Format Data or Format
  Heading as appropriate)

Getting Data
21
Create a New Workbook

• Menu File New
• The workbook wizard appears
• Click Table
• Click Next
• Select Items from the Database

Getting Data
22
Select Items

• Click on
• Video Analysis Information
• Click Department gt
• Click on Profit
• Profit SUM gt
• Ctrl-Click to select Region and Calendar Year gt
• Click Next

Getting Data
23
Rearrange Columns

• Department is the page item
• Click Calendar Year and drag it to the left of
  Profit Sum
• The black bar will show the position

Getting Data
24
Set Conditions

• Click Next
• These are the conditions that have already been
  defined, and/or have been made available by the
  Discoverer Administrator.
• View Conditions For - display all the conditions
  in a workbook, or only those that apply to
  particular data items.
• Check Department is Video Rental or Video Sale
• Click Finish

Getting Data
25
Save Workbook

• Menu File Save As
• Click Database
• Click Save
• Name YourNameLesson1Wk1
• Click Save
• Where will you see the name of your workbook?
• To Delete Menu Manage Workbooks Delete
• Note if saved to your machine you must also
  delete via Windows Explorer

Getting Data
26
Rearrange Data

• Menu Sheet Edit Sheet
• Click Table Layout tab
• You can arrange, but not change, data on this tab
• Add Region to the Page Items (Drag next to
  Department)
• Note Show Page Items is checked

Getting Data
27
Rearrange Data (cont.)

• Drag Department down between Calendar Year and
  Profit Sum
• Click OK

Getting Data
28
Change from Table to Crosstab

• Menu Sheet Duplicate as Crosstab
• Warning!
• Click OK
• Drag Region from Page Items to Side

Getting Data
29
Change from Table to Crosstab (cont.)

• Drag Department to Page Items
• Click OK
• Double-click Sheet 2 to rename your worksheet
  to CrossTab1
• Click on Sheet 1

Getting Data
30
Filter Data with Conditions

• Menu Tools Conditions
• Click New
• Generate name automatically should be already
  checked
• Enter Description
• Select Profit SUM from Item drop down
• Select gt from Condition drop down
• Enter 50000 in Value
• Click OK (twice)

Getting Data
31
Filter Data with Conditions (cont.)

• Only records with a Profit SUM gt 50000 will now
  show
• Turn the condition off Menu Tools-Conditions
• Uncheck Profit SUM gt 50000
• Click OK

Getting Data
32
Conditions Advanced Topics - Subqueries

• Requires an intermediate step to determine (Often
  creates separate intermediate worksheet)
• Subquery identifies the intermediate worksheet as
  the value for the condition
• Menu Tools Condition, Values dropdown, select
  Create Subquery
• Example Get all employees who are also student.
  Main query/worksheet - all employees w/
  condition. Intermediate worksheet - all
  students. Main condition will note intermediate
  worksheet.

Getting Data
33
Conditions Advanced Topics Correlated Items

• Find all the sales profits that exceed the median
  profit amount by department.
• The by department portion of the value is the
  new dimension to the subquery.
• Usually appear on both the original worksheet,
  and the intermediate value worksheet .
• Example
• original worksheet - profit data for each
  department
• intermediate worksheet - median value for each
  department
• Correlating the two items matches them so each
  department median value corresponds to each
  department profit value.

Getting Data
34
Create Exceptions

• Two Steps
• Define Exception
• Ex Profit Sum lt 50000
• Define the format
• Ex Arial Red

Getting Data
35
Create Parameters

• Unlike regular conditions that find the same data
  each time they are applied, parameters offer
  choices at the time the data loads.
• Two levels
• Workbook level - Applies to all worksheets in the
  workbook.
• Worksheet level - Applies to the current
  worksheet only.
• Menu Tools Parameters
• Click New
• You will see the screen on the bottom right

Getting Data
36
Create Parameters (cont.)

• Pick Video Analysis Information Calendar Year
  from For Item drop down
• Enter Name
• Enter Prompt
• Enter Description
• Check Let user enter multiple values
• Dept X OR Dept Y
• Enter Default Value 2000
• Click Allow only one value for all sheets
• Value cascades through all sheets in book

Getting Data
37
Create Parameters (cont.)

• Click OK
• Choose a Year will now show on the parameters
  screen and will be checked
• Click OK
• You will be prompted to Choose a Year
• Accept 2000 and Click Finish

Getting Data
38
Create Parameters (cont.)

• The worksheet will appear as top right
• Create Condition was checked - toggle this off
  and on using the Conditions dialog
• Menu Tools Conditions
• To change parameter values
• Menu Sheet - Edit Parameter Values
• Menu Sheet - Refresh Sheet

Getting Data
39
Add Items

• Click Sheet 1 to be sure to return to tabular
  view
• Menu Sheet Edit Sheet
• Click Select Items tab
• Under Available, Click
• Video Analysis Information
• Click Sales
• Sum gt
• Click Table Layout and order Region (Page Item),
  Calendar Year, Department, Profit SUM, Sales Sum

Getting Data
40
Add Items (cont.)

• Click OK
• The worksheet will appear similar to the one at
  the right

Getting Data
41
Use SQL

• Menu View SQL Inspector
• Copy
• Export
• Menu File Import SQL

Getting Data
42
Questions

• Do your default formats affect all of your
  existing workbooks?
• What is the difference between a condition and a
  parameter?
• How do you turn conditions on and off?

Getting Data
43
Sort

• Tools ascending, descending, group
• Group sort
• Only on tables (not on crosstabs)
• precede columns without group sorting
• Cant move a column without group sorting above a
  column with
• Page Break
• New page at the start of each group (name at the
  top of the page)
• Line
• thickness of line separating groups
• Spaces
• number of cell spaces between groups

• Sorted on city w/in region

Analyzing Data
44
Sort (cont.)

• Close your workbook
• Menu File- Open Video
• Click Tabular Layout
• Choose 2000 from the list of years
• Menu Tools Sort
• Select Region and click Delete
• Click Add and select Profit SUM
• Click Direction Hi to Low
• Click OK

Analyzing Data
45
Pivot Rows and Columns

• Pivoting data
• move the data from one axis to another to arrange
  it for efficient analysis
• Menu Sheet- Edit Sheet
• Click Table layout
• Drag Calendar Year down to the left
• Drag Region to Page Items
• Click OK

Analyzing Data
46
Drill

• Drilling into data
• shows more details about the data
• Drilling out of data (collapsing)
• consolidates the data for a broader overview
• Requires predefined hierarchy in EUL
• Click drill icon for year
• Check Calendar Quarter
• A new column will appear
• To collapse, click the drill next to Quarter and
  change back to Calendar Year

Analyzing Data
47
Add Totals

• Sum rows and columns of numbers, find averages
  and standard deviation, compute subtotals and
  Grand Totals, etc.
• Automatically places the summations at the
  appropriate positions
• Click the crosstab layout tab
• Menu Tools - Totals

Analyzing Data
48
Add Totals (cont.)

• Click New
• From the Calculate drop downs choose Sum of
  Profit Sum
• Check Grand Total at Bottom
• Select Sum from Label drop down
• Click OK (twice)

Analyzing Data
49
Add Totals (cont.)

• You will see the sum at the bottom like the one
  at the right
• Click Tabular Layout

Analyzing Data
50
Add Percentages

• Menu Tools Percentages
• Click New
• Choose Profit SUM in the Calculate percentages
  for drop down
• Set column heading to Percentage of Annual
  Profit
• Click Display subtotal and subtotal percentage

Analyzing Data
51
Add Percentages (cont.)

• Click OK, Percentage of Annual Profit will be
  checked
• Click OK your sheet will appear as the one in
  the bottom right.

Analyzing Data
52
Add Calculations

• Menu Tools- Calculations
• Click New
• In name enter Sales Tax
• In the show list box, click Profit SUM, then
  paste
• Click on the multiplication button (x) and in
  the calculation box type .08
• Click OK

Analyzing Data
53
Add Calculations (cont.)

• Sales Tax will be checked
• Click OK, your worksheet will appear as the
  bottom right
• To format number
• Click column
• Menu Format Data
• Click Number tab
• Select Currency

Analyzing Data
54
Add a Graph

• Object placement limited
• Cant rotate label text, cant move legends
• Options
• resize, change fonts, toggle display
• Menu Graph New Graph
• Click on Bar, Next
• Click on 3D, Next

Analyzing Data
55
Add a Graph (cont.)

• Enter Top, Left (Y Axis) and Bottom (X Axis)
  titles
• Check Show Legend
• Click Next
• Set options as desired
• Click Finish

Analyzing Data
56
Add a Graph (cont.)

• Moving the mouse over the bars will display a
  description at the bottom left
• Double clicking on the bars will allow you to
  drill
• Clicking on tools and dragging allows to pour
  color or pattern onto bars
• Dragging mouse across will display reference lines

Analyzing Data
57
Modify a Graph

• Resize graph
• Resize columns
• Resize window
• Click snapshot tool to copy to clipboard
• Click printer tool to print graph

Analyzing Data
58
Questions

• What happens if you remove the checkmark next to
  a total, percentage or calculation?
• For which layout can you do a group sort?

Analyzing Data
59
What are the ways to share data?

• Printing
• Exporting
• Granting access

Sharing Data
60
Print

• Menu File Page Setup
• Click Header and Footer tabs to Set Headers and
  Footers
• Similar to Title, can insert additional
  information (noted by )
• Choose to print sheet, workbook or graph

Sharing Data
61
Grant Access to Workbook Export

• Export
• File Export
• Excel
• Oracle Reports
• Allow access to workbook
• File Manage Workbook Sharing
• Click User gt
• Click OK

Sharing Data
62
Put it Together View Analytic Workbook

• Tutorial includes sample workbook with various
  analytical sheets
• Menu File Open Vidaf4 - Analytic Function
  Examples
• Rank of sales
• Top/Bottom N
• Sales are of product category
• Sales this year
• Sale this/last quarter
• 3 month average sales

Review
63
Put it Together Look at BSC data

• View Financial Aid workbooks
• Create new workbooks
• Choose business areas
• Schema/Owner/User
• SATURN
• POSCNTL
• PAYROLL
• Etc.
• Subject Areas Object Access
• Student Object Access
• HR Object Access
• Etc.

Review
64
Put it Together Look at BSC data F/A Tracking
Report
Review
65
Put it Together Look at BSC data F/A Tracking
Report - Selected
Review
66
Put it Together Look at BSC data F/A Award
Report
Review
67
Put it Together - Create a New Workbook

• Items
• Region, Store Name, Calendar Year, Costs SUM,
  Sales SUM
• Make Region a Page Item
• Rearrange to order
• Calendar Year, Store Name, Sales SUM, Costs SUM
• Create a condition
• Only see regions w/ annual sales gt 40,000
• Sort the worksheet by Store Name ascending
• Create a calculation to find Profit from each
  store
• Calculate Sales Tax
• Create a cross tab version
• Pivot Store Name to top and Calendar Year to left
• Drill to Quarter
• Create a graph

Review
68
Next Steps

• Power Users can utilize Discoverer 9.0.2 Desktop
• Power Users provide IT with sample joins,
  hierarchies, queries
• IT upgrade to Discoverer Web (9.0.3) as part of
  the ODS installation
• Waiting for hardware and infrastructure
• See http//it.bridgew.edu/Banner , Click
  Reporting for
• This document
• Users Guides
• Project information

The End