Transcript Introduction to OracleBI Discoverer Powerpoint

Introduction to OracleBI
Discoverer
1
Beginning Note:
• Just as any windows based application
there are always many different ways to
accomplish the same tasks. (Copy, Paste,
Save, etc…)
Discoverer is no different. This will point
out several ways to do the different tasks
but it would be too time consuming to
cover each and every one.
2
With that being said…..
This will attempt to give you, the “end-user”,
an idea of some of the features and
functionality of the OracleBI Discoverer
product.
You will see enough of the basic things to
enable you to start using the product after
viewing this introduction.
3
What we are going to do….
• Discoverer – What is it? Why use it…
• Go over Concepts & Key Terminology of
the Discoverer product.
• Build several queries utilizing some
different features of Discoverer.
• Go over additional features of Discoverer.
4
Why use Discoverer
• To do ad-hoc queries in lieu of running or
looking through reports:
– What was the score of an establishment the
last time it was inspected?
– How many active food service
establishments?
– How many push cart permits issued last
quarter?
5
Key Terminology
• EUL (End-User Layer)
– Interface between the “end-user” and the
database
– Contains business areas, folders, items, etc…
• Business Area
– Set of related information with a common
purpose (Environmental)
6
Key Terminology (cont.)
• Folders
– Store details about groups of related
information (Establishments, Inspections, etc.)
– Usually relates to a database table or view
• Items
– Folders contain items (for example the
“Inspections” folder might contain State ID,
Premise name, Score, etc.)
– Usually relates to a column in a database
table
7
Key Terminology (cont.)
• Workbook
– Can contain one or more worksheets
– Somewhat similar to the Excel workbook
– Stored on the database
• Worksheet
– Contains 1 SQL statement (query)
– Shows the results of the query
8
ORACLE DISCOVERER OVERVIEW
END USERS
(Workbooks & Worksheets)
Administrator
End User Layer (EUL)
(Business areas,Folders,Items etc…)
CDP-DataMart
(Oracle 9i database)
9
• The queries we will build will be based
on the IHS Environmental business
area.
• It is important to understand the
CONCEPTS of how we are using
Discoverer as opposed to the system or
business area we are using.
10
QUERY #1
• We will:
–
–
–
–
–
–
–
–
–
–
–
Connect & Sign on to Discoverer 10gPlus
Create A New Workbook
Build A Simple “Table” Query
Apply Some “User Defined” Conditions
Run The Query
Format Column Data And Headings
Add A Sort
Edit “User Defined” Conditions
Re-run The Query
Rename The Worksheet
Close And Save The Workbook
11
Connecting to Discoverer
10gPlus
• Start Internet Explorer.
• Type in the following URL:
– http://discoverer.cdpehs.com/
• After typing in the Discoverer URL you
should be brought to the Login page.
12
13
Enter User Name
Enter Password
Enter Database
Enter End User Layer… Then click “Go”
14
You may get a “Security Information” pop-up. Click “Yes” to display the
items.
15
The Workbook Wizard
• A series of 5 steps that assists/prompts
you in creating an end result.
• Once you become familiar with building
queries you do not have to go thru all 5
steps.
• You can always get to any of the wizard at
a later time to “edit” the query.
16
Create/Open Workbook
• Workbook Step #1 allows you to :
• Open an existing workbook
• Or
• Create an new workbook
17
Click on “Create a new workbook”.
We will open an existing workbook later in this session.
18
Uncheck “Title”, “Graph”, and “Text Area”. Leave the
default “Table” selected and click “Next”.
19
The Selection Process
• Workbook Step #2 allows you to:
• Select “Business Area”, “Folders” from the
business area, and “Items” from the
folders.
• Create conditions, calculations,
percentages, and totals, edit &
enable/disable.
20
Business Area
• Select the business area that contains the
information (tables) that you want to use
(Environmental).
• You will only see the business areas you
are authorized to see.
• You will only see the data that belongs to
you.
21
22
Select the Folder(s)
• Select the folder(s) that contain the items
that you want to use in your worksheet
(query).
• Clicking the (+) sign to the left of the folder
will expand the folder to show all items
contained within the folder.
23
Select the “Establishments” Folder.
Click the (+) sign to the left of “Establishments” to see the “Items”.
24
Selecting Items
•
•
•
•
•
Scroll down the list of items.
To Select an item:
Click on the item (word) to highlight it.
Click the (>) sign in between the 2 panes.
You will see the selected item displayed in
the right pane.
25
Let’s select the
•
•
•
•
Est Number
Premise Name
Establishment Type
Primary Phone
26
Click on the item – then click the (>) arrow.
27
Until your screen looks like this…
28
To de-select an item
• Click on the item in the right pane to
highlight it.
• Click the (<) sign in between the 2 panes.
• The item will no longer be displayed in the
right pane.
29
Once you have selected the items – Click the “Conditions” tab.
30
Click “New” and “New Condition” from the drop-down menu.
31
Naming the condition
32
Conditions (tip)
• It is a good idea to assign a meaningful
name to the condition if:
• You are going to have multiple worksheets
in a workbook.
• You are going to save and reuse this
workbook.
• You are going to share the workbook with
other users.
33
We will allow to Generate name automatically. Click the “arrow” under
Item to select the item. You will see that the 4 items we selected under
on the “Items” tab.
34
If you select “More Items” you will see ALL the
items that are in the folder(s) you are working with
(Establishments)… click “Cancel”.
35
The list of items….
• You do not have to select the item in the
query in order to use that item in a
condition
• But
• You have to at least select one item from
the folder that contains it.
36
Scroll down to and click “Establishment Type”.
37
Notice that the item “Establishment Type” now has
the folder’s name in front of it.
38
Arrow down the Condition column and select the
conditional expression “=“.
39
Manually enter 48 in the “Values” column to select only
Bars – Click “OK”.
40
Condition Tab
41
Conditions (tip)
• Text values and dates are enclosed in
single quotes.
• Multiple values are separated by a
comma.
• Date format is “dd-mon-ccyy”
– (ie; ’28-FEB-2008’)
42
The NEW tab offers options for calculations, percentages, and totals.
We see those later.
43
Click Next
44
Table Layout
• Workbook Step #3 allows you to rearrange the order the items appear on the
worksheet.
• To re-arrange the items:
• Click on the column name and drag the
column to the left or right until it is in the
desired order.
45
Click and drag the column names to the desired locations… then click
“Next”.
46
Workbook Step 4 allows you to sort the worksheet by the data points.
We will sort the worksheet later in the session… click “Next”.
47
Workbook Step #5 allows you to add a parameter to the worksheet.
This can supply values to a conditional statement. We will add a
parameter to the worksheet later in the session. Since this is the last
step of the Wizard… let’s run the query. Click “Finish”.
48
You should see the “query progress” bar informing you of
the status of the query.
49
And you should see something like this…….
50
If you scroll to the bottom of the report, you will notice “click
here to retrieve the next 250 rows”.
51
Let’s format the columns
52
Right click on the column of data you want to edit.
53
Format Data – allows you to edit
the data in that column.
54
Format Heading – allows you to
edit the column heading.
55
Edit Heading – allows you to
change the heading over the
column.
56
Worksheet Properties – allows you to
change the view of the worksheet.
57
Let’s add a sort sequence. Click “Tools” then click “Sort”.
58
Click the “Add” button to the right then select “Premise
Name” from the drop down Column. Click “OK”.
59
The Premise Name (establishment name) should be in
alphabetical sequence (numbers and special characters
will appear first).
60
Let’s disable the Establishment Type condition. Click on
“Tools” then click on “Conditions”.
61
To disable the Establishment Type condition, click on the
“Establishment Type= 48” condition in the “Selected”
window and click the (<) arrow.
62
This will move the condition back to the left (and the
checkmark indicating that the condition is enabled will
disappear). Click “OK”.
63
Now the query will show all Establishment Types.
64
Let’s edit the Est Type condition. Click on “Tools” and
“Conditions”.
65
Highlight the “Establishment Type= 48’” condition by
clicking on it and click “Edit”.
66
Change the Value from 48 to 47 and click “OK”.
67
Are we ready to run the query?
68
No.
We need to enable the condition. Click on “Establishment Type=
47” condition and click the (>) arrow to move the condition to the
“Selected” window and enable it.
69
The condition is now enabled. Notice the check mark next
to the condition now. Click OK.
70
Now you will only see Café/Restaurant Establishments.
71
Let’s rename the worksheet. Click on “Edit” and “Rename Worksheet”.
72
Change the name from “Sheet 1” to “Restaurants” and click “OK”.
73
Now the worksheet’s name has been changed.
74
Let’s save the workbook. Click on “File” and “Save”.
75
Enter “Training Query 1” as the new name and click “Save”.
Since multiple users are using the same signons, put your
initials into your workbook name.
76
Let’s close the workbook. Click on “File” and “Close”.
77
What we will do next
•
•
•
•
Open an existing workbook.
Add more items to the worksheet.
Add more conditions to the worksheet.
Run the query.
78
Let’s open an existing workbook. Click on “File” and “Open”.
79
Select “Training Query 1” by clicking it. Notice the
description at the bottom.
80
Click Open
81
When you click Open, the query
will automatically run.
82
To add a description click on “File” and “Workbook Properties”.
83
Enter a description for your workbook and click “OK”.
84
Let’s add more items our worksheet.
Click on “Edit” and “Worksheet”.
85
Notice the “grayed out” folders – No join currently exists between
these folders and the “Establishments” folder.
86
Click the (+) sign next to the “Establishments” folder.
87
Scroll down and click on “Next Insp Date”.
88
Click on (>) button to move “Next Insp Date” over to the
selected pane.
89
Let’s add an additional condition. Click the “New” button and click
“New Condition”.
90
Arrow down the item list and click on “Next Insp Date”.
91
Arrow down the condition list and click on “BETWEEN”.
92
Enter ’01-JAN-2012’ in the first value box.
93
Enter ’31-JAN-2012’ in the second value box. Click “OK”.
94
Your new conditions now shows in the list. Click OK to run the query.
95
Our query returns and now shows only those establishments that have
an inspection due in January 2012.
96
Let’s rename the column heading for “Next Insp Date”. Right click the
column heading “Next Insp Date” and click “Edit Heading”.
97
Enter “Next Survey” and click “OK”.
98
Now you should see something like this…….
99
Notice that the item name did not change, just the column heading.
100
Let’s save our query. Click File, and then click Save.
101
The worksheet is ok, but
• The worksheet we created is ok, but if we
want to see establishments other than
food stands (est type 2) we need to edit
the worksheet and change the value of the
condition. This is time consuming. Lets
discuss Page Items.
102
To add a page item to our query, click View and then click Page Item.
103
Our page now has a Page Item box at the top of the screen.
104
Click on the Establishment Type heading and drag it up to the page
item box.
105
The screen will refresh and move the Establishment type to the top of
the screen. Notice the drop down next to the Establishment Type now.
106
Our drop down only shows 47 Café/Restaurants. That is because we
have a condition to only put establishment type 47.
107
Click Edit, and then click Worksheet.
108
Click on our condition, Establishment Type = ’47 Café/Restaurants’ and
then click the (<) button. This will remove that condition from our query.
109
The condition has been disabled. Now let’s click the OK button.
110
Our screen refreshes and it doesn’t look any different. But wait, click
the drop down next to our establishment type.
111
Now are drop down has all of the Establishment Types for us to choose
from. Find the one you want to see and then click OK.
112
Your query will refresh and now show the Establishment Type you
requested. This is much easier than editing the condition each time.
113
Let’s add more fields to our Page Items. Click Edit and then click Worksheet.
114
Scroll down thru the establishment folder until you find
Next Insp Date:Year and Next Insp Date:Month and then click the (>)
button.
115
Now let’s click on the Table Layout Tab.
116
On the Table Layout tab, drag Next Insp Date: Year and Next Insp
Date: Month up to the page items. Then click the OK button.
117
Your query will refresh and now you will have the options to change
you’re the Month and Year for the establishments next survey date.
118
Once again, our drop down’s only offer January of 2012. Let’s disable
our condition and that will give us more choices.
119
Click Edit, and then click Worksheet.
120
Click on our condition for Next Insp Date and then click the (<) button.
This will remove that condition from our query.
121
The condition has been disabled. Now let’s click the OK button.
122
Our query will refresh and now we have more options available in our
Year and Month drop downs. Select one and your query will refresh.
123
Our query will re-fresh with our selection. Note: the drop downs
selection will contain all values found in that field.
124
• Let’s rename the worksheet
(because “Restaurants” is no
longer meaningful).
125
Click on “Edit” and “Rename Worksheet”.
126
Enter “Establishment with Next Survey” under Name and click “OK”.
127
128
Click on “File” and “Save” then “File” and “Close”.
129
QUERY #2
• We will:
–
–
–
–
–
–
–
–
–
–
–
Open an existing workbook
Build a cross-tab query
Move around “AXIS” items
Run the query
Add a title
Edit a heading
Rename the worksheet
Add totals to columns and rows
Add a graph
Create a new cross-tab worksheet
Save and close the workbook
130
Let’s open an existing workbook. Click on “File” and “Open”.
131
Select the “Training Query 1” workbook and click “Open”.
132
Wait for the query to run, then click “Edit” and “Add Worksheet”. This
will add a second worksheet to you workbook.
133
Uncheck Title, Page Items, Graph, and Text Area. Change the display
from Table to Crosstab. Click “Next”.
134
Scroll down thru the list of folders and click the (+) sign next to the
Survey Base folder.
135
From the Survey Base folder, select Survey status, Survey date: Year,
and Survey date: Month.
136
Find Survey date and click the plus (+) sign next to the field. It will
open up and show three new fields, Count, Min, and Max.
137
Click on COUNT, and then click (>) to send the field to the selected list.
138
We will now set up a condition to only look at surveys from 2011. Click
on the Condition tab.
139
On the Condition tab, notice that the conditions that were built for the first query
are listed but not enabled. If they were appropriate you could enable those
conditions. For our current query, they are not. Click the New button.
140
After clicking the New button, then select New Condition.
141
Click the drop down for Item, and then click the More items.
142
You will then be presented with a list of items from folders we have
selected from. Scroll down and click on Survey Date, then click OK.
143
Click the drop down for Conditions and select BETWEEN.
144
Then we will set our values to ’01-JAN-2011’ AND ’31-DEC-2011’.
Then click OK.
145
We have our condition built, so now lets click NEXT.
146
When you click Next, you will receive the following warning.
We are about to fix it, so click OK.
147
Left-click and hold the mouse button down on the “Survey status” and
drag it down from the top to the left hand column.
148
Here you can see where the item is to be “dropped”.
149
Click “Finish” to run the query.
150
Now you should see something like this. Across the top of the report
are the months of 2011. Down the left side of the report are the survey
status’. Then the report shows the number of surveys entered for each
month.
151
Let’s add a title to our query.
152
Click on View, then click on Title.
153
Double-click where it says to double-click.
154
When you double-click, the following screen will pop up. Enter what title
as you would like it to appear on the report.
155
Once the title is entered, click OK.
156
The title now shows, lets add totals.
157
From the menu bar, click on Tools and then
click Totals.
158
Click on the New button, and then New
Total.
159
The first field will be the one you want to total up. Discoverer will try to
determine which field to total up. A drop down is available if the field is
incorrect.
160
The next field wants to know what kind of total you want. The system
will default to Sum.
161
Next, where do you want the total to be located.
162
We want our total to be on the right. Then click OK.
163
Then click OK to run the query.
164
The report now has the total
number of surveys for 2011.
165
Let’s change the heading over our total from “Sum” to “Total for year”.
Click Tools, then Totals.
166
Click on the total, then click Edit.
167
Uncheck the box next to “Generate label automatically”, then type your
heading in the box that has “Sum” in it.
168
Now click OK, then click OK to run report.
169
The report now shows our new title.
170
Let’s rename one of our headings.
171
Right click on the heading Survey date COUNT
and then click Edit Heading.
172
Change the heading to “Number of Surveys”
then click OK.
173
• Let’s give our new worksheet a
name. Right now it shows as
Sheet 2.
174
Click on “Edit” and “Rename Worksheet”.
175
Enter “Number of Surveys by Month and Status” under Name and click
“OK”.
176
177
The worksheet is ok, but
• The worksheet we created is ok, but if we
want to see survey counts for other than
2011, we need to edit the worksheet and
change the value of the condition. This is
time consuming. Lets add a Page Items.
178
To add a page item to our query, click View and then click Page Item.
179
Our page now has a Page Item box at the top of the screen.
180
Click on the 2011 heading and drag it up to the page item box.
181
The screen will refresh and the Year will move to the top of the screen.
Notice the drop down next to the Survey date: Year now.
182
Our drop down only shows 2011. That is because we have a condition
to only look at surveys with a date between Jan 1 and Dec 31 2011.
183
Click Edit, and then click Worksheet.
184
Click on our condition, Survey date BETWEEN 01-JAN-2011 and 31DEC-2011, and then click the (<) button. This will remove that
condition from our query.
185
The condition has been disabled. Now let’s click the OK button.
186
Our screen refreshes and it doesn’t look any different. But wait, click
the drop down next to our Survey date: Year. All years that surveys are
entered will be available in the drop down.
187
Now our drop down has all of the Years for us to choose
from. Find the one you want to see and then click OK.
188
Your query will refresh and now show the Year you requested. This is
much easier than editing the condition each time.
189
Graphs
With the total number of surveys,
we can now produce a graph to
compare our months.
190
Click on edit, then click Graph.
191
The edit graph window will appear. On the first
tab, you can select the style of graph you need.
We are going to use Bar.
192
The next tab is Style.
193
On the style tab, you can select
your colors.
194
Titles, Totals and Series allows you
to pick the data you want graphed.
195
X-Axis is for making adjustments to
the x-axis of your graph.
196
Y-Axis is for making adjustments to
y-axis of your graph.
197
Plot Area allows changes to the
graph view.
198
Legend allows for changes to the
legend.
199
Click the OK button once your
changes are made.
200
The screen will refresh and your
graph will show at the bottom.
201
Click on “File” and “Save” then “File” and “Close”.
202
Exporting Workbook Data
• Discoverer lets you convert your
worksheets into other popular formats
such as Excel, CSV, etc.
• Let’s convert our “Training Query 1” into
an Excel spreadsheet.
203
Click on “File” and “Open”.
204
Select “Training Query 1” and click “Open”.
205
Click on the “Establishments with Next Survey” tab at the bottom. Click
“OK” to run the query.
206
Wait for the query to process then click “File” and “Export”.
207
Accept the default to export the current worksheet and click “Next”.
208
Chose the destination for your file and click “Next”.
209
Pick the location you want the file saved, the file type, and the file
name.
210
Accept the default for “Supervised” and click “Finish”.
211
Once the process is complete you will get an export log informing you
that the export was successful. Click “OK”.
212
Now you can get into Excel and open the worksheet that you
exported.
213
Excel spreadsheet
214
Deleting Worksheets
• Deleting a worksheet from the database
permanently removes it. You should not delete
a worksheet from the database unless you are
absolutely certain that you won’t need it in the
future.
• Note: You cannot delete a worksheet unless you
have the appropriate database privileges. Also,
you cannot delete a shared worksheet unless
you created it.
215
To delete a worksheet click on “Edit” and “Delete Worksheet”.
216
When prompted “Are you sure?” – Click “Yes” or “No”.
217
Sharing Workbooks
• Sharing a workbook allows others to view,
analyze, and print the workbook. They will
only see the data that belongs to them.
* This assumes they have the appropriate
database privileges.
218
To share a workbook click on “File” and “Share”.
219
Choose the user you would like to share your workbook
with and click the “>” arrow to move it to the Shared
window.
220
Once you have selected the user(s) you want to share this with click
“OK”. Now when DBUCHANAN signs into Discoverer he will see this
workbook has been shared with him.
221
He/She who giveth also taketh
away.
• If your co-worker and possible friend gets
you really mad, you can choose to unshare your workbooks by reversing the
process.
222
Options
•
•
•
•
•
•
General
Query Governor
Sheet
Formats
Advanced
EUL
223
To change Options click on “Tools” and “Options”.
224
Options - General
225
Options – Query Governor
226
Options - Sheet
227
Options - Formats
228
Printing a Worksheet
• OracleBI Discoverer printing is WYSIWYG
– (What you see is what you get).
• There is very little you can do to adjust the
way that the worksheet prints.
• For reports, we may have to use another
product (Access, Excel, etc.).
229
Where to get help…
230
Help Topics – Help Navigator
231
Contact CDP’s Customer Support
• [email protected]
• 1-866-237-4814
232