Oracle Business Intelligence : The Condensed Guide to Analysis and Reporting — Save 50%
A fast track Oracle book and eBook guide to uncovering the analytical power of Oracle Business Intelligence: Analytic SQL, Oracle Discoverer, Oracle Reports, and Oracle Warehouse Builder.
In Oracle Discoverer, you can also use drilling to analyze data, getting a new angle on the data you're dealing with. There are several ways supported by Discoverer in which you can accomplish drilling. For example, you can drill data down to see more detailed information for a certain product. Or, on the contrary, you may need to view data at a higher level, drilling data up from products to categories. Another example is drilling to a related item, allowing you to add a related item that is not currently in the worksheet but can be found in a folder used by the worksheet.
This article by Yuli Vasiliev, author of Oracle Business Intelligence: The Condensed Guide to Analysis and Reporting, will walk you through the simple steps required to drill data on Discoverer worksheets. With the help of many examples, this article will introduce you to the following:
- Navigating a Discoverer worksheet data with drilling
- Drilling up and down
- Drilling to a related worksheet item
- Drilling from a worksheet graph
|Read more about this Oracle Business Intelligence book|
(For more resources on Oracle, see here.)
What is drilling?
In terms of Oracle Discoverer, drilling is a technique that enables you to quickly navigate through worksheet data, finding the answers to the questions facing your business. As mentioned, depending on your needs, you can use drilling to view the data you're working with in deeper detail or, in contrast, drill it up to a higher level. The drilling to detail technique enables you to look at the values making up a particular summary value. Also, you can drill to related items, adding related information that is not currently included in the worksheet.
So, Discoverer supports a set of drilling tools, including the following:
- Drilling up and down
- Drilling to a related item
- Drilling to detail
- Drilling out
The following sections cover the above tools in detail, providing examples on how you might use them.
Drilling to a related item
Let's begin with a discussion on how to drill to a related item, adding the detailed information for a certain item. As usual, this is best understood by example. Suppose you want to drill from the Maya Silver item, which can be found on the left axis of the worksheet, to the Orddate:Day item. Here are the steps to follow:
- Let's first create a copy of the worksheet to work with in this example. To do this, move to the worksheet discussed in the preceding example and select the Edit | Duplicate Worksheet | As Crosstab menu of Discoverer.
- In the Duplicate as Crosstab dialog, just click OK. As a result a copied worksheet should appear in the workbook.
- On the worksheet, right-click the Maya Silver item and select Drill… in the pop-up menu:
As a result, the Drill dialog should appear.
- In the Drill dialog, select Drill to a Related Item in the Where do you want to drill to? select box and then choose the Orddate:Day item, as shown in the following screenshot:
- Then, click OK to close the dialog and rearrange the data on the worksheet. The reorganized worksheet should now look like the following one:
As you can see, this shows the Maya Silver item broken down into day sales per product.
Now suppose you want to see a more detailed view of the Maya Silver item and break it out further into product category.
- Right-click the Maya Silver item and select Drill… in the pop-up menu. In the Drill dialog, select Drill to a Related Item in the Where do you want to drill to? select box and then choose the Category item. Next, click OK.
The resulting worksheet should look now like this:
As you can see, the result of the drilling operations you just performed is that you can see the dollar amount for Maya Silver detailed by category, by day, by product.
You may be asking yourself if it's possible to change the order in which the Maya Silver record is detailed. Say, you want to see it detailed in the following order: by day, by category, and finally by product. The answer is sure.
- On the left axis of the worksheet, drag the Orddate:Day item (the third from the left) to the second position within the same left axis, just before the Category item, as shown in the following screenshot:
As a result, you should see that the data on the worksheet has been rearranged as shown in the following screenshot:
Having just a few rows in the underlying tables, as we have here, is OK for demonstration purposes, since it results in compact screenshots. To see more meaningful figures on the worksheet though, you might insert more rows into the orderitems, orders, and products underlying tables. Once you're done with it, you can click the Refresh button on the Discoverer toolbar to see an updated worksheet.
- Select the File | Save menu option of Discoverer to save the worksheet discussed here.
Drilling up and down
As the name implies, drilling down is a technique you can use to float down a drill hierarchy to see data in more detail. And drilling up is the reverse operation, which you can use to slide up a drill hierarchy to see consolidated data. But what is a drill hierarchy?
Working with drill hierarchies
A drill hierarchy represents a set of items related to each other according to the foreign key relationships in the underlying tables. If a worksheet item is associated with a drill hierarchy, you can look at that hierarchy by clicking the drill icon located at the left of the heading of the worksheet item.
Suppose you want to look at the hierarchy associated with the Orddate item located on our worksheet at the top axis. To do this, click the Orddate drill icon. As a result, you should see the menu shown in the following screeenshot:
As you can see, you can drill up here from Orddate to Year, Quarter, or Month. The next screenshot illustrates what you would have if you chose Month.
It's important to note that you may have more than one hierarchy associated with a worksheet item. In this case, you can move on to the hierarchy you want to use through the All Hierarchies option on the drill menu.
eBook Price: $23.99
Book Price: $39.99
|Read more about this Oracle Business Intelligence book|
(For more resources on Oracle, see here)
Drilling down to see data in more detail
Now that you have drilled up to monthly data, can you drill down to daily figures again for a particular month? Yes, you can. Before moving any further though, let's add more data to the orderitems underlying table to see a more interesting layout.
Suppose you want to see more day columns under the Jan column. To achieve this, you need to insert items associated with an order placed by Maya Silver on any day in January but different from 10-JAN-10, because you already have an order placed on this day. This can be done with the following statements issued from SQLPlus, assuming you're connected as usr/usr:
INSERT INTO orderitems VALUES(1003, 1111, 10, 22.0);
Now you can refresh the data on the worksheet by clicking the Refresh button on the Discoverer toolbar and then click the Jan drill icon. In the drill menu, select Orddate: Day. The following screenshot illustrates what you should see as a result:
The previous example illustrates that you can drill down from a selective item, while the others presented at the same level remain collapsed. In this particular example, you drill down from the Jan item, while the Mar is still summarized.
Later, you can always collapse an expanded item by selecting the Collapse command in the drill menu. In this way, for example, you can collapse the Jan item drilled down to daily data as discussed in this section.
Drilling up to summarize data at a higher level
You've seen now how you can drill down to see the data of interest in more detail. In practice, though, you may also need to take a generalized look at your data.
Turning back to our example, let's now try to drill up from monthly data to quarter data, thus consolidating the worksheet figures. To accomplish this, you can use either the Drill dialog or drill menu:
- To open the drill menu associated with the Jan item, click its drill icon. The menu should look like this:
To drill up to quarter data, you can click the Oradate: Quarter command on the previous menu.
- The same can be done through the Drill dialog, which you can open by clicking the Drill… command in the pop-up menu associated with each item on the worksheet. For example, if you right-click the Jan item and then select Drill… in the pop-up menu, you get to the following dialog:
As you can see, in the Drill dialog, unlike the drill menu, you can see where a drill-down operation is and where a drill-up one is. You can easily recognize this by the arrow next to the item to drill to. Thus, the down arrow indicates a drill-down operation and the up arrow is used to indicate a drill-up operation.
Regardless of the way you've chosen to perform the drill-up operation discussed here, the resulting worksheet should look like the following one:
So, after the drill-up operation discussed in this section, the worksheet now shows the data aggregated at the Quarter level.
Drilling from a graph
It's interesting to note that Discoverer enables you to drill from the graph associated with a worksheet. Continuing with the example in the preceding section, the graph associated with the worksheet you can see in the previous figure should look like this:
(Move the mouse over the image to enlarge.)
Looking to the right in the above graph, you may notice the Orddate: Year link, which you can click if you want to drill up to year figures. But how can you drill down to, say, month figures? The answer is you can double-click any graph bar to drill one step down. Thus, in this particular example, if you double-click a graph bar, you'll launch a drill-down from quarter data to monthly data. So the result worksheet would look like the following one:
The graph will also be automatically redrawn. This should look now like the following screenshot:
Each bar in the above graph represents figures associated with a particular month. In this particular example, this can be either January or March figures. To find out which month a given bar belongs to, you can move the mouse cursor to it and then read the information in the pop-up label. For example, if you move the cursor to the first (far left) bar, you should see the following pop-up label:
As you can see from the label, the associated bar represents a January figure. So, you can double-click this bar to drill down to January daily figures. So, the worksheet should now look like this:
Now if you want to come back to the monthly figures, you can click the Orddate: Day drill icon on the top axis and select Orddate: Month. Moreover, if you want to see a month's figures divided into daily figures (the view you had before drilling up to quarters), you can click the drill icon next to the month of interest and select Orddate: Day in the menu.
Using the page items area
Often you need to select for display only part of the data you have on the worksheet. This is where the page items area above the crosstab comes in handy.
Proceeding to the example discussed here, suppose you want to select data by month, choosing a month in the items area select box. To do this, you can drag the month item from the top axis to the page items area. The following is the worksheet you should see as a result:
The layout will change, of course, if you select another month from the Page Items area select box. It's interesting to note that you're not limited to a single item to be set in the Page Items area. For example, you might drag the Salesperson Name item from the left axis. That would allow you to put another filter on the data to be displayed on the worksheet.
As you learned in this article, drilling is another useful tool when it comes to organizing data for more effective analysis and discovering hidden details. As you found out, Oracle Discoverer provides a set of useful drilling tools, making the task of getting answers to your business questions a breeze.
Walking through the examples, you learned how to navigate through a drill hierarchy up and down, getting to the level of detail you need. You also saw how to drill to a related item, to see the detailed information for a certain item, and how to do drilling from a worksheet graph.
- Oracle JRockit: The Definitive Guide [Book]
- Oracle Business Intelligence: Getting Business Information from Data [Article]
- An overview of Oracle Hyperion Interactive Reporting [Article]
- Debugging PL/SQL in Oracle SQL Developer 2.1 [Article]
- Managing User Accounts in Oracle Siebel CRM 8 [Article]
eBook Price: $23.99
Book Price: $39.99
About the Author :
Yuli Vasiliev is a software developer, freelance author, and consultant currently specializing in open-source development, Oracle technologies, and service-oriented architecture (SOA). He has over 10 years of software development experience as well as several years of technical writing experience. He wrote a series of technical articles for Oracle Technology Network (OTN) and Oracle Magazine.