2.32k likes | 2.41k Views
Introduction to OracleBI Discoverer. Beginning Note:. Just as any windows based application there are always many different ways to accomplish the same tasks. (Copy, Paste, Save, etc…)
E N D
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.
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.
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.
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?
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)
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
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
END USERS (Workbooks & Worksheets) Administrator End User Layer (EUL) (Business areas,Folders,Items etc…) CDP-DataMart (Oracle 9i database) ORACLE DISCOVERER OVERVIEW END USERS (Workbooks & Worksheets) END USERS (Workbooks & Worksheets) END USERS (Workbooks & Worksheets) END USERS (Workbooks & Worksheets) END USERS (Workbooks & Worksheets) END USERS (Workbooks & Worksheets) END USERS (Workbooks & Worksheets) END USERS (Workbooks & Worksheets) END USERS (Workbooks & Worksheets) END USERS (Workbooks & Worksheets) Administrator Administrator Administrator Administrator Administrator Administrator Administrator Administrator Administrator Administrator End User Layer (EUL) (Business areas,Folders,Items etc…) End User Layer (EUL) (Business areas,Folders,Items etc…) End User Layer (EUL) (Business areas,Folders,Items etc…) End User Layer (EUL) (Business areas,Folders,Items etc…) End User Layer (EUL) (Business areas,Folders,Items etc…) End User Layer (EUL) (Business areas,Folders,Items etc…) End User Layer (EUL) (Business areas,Folders,Items etc…) End User Layer (EUL) (Business areas,Folders,Items etc…) End User Layer (EUL) (Business areas,Folders,Items etc…) CDP-DataMart (Oracle 9i database) CDP-DataMart (Oracle 9i database) CDP-DataMart (Oracle 9i database) CDP-DataMart (Oracle 9i database) CDP-DataMart (Oracle 9i database) CDP-DataMart (Oracle 9i database) CDP-DataMart (Oracle 9i database) CDP-DataMart (Oracle 9i database)
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.
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
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.
Enter User NameEnter PasswordEnter DatabaseEnter End User Layer… Then click “Go”
You may get a “Security Information” pop-up. Click “Yes” to display the items.
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.
Create/Open Workbook • Workbook Step #1 allows you to : • Open an existing workbook • Or • Create an new workbook
Click on “Create a new workbook”.We will open an existing workbook later in this session.
Uncheck “Title”, “Graph”, and “Text Area”. Leave the default “Table” selected and click “Next”.
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.
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.
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.
Select the “Establishments” Folder.Click the (+) sign to the left of “Establishments” to see the “Items”.
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.
Let’s select the • Est Number • Premise Name • Establishment Type • Primary Phone
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.
Once you have selected the items – Click the “Conditions” tab.
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.
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.
If you select “More Items” you will see ALL the items that are in the folder(s) you are working with (Establishments)… click “Cancel”.
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.
Notice that the item “Establishment Type” now has the folder’s name in front of it.
Arrow down the Condition column and select the conditional expression “=“.
Manually enter 48 in the “Values” column to select only Bars – Click “OK”.
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’)
The NEW tab offers options for calculations, percentages, and totals. We see those later.
Table Layout • Workbook Step #3 allows you to re-arrange 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.
Click and drag the column names to the desired locations… then click “Next”.
Workbook Step 4 allows you to sort the worksheet by the data points. We will sort the worksheet later in the session… click “Next”.
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”.
You should see the “query progress” bar informing you of the status of the query.