Chapter 6. Pivoting Through Data

This chapter comes back to Oracle Discoverer, explaining how you can reorganize data on a worksheet for more effective analysis. In particular, you will learn to explore data relationships with pivoting.

Generally speaking, pivoting is a tool that allows you to look at the data from a different angle, rearranging worksheet items to meet your needs. For example, you may need to explore the sales by product or product category, rearranging the data so you can instantly see the sales figures for each product or category. In Discoverer, you can easily accomplish a pivoting operation using a drag and drop technique.

In this chapter, you will look at an example of pivoting and learn how to do the following:

• Create a crosstab worksheet in Discoverer Plus

• Rearrange data on a crosstab worksheet

• Pivot worksheet items using the drag-and-drop feature

• Create and use calculations

Making database data available for use in Discoverer

Before going any further, you need to create...

Making database data available for use in Discoverer

Before going any further, you need to create some metadata objects required to make the underlying database objects you're working with available for use in Discoverer. If you recall from Chapter 4, Analyzing Data and Creating Reports, the first step is to create an EUL (End User Layer) that contains metadata to access the database data and is designed to isolate you from database complexity.

In Chapter 4, you already created an EUL upon the usr/usr database schema. However, this underlying schema was modified in Chapter 5, Warehousing for Analysis and Reporting. So, you need to create a new EUL upon that schema. Since the process of creating an EUL was described in detail in Chapter 5, the following are only general steps:

1. 1. Launch Discoverer Administrator.

2. 2. In the dialog Connect to Oracle Business Intelligence Discoverer Administrator, enter the usr/usr username/password pair and the service name of the underlying database.

3. 3. If you...

Creating a crosstab worksheet in Discoverer Plus

Now you can launch Discoverer Plus and connect to it as the usr/usr database user, specifying the usr EUL. As you already know, to launch Discoverer Plus, you can point your browser to the following URL:

http://yourhostname:7777/discoverer/plus

After Discoverer Plus IDE is loaded, the first screen you should see is the Workbook Wizard dialog. You should remember this wizard from the example in Chapter 4. The major difference from that example, though, is that this time you're creating a crosstab worksheet, not a table worksheet. The following steps will walk you through the process of creating a crosstab worksheet:

1. 1. On the first screen of the wizard, select the Crosstab radio button, leaving all the other settings at their default:

   Click Next to continue.

1. 2. On the Select Items screen of the wizard, you have to select the items you want to include in the workbook, moving them from the Available pane to the Selected pane.

   • So, expand the...

Pivoting worksheet items

Obviously, the next step should be to populate the worksheet with data, so that you can then play with pivoting. Thinking about the data you would like to see on the worksheet, you most likely would like to see the dollar amount of operations performed during a certain period of time, with the ability to examine that data by salesperson, product, and/or product category. So, the first thing to take care of is to create a calculation determining this amount.

Creating a calculation

The product of the quantity and the unit price of the item in the order details gives you the dollar amount of the operation for that item—and, when summed up over the entire order for each item, it gives you the order total. Summing over all the orders gives you the total sales. Often, though, you need to aggregate sales figures across a certain time period at different levels.

Turning back to our example, let's create a calculation that will calculate the product of the Quantity and the...

Summary

In this chapter, you looked at pivoting. You saw how easy it is to change a crosstab worksheet's layout with the help of the drag-and-drop feature of Discoverer Plus. The ability to pivot through data enables you to explore data relationships more efficiently, thus allowing for more effective analysis.

Continuing with the topic of reorganizing worksheet data for effective analysis, the next chapter will cover how you might drill data up and down.