The Art of Pivoting

One of the nice features of OBIEE is being able to view your data from various angles. A table of data is prefered by some people, but for others they would prefer to see totals by Month, then drill in where they see issues.

One of the mistakes I often see is that multiple reports are prepared, showing the same data, but in different ways. A Table on one, a pivot by Month for another, a pivot by region on another.  Worst still they put these reports on the same dashboard.

The best practice would be to create one report, then create various views that can be placed on dashboards, or even create multiple compound layouts.  Obviously Pivots are the best way to slice and dice the data, and you can go to town with these, creating as many as you like on one dataset (table).

When creating Pivot tables in 10g it was very slow and often impossible to remove columns from the left panel using the UI. The best way to do this was by updating the report XML, removing the columns referenced in the relevent section.

While the XML editing is also possible and useful in 11g, the UI is more helpful, allowing whole tables’ columns to be moved out, and of course the remove section is now below the columns making the process smoother.

Here are the steps, using a small example to demonstrate the point, using both methods:

Starting with a table view

Add a pivot

Drag the table name down to the excluded section

(drag the table name section)

The XML method involves selecting the Advanced tab

Locate the Pivot table section, and note the column ids (they are now much longer in 11g)

remove the columns you don’t want, and apply the XML

And the result is a pivot table with just the columns you want in.

Til Next time

@ddi