Oracle Discoverer is an extremely popular tool for querying data warehouses and generating reports, because it is very easy and intuitive to use and has been designed for use by users who are not computer literate. Therefore, in order for our non-computer-literate users to be able to use Discoverer easily, some setup is required from the DBA group. But once this has been done, your end users should really like using this tool. There are four parts to Oracle Discoverer:
Discoverer Administrator is the version that is used by the DBAs of the data warehouse to set up the environment for the general Discoverer users. General users will either use the Desktop or Plus versions, because they have been designed especially for people who are not familiar with writing computer programs, as well as for anyone who is not familiar with SQL and prefers to deal with data using familiar business entities. For those users who may be concerned that they could change the data within the data warehouse, fear not; Discoverer Viewer allows the user only to view predefined reports. The attraction of using Discoverer Viewer is that all users require to access the data is their PC and a browser.
Oracle Discoverer is also integrated with Oracle Portal, which we will learn more about in Chapter 8. Via this route, access to data can be achieved via a list of workbooks or a worksheet/graph portal.
The use of Oracle Discoverer is not restricted to Oracle databases, but that is how we will illustrate its use here. The databases that have been certified against Discoverer include DB2 for NT and MVS, Microsoft SQL Server, Sybase, and Terradata.
Before we learn how to set up Discoverer, let's first look at the types of reports that it can produce.
Discoverer is a very powerful reporting tool; there is a version called Discoverer Viewer, which provides a user with the ability to access predefined reports and have the capability to modify those reports in a limited way. The advantage of this approach is that the user only needs a PC and a browser to access the data in our warehouse; the DBA can rest assured that users cannot change the data.
You can start Discoverer viewer from your browser using a URL such as:
A connection is made to this database, and a list of the available workbooks to which we can attach is presented. We select our workbook, Easydw_workbook, and the query contained within is immediately executed. In Figure 6.23 we can monitor the progress of our executing query; when it has completed, our report is displayed-as shown in Figure 6.24.
Figure 6.23: Discoverer Viewer-executing our query.
Figure 6.24: Discoverer Viewer-category sales by month report.
Since we defined this report as both a table and a graph, both versions are displayed. Note that at any time we can hide the graph or table by clicking on the Hide Data or Hide Chart option. The report shown here is the default generated by Discoverer; I am sure you will agree that it is a very nice format which is presented well and is easy to understand.
Inside a workbook there can be many reports, and each one is accessed by simply clicking on the tab on the left-hand side where the report is named.
One of the advantages of using Discoverer Viewer is that the user is not able to change the data in the warehouse or generate new reports. However, there are limited customizations that users can do to this report, such as changing the sort order or table layout or by clicking on the Presentation Options items, as illustrated in Figure 6.25, to change how the report looks.
Figure 6.25: Discoverer Viewer-presentations options (screen 1).
One of the features that makes Discoverer nice to use is the ability to group together a number of reports that can be easily run and customized to your own requirements. In the example shown here, we have a workbook called Easydw_reports, and, referring to Figure 6.26, at the bottom of the screen we can see three tabs for each of the reports that are available:
Total Sales by Customer
Most Popular Products
Figure 6.26: Discoverer Viewer-presentations options (screen 2).
In Figure 6.26, we see both a table on the left and a graph on the right generated from data in our warehouse. When this report is run, users are prompted to specify their criteria for what makes a best customer (e.g., purchases more than $15,000) and then the data is displayed. So we can see that our best customer at the moment is a company called Harry Travel.
When a report is produced, people reading it may feel it is very interesting, but they need to know more about how this data is derived. For example, suppose a user is viewing the report shown in Figure 6.27 and sees that cameras were the most frequently sold item. This could raise the question: Do we sell the same number of cameras throughout the year or is it seasonal?
Figure 6.27: Discoverer Viewer-presentations options (screen 3).
Anyone viewing this report can right-click on an item, such as camera, and select the Drill option. Discoverer will now ask you how you want to drill on this data. In Figure 6.27, we have drilled down to see the sales by month, and we see that we are selling about the same number of cameras every month. When drilling on the data, you only have to drill on the items you are interested in.
Now that we have caught a glimpse of the types of reports Discoverer can produce, we must return to setting up Discoverer; before you can use it, the setup we mentioned earlier must be performed.
Although this may seem like a lot of work initially, once completed, users will really appreciate the work you have done. The environment that you create will determine exactly which data in the warehouse your users can see and how they see this data. Most important, they won't need to understand how to join data in order to query the data warehouse. To begin using Discoverer, first connect to your Oracle database.
You may prefer to create a special user for Discoverer and connect to your database using that user to ensure that the metadata is loaded under that user name.
The first time the Discoverer Administrator is started, you will be asked to create an end-user layer (EUL), which comprises all the metadata that is needed by Discoverer; therefore, this task will be performed only once. Figure 6.28 shows the screen where you can manage the end-user layer, including creating a new one or deleting an existing one.
Figure 6.28: Discoverer Administrator-create an EUL.
It will then ask who is to own the EUL, and when you click on the Finish button it is created. You must then wait for a few moments while the metadata is loaded into the database.
There are many steps involved in setting up the environment for Discoverer. To help you remember them, a window will appear, as shown in Figure 6.29, that lists all of these tasks. By clicking on any item in this list, you can automatically start that component.
Figure 6.29: Discoverer Administrator-task list.
Once the definition of the end-user layer is complete, a business area is named, where all the information needed to query the warehouse must be defined. The purpose of the business area is to group information into business-oriented categories, such as sales or finance, that are familiar to end users. Business areas are the unit of access control and a user can be assigned to one or more business areas. Users will then have access to all of the objects within the assigned business area.
Within this business area, you will specify exactly which data a user may access and how those data may be joined, as well as describe data aggregations and new data items based on calculations on existing data items. Creating the business area will take some time, but it will reap significant benefits later.
When creating the business area, we must first specify the schemas from which data is to be made available to the end user, as illustrated in Figure 6.30. We can select any number of users from the list and also request that it only selects items from the list that match the pattern specified in the box. In our example, we have selected only the user or schema EASYDW.
Figure 6.30: Discoverer Administrator-select the schema.
Once we know the schema from which the data will be available, we can then explicitly state from which tables or views we can retrieve data, as shown in Figure 6.31. Using our Easy Shopping example, we have given the users access to four of the five tables in this data warehouse.
Figure 6.31: Discoverer Administrator-select the tables and views.
One extremely useful feature in Discoverer, is the ability of the wizard to automatically create joins based on primary and foreign keys and create hierarchies from the data, as illustrated in Figure 6.32. By allowing the wizard to perform these tasks, there will be less setup work for you to do, and by default these options are already selected.
Figure 6.32: Discoverer Administrator-automatic joins.
There is one final screen before the first stage in creating the business area is complete. In Figure 6.33 we must name the EUL, and optionally add a description to describe what is being held. Discoverer will also generate names for all of the objects, and in Figure 6.33 you can see the options available, such as capitalize or replace underscores with spaces.
Figure 6.33: Administrator-naming the business area.
Clicking on the Finish button, the first stage in creating our business area is now complete, and the screen shown in Figure 6.34 will now appear. This is where the real work begins. We can now set up the rest of the business area using our task list, shown in Figure 6.29, as a reminder of the steps to complete. Although we have shown only the creation of one business area, you could create any number of business areas in your environment, each with their own unique set of data requirements.
Figure 6.34: Discoverer Administrator-setting up the business area.
By default, when access is given to a table, all the columns in that table are accessible. What is nice about Discoverer is that users can only access data via the business area. If the table and column are not included, the user will never even know that this data existed.
In Figure 6.34, we can see all the tables from the database that we have access to. Note that our table TODAYS_SPECIAL_OFFERS is now called Todays Special Offer as a result of the naming change requested earlier. By default, the end user will have access to every column in those tables. If you click on the table name to expand it, all the columns in that table will appear. To remove any of those columns, simply click on the item using the right mouse button, and a drop-down list will appear. One of the items in that list is delete item. Simply select that option, and the item will be removed from the business area but not from the database.
Before moving on, there is some important terminology that you must familiarize yourself with. In Discoverer, a table or view is known as a folder, and a column from the table is called an item. A folder can be one of two types: simple, where it is based on a single database table or view, or complex, where it can contain items from other folders and can be nested
An item corresponds to a column in a relational database. A simple item is based on a single column in the database, but an item can also be calculated or derived based on a formula using other items, functions, or operators.
The attributes of any of the items that you have selected may be modified by selecting that item, clicking on the right mouse button, and then selecting Properties. The window illustrated in Figure 6.35 will then appear, and you can then modify whichever properties you like. In this example, we have changed the item's name from Supplier to Main Supplier, which means that our users can be presented with friendly meaningful names rather than computer format names.
Figure 6.35: Discoverer Administrator-change the item details.
When the business area is first created, if the database has primary and foreign keys defined, then Discoverer will automatically create joins between those constraints. However, you can specify your own joins by selecting Insert from the strip menu and then Joins. The window illustrated in Figure 6.36 will appear.
Figure 6.36: Discoverer Administrator-define a join.
A join is defined by selecting, from the drop-down list, which item from the folder is to be joined and the type of join. In our example in Figure 6.36, we have specified a join between the Todays Special Offers table and the Product table using the column Product_id. The advantage of defining these joins now is that when a user writes a query, Discoverer will know how to join the data, so this is one less piece of information that our user has to supply.
Another feature that many DBAs may require is the ability to create new columns or items in the database by calculating their results from other columns.
A calculation creates a new item in the end-user layer. It will not add underlying columns to database tables; it is used to create a new item where there is no underlying database column containing the required data. Calculations can be simple, such as weight*4.54, or they can be complex mathematical or statistical expressions. For example, in Figure 6.37, a new column called Total Cost is created in the Purchases table by adding together the columns purchase price and shipping charges. You can create as many of these types of calculations as you require, and they will appear as an item for that table.
Figure 6.37: Discoverer Administrator-creating a calculated item.
We have already seen that hierarchies play an important role in our data warehouse. Although in Oracle we can create dimensions, at the time of writing these are not used by Discoverer, and we must create our own dimensions, which are known in Discoverer as hierarchies. Hierarchies are very important in Discoverer, because if an item is in a hierarchy, then users can drill up, changeing the query to show a higher level of details, or drill down to show more detail.
This is how we were able to drill on our report shown in Figure 6.27.
A hierarchy is created by clicking on Insert in the menu at the top and then selecting Hierarchy. First you will be asked about to the type of hierarchy you want to create, an item or a date. When the business area is first created-it is quite likely that Discoverer will automatically create a timebased hierarchy. Therefore, you will probably only have to create non-timebased hierarchies.
Figure 6.38 shows how easy it is to create the hierarchy by selecting the items and then defining the hierarchy relationship. In Figure 6.38, we have created a very simple hierarchy between product_id and category.
Figure 6.38: Discoverer Administrator-defining a hierarchy.
When the end users are actually querying the data, there are times when it may be helpful if they can see a possible list of values. For example, suppose they want to pick out all of the electrical items. If they know which category they are represented in, then this will facilitate rapid query generation.
An item class can describe the hierarchical relationship between items, a list of values, alternative sort keys for items, and the display methods. Therefore, an item class defines all of the attributes for an item. Once an item class has been defined it can then be assigned to other items that share similar properties.
An item class is created by selecting Insert from the menu at the top and then Insert Class from the list or by clicking on the text in the administration task list, shown in Figure 6.29. Several different types of classes of items may be created, but if you select List of Values, all you have to select is the column containing the values and which tables will use it. When it is complete, click on the tab Item Classes, and the window illustrated in Figure 6.39 will appear. In this example a list of values called Categories has been created from our products dimension table. If we expand the entry categories, the data warehouse can be queried, and all the different values will be displayed. In Figure 6.39, we can see some of the different products that are sold. Here we only have a few items, but in the real world, where you may have a number of different values, you can omit this step of displaying the results.
Figure 6.39: Discoverer Administrator-item class.
When an item class is defined, you can also define the sort order for the data. In Figure 6.39, we have chosen to use the conventional alphabetical method, but that is not always suitable and you can specify a logical order such as N,S,E,W rather than E,N,S,W.
We have already seen the importance of creating and using materialized views in our data warehouse in Chapter 4. Discoverer allows you to create your own materialized views, which Discoverer calls summaries, from its Summary Wizard, either by:
Using query performance statistics
Manually creating the summary
Registering a previously built summary
To create your summary, click on Insert in the menu at the top, and then select Summary; the Summary Wizard appears, and you will be asked the type of summary that you wish to create. If you select From Items in the end-user layer, you can create your own summary.
Next, the window illustrated in Figure 6.40 appears. There you select the folders and items from within those tables that are to appear in the summary. In our example, we have selected only the Purchases table, but you could select multiple tables. Then we chose four of the data items in that table. For the item purchase price, we have asked that this value be aggregated. You will see that Discoverer will automatically supply a range of functions for you to select when a function may be applied to an item. In Figure 6.40, for the item purchase_price, we have asked that the SUM function be applied to this item. Next, you are asked to specify which groups of items you require.
Figure 6.40: Discoverer Administrator-creating a summary.
As we have seen, it is very important to ensure that our materialized views contain the latest data. When defining your summary in Discoverer, you can specify how often it is to be refreshed. Remember, that as new data is added to the warehouse, the summaries must be maintained to reflect the latest data. In Figure 6.41, we can see that we have stated that this summary should be refreshed every hour.
Figure 6.41: Discoverer Administrator-refreshing the summary.
If you don't know what summaries to create, then Discoverer can recommend them for you using its own Summary Wizard. In Figure 6.42, we can see one of the steps from the wizard, where we can select the summaries we require based on our space requirements. This wizard is very similar to Summary Management's own Summary Advisor Wizard, described in Chapter 4, but the recommendation process used by Discoverer is different from the one used by the server's Summary Wizard.
Figure 6.42: Discoverer Administrator-Summary Wizard.
Creating either Discoverer summaries or materialized views is very important if you want to achieve the fastest query response time. We have seen here how to create summaries directly in Discoverer. You also can create materialized views, as discussed in Chapter 4, and Discoverer will still use them, because a materialized view doesn't have to be created by Discoverer for it to be used by Discoverer.
The final setup task is defining who may access the business area you have just created. You can start this component by double-clicking on Grant Business Area Access, shown in Figure 6.29. A window will appear that will allow you to state which users can access a business area or which business areas a user can access. In Figure 6.43, we can see that the only user we will grant access to in our business area is EASYDW. Don't forget that although in this example we have enabled access via the user name, you can also grant access to the business area via the roles that may be given to a user. When you are satisfied that all the relevant access rights have been given, click on the Apply button to complete the changes.
Figure 6.43: Administrator-granting access to the business area.
Figure 6.44 illustrates our complete business area. Expanding just the Purchases table, we can see all the items that are available to our users, the new calculation that we created, called Total Cost, and the functions that we can apply to that column. At the bottom of the window, we can see that joins have been created from the Purchases table to the Product and Time table. Of course, not all the information can be displayed on this one window, so, to see the Hierarchies tab you will have to click it. The same is true for item classes and summary information.
Figure 6.44: Discoverer Administrator-business area.
We have now completed all of the basic setup tasks for using Discoverer. Don't forget to save all of your work, and please remember that the tool is much more comprehensive than we have shown. For example, we haven't shown that Discoverer fully supports the Oracle Analytical Functions to enable sophisticated analysis of the data. Now, we can start using Discoverer Desktop, Viewer, or Discoverer Plus to retrieve data from our data warehouse.
Once the environment has been set up for querying via Discoverer, you can start either the Desktop edition or the Plus version, which has been designed for the Web. In this section our examples will use the Web version, Discoverer Plus.
Reports in Discoverer are held in a workbook, so the first step is to connect to the database where our workbook resides. In Discoverer Plus, when you launch Plus from your browser using a URL such as
you will be presented with a list of the databases you can connect to, as shown in Figure 6.45. Here we can see that we only have one database, called EasyDW.
Figure 6.45: Discoverer Plus-list of databases for connection.
These connections will have been defined previously and comprise your user name and database name. Therefore, all you must supply is your password, and you will be asked whether to create or open an existing workbook. In Figure 6.46, we see the initial window, where we specify the workbook and how the results are to be displayed. Discoverer offers a range of display options, such as showing the data in tabular or a crosstab form. Using the crosstab format is ideal when you have multidimensional data to display.
Figure 6.46: Discoverer Plus-using a workbook.
Now it is time to specify exactly what is to be reported in this query. First, you must select the business area that was defined using the Administrator edition, which determines the data that you may see. In our example, we have only the Sales Data business area, but there could be several to choose from.
Now we have to select the items from those folders. This is a simple process, involving moving them from the left window (available) to the right window (selected). In Figure 6.47, we have selected the item category from the product folder and the date from the purchases folder and then requested that the item purchase price should be aggregated.
Figure 6.47: Discoverer Plus-selecting data to be displayed.
When Discoverer identifies an item upon which functions may be applied, all possible options are made available to the user. For example, in Figure 6.47, we see that for the item purchase_price, we can apply the functions MAX and COUNT, and Discoverer can also calculate the minimum, maximum, or an average. We have already chosen to sum purchase price by simply expanding the aggregate options for purchase price in the left window, then selecing the required aggregate, and clicking on the right arrow. This process is repeated for every item required in the report.
Next, we must specify the layout for our report. This is very easily achieved by dragging the columns to where you require them on the report. In Figure 6.48, we have specified our order as category, date, and total price. But we could easily drag the date column and place it after the total purchases for that category. At this stage we can only decided how the data is to be presented; on the next screen we can specify formats and headings.
Figure 6.48: Discoverer Plus-table layout.
For each item that will be displayed on our report we can now specify how those data is to be formatted and select the heading to be used on reports. In Figure 6.49, we have changed the heading for our sum on purchase_price to be Total Purchases. By clicking on the Format Heading button, the font and alignment options can be defined, and clicking on Format Data allows us to specify how the data is actually presented. In this example we have decided that our total will have no decimal places.
Figure 6.49: Discoverer Plus-format headings.
Now we can specify a condition to limit the results of our worksheet to a specific criterion. There are some very extensive options available here; however, in this example we are going to view all of the data. Then we can specify how to sort all of our data, which is achieved by clicking on the Add button and selecting a column. It then appears in the list, and you can also specify the sort order. In our example in Figure 6.50 we are sorting by date first and then category.
Figure 6.50: Discoverer Plus-sorting the data.
At this stage we could also add calculations to appear in our report, such as profit made by subtracting purchase price from cost price. In our report today we will not include any calculations. Another option is the ability to create a percentage point on any item in the report that can be useful to help understand the data.
We have already said that we want to sum the item purchase price, and in Figure 6.51 we can now add a Total to this item as well. As you can see, there are a number of options available, such as whether we want a subtotal, the type of sum to perform, and how the data should be formatted. Once again, we can create as many totals as we need for inclusion in our report.
Figure 6.51: Discoverer Plus-defining totals.
Clicking on the Finish button will display our data. Discoverer will now query our data warehouse directly and show us the results, as illustrated in Figure 6.52. There we can see the total sales by category for a given date.
Figure 6.52: Discoverer Plus-report.
You may have to format the cells of the report to see the data if the numbers are large, because, by default, it uses small numbers.
Note that in order to report this data, we did not have to specify how to join the tables we selected, because the join information had already been specified in the business area. This is one of the really nice benefits of using Discoverer, because the end user does not have to know about relational joins. All of this work has been done behind the scenes by the person who created the business area using the Administrator edition. Now all our user has to do is select the data of interest, answer the questions on a few screens, and then click the Finish button to request the information.
Now that we have our report, we can customize it to our own requirements by either clicking on the items or selecting from the menu at the top. To change the format of our numbers, if we click on Sheet and then Format, Figure 6.53 appears, where we can amend the item. See how all the screens we used to define our report are now shown as tabs, so we can easily select them and amend anything there as well.
Figure 6.53: Discoverer Plus-customize the report.
If you are interested in how the report is being executed within the database, selecting Sheet followed by Show SQL, will bring up the SQL Inspector box shown in Figure 6.54. Here you can either view the SQL used to execute the query or, as shown in Figure 6.54, the query's execution plan.
Figure 6.54: Discoverer Plus-SQL Inspector.
Now that we have our report, we may want to look at the data from a different perspective. For example, we could roll up the data to see sales by month or drill down to see which electrical item generated most revenue.
You can change the report by clicking on the arrow, and the screen shown in Figure 6.55 will appear, which shows you how you can drill on this data. Here we have chosen to show the data by month, but we could select any one of the five options shown. Note that our user did not have to tell Discoverer about how it could report on the data. All this information was previously defined during the setup, so once again our end user needs to know little about how the data is stored in order to get the required information.
Figure 6.55: Discoverer Plus-drilling up/down the data.
In Figure 6.56, we now see our report with the information at the month level; because we have a time hierarchy, we can change our report to sum by year, instead of by day, simply by selecting year from the list. Hopefully, now you are beginning to appreciate all of the setup work that we completed using the Administrator edition.
Figure 6.56: Discoverer Plus-report at the month level.
So far we have only viewed our data in a traditional report format, but Discoverer Plus can also represent our data graphically answering a few questions using the Graph Wizard, which is shown in Figure 6.57.
Figure 6.57: Discoverer Plus-Graph Wizard.
There are over a dozen different types of graphs available from Discoverer Plus, and you can totally customize the output by adding your own titles and legends. We have decided to use a pie chart to represent our monthly sales in Figure 6.58, which makes it easy for us to see that electrical items were the most popular when we ran our sale on January 1.
Figure 6.58: Discoverer Plus-graph of monthly sales.
In this sample Discoverer report, we reported all of the data, but you can select a subset by specifying a condition. What is nice in Discoverer Plus is that you can set up a number of conditions and then select the ones you want for this report. In Figure 6.59, we see one of the screens where you can specify these conditions.
Figure 6.59: Discoverer Plus-select specific data.
There is actually another screen where you actually specify the condition, which can be done using quite complex expressions. Here we can see that we have two conditions defined, and we are using the one for items over $100 to see what effect it has on our report. Then, when we view the new report in Figure 6.60, we can see that it was only electrical items that cost more than $100-and even then, they did not account for most of our sales that month.
Figure 6.60: Discoverer Plus-report using conditions.
Our completed report can also be exported into various formats so that it can be published on our Web site; Chapter 8 explains how to do this.
There are many more facilities available from Discoverer, but, hopefully, you now have an appreciation of some of this tool's capabilities and how ideal this tool is for end users who may not be very computer literate.