Oracle Business Intelligence: Drilling Data Up and Down

What is data 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:

1. 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.
2. In the Duplicate as Crosstab dialog, just click OK. As a result a copied worksheet should appear in the workbook.
3. 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.

4. 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:

   
5. 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.

6. 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.

7. 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.

8. 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.