Cal Answers Analysis Training Part II Viewing Analysis Results in

Cal Answers

Analysis Training

Part II

Viewing Analysis Results

in OBIEE

University of California, Berkeley

March 2012

Cal Answers Analysis Training Part II – Viewing Analysis Results in OBIEE

March, 2012 Page 1

Table of Contents

Table of Contents .......................................................................................................................................... 1

Overview ....................................................................................................................................................... 2

Remember How to Create an Analysis? ....................................................................................................... 2

Create a Sample Query to Use in This Class .......................................................................................... 3

Compound Layout ......................................................................................................................................... 3

Default Results View ............................................................................................................................. 3

Working With Results Views ................................................................................................................. 4

Editing Existing Views in a Compound Layout .................................................................................. 4

Adding New Views to a Compound Layout ....................................................................................... 4

Results Views ................................................................................................................................................ 5

Title Views ............................................................................................................................................. 5

Table Views ........................................................................................................................................... 5

Graph Views .......................................................................................................................................... 6

Chart Types, Subtypes, Styles, and Effects ....................................................................................... 7

Graph Properties ............................................................................................................................... 7

Graph Layout ..................................................................................................................................... 8

Pivot Table Views .................................................................................................................................. 8

Pivot Table Editor Toolbar ................................................................................................................ 9

Excluded, Prompts, Sections, Rows, and Columns ......................................................................... 10

Measures ......................................................................................................................................... 10

Filters Views ........................................................................................................................................ 11

Static Text Views ................................................................................................................................. 11

Other Views ......................................................................................................................................... 11

Practice Queries and Result Views.............................................................................................................. 12

Degree Practice Query ........................................................................................................................ 12

Census Count Practice Query .............................................................................................................. 13

Cohort Grad Rates Practice Query ...................................................................................................... 14

To report any problems with the Cal Answers Portal, Reports, or Dashboards:

 Send an e-mail to calanswers-help@berkeley.edu

• Call the Help Desk at 642-8500, Monday – Friday, 8:00am – 5:00pm

Cal Answers Analysis Training Part II – Viewing Analysis Results in OBIEE

March, 2012 Page 2

Overview

Cal Answers is the new reporting environment for UC Berkeley’s Enterprise Data Warehouse (EDW). It

currently uses Oracle Business Intelligence Enterprise Edition (OBIEE) version 11g software.

This three-part training course will deal with using OBIEE, including how to write your own queries and

create your own dashboard reports. Specifically, the sessions will cover:

1. Creating Analyses in OBIEE

2. Viewing Analysis Results in OBIEE

3. Advanced OBIEE – Dashboard Reports

Remember How to Create an Analysis?

Clicking either “New > Analysis” or “Create… > Analysis,” and then choosing a data subject area will start

the OBIEE ad hoc query tool:

The various fields available in a given subject area are stored in an expandable/collapsible tree. Click the

plus signs ( ) to expand a branch, and the minus signs ( ) to collapse it. To bring a column into your

query, either double-click it or drag it over into the “Selected Columns” area.

Column/Filter Selection Pane

Query Columns and Filters

Build Query

View/Format Results

Define Prompts

Do Fancy Stuff

Cal Answers Analysis Training Part II – Viewing Analysis Results in OBIEE

March, 2012 Page 3

Create a Sample Query to Use in This Class

For this class, we’ll be working with a simple query that shows degree recipient headcounts by

undergrad/grad status and degree level since 2008-09. Using the “Student Counts – Degrees” subject

area, create the following query and then save it to your “My Folders” area:

You should get 20 rows in your results. Please make sure you save this query!

Compound Layout

As we discussed in the last class, you can view the results of

your query by clicking on the “Results” tab.

Default Results View

The default results view is a

table with a blank title above

it, combined in OBIEE’s

“Compound Layout,” as

shown to the right.

As you can see, you have a

blank title (if you save the

query, the title defaults to the

name), followed by a simple

table that suppresses

duplicate values. The

compound view lets you show

a single view of results, or it

allows you to combine two or

Cal Answers Analysis Training Part II – Viewing Analysis Results in OBIEE

March, 2012 Page 4

more result views. So, for instance, you could create a compound layout that had a title, a pivot table,

and a chart of the data from your query. If you’re thinking that that sounds a bit like a dashboard

report, that’s exactly what it is. All of the dashboard reports are actually just analysis result views using

compound layouts.

Working With Results Views

You can make several kinds of changes to any of the views in a compound layout, including editing

existing views and adding new ones.

Editing Existing Views in a Compound Layout

To edit an existing view in a compound layout, use the three buttons that appear in each view’s title bar:

 The first button ( ) gives you access to a “Format Container” dialog box, where you can

change things like alignment, colors, and border styles.

 The second button ( ) takes you to the editor for that type of view. Each view (title, table,

pivot table, etc.) has its own editor with its own options. We’ll be covering several of these later

in this document.

 And the third button ( ) removes a given view from the compound layout.

You can also access the editor for a given view by using the toolbar in

the “Views” pane that appears at the bottom left of the OBIEE

screen. This will list all of the views that you’ve already created for a

given analysis. (Notice that, by default, the “Title” and “Table” views

already exist.) Selecting any of the options in this list and then

clicking the pencil toolbar button will open the appropriate view editor.

Note that just because a view appears in this list (indicating that it already exists), that view doesn’t

necessarily appear in the compound layout. You have to add it specifically, which we’ll cover below.

Just removing a view from the compound layout, by the way, does not delete it entirely. As long as the

view still appears in this list, it still exists, regardless of whether it actually appears in the compound

layout. You can completely delete a view by using the ( ) button on the “Views” toolbar.

Adding New Views to a Compound Layout

To add a new view directly to a compound layout, use the “New View” dropdown button ( ) on the

“Results” toolbar at the top of the screen. To create a new view without immediately adding it to the

compound layout, use the “New View” dropdown button ( ) in the “Views” pane toolbar at the

bottom left of the screen.

The “Results” toolbar at the top of the

screen, by the way, has enough options to keep you busy for a while. Just hover your mouse over each

button to see what it does, including: printing, exporting, scheduling, viewing as a dashboard, setting

lots of options, changing properties, etc.

Cal Answers Analysis Training Part II – Viewing Analysis Results in OBIEE

March, 2012 Page 5

Results Views

In this section, we’ll cover several of the more popular results views—Title, Table, Graph, Pivot Table,

Filters, and Static Text—and how to use them. In the next class, we’ll cover a few more results views,

such as Column Selectors and View Selectors.

Title Views

The Compound Layout includes a Title view by default and, if you save your query, the name of the

query will appear in that title.

The Title editor has only a few options:

 Title – The main, large-font title; by

default, this is the saved query name.

 Logo – The URL for an image to display to

the left of the title; IST has to do this for

you.

 Subtitle – The secondary, smaller-font

title.

 Started Time – An automatically

generated start date and/or time for the

report.

 Help URL – The URL for a help document;

IST has to do this for you.

The toolbar at the top also gives you some additional printing, copying, and formatting options. To

return to the compound layout, click the “Done” button. The “Revert” button will simply undo any

changes that you made in the editor. Note that OBIEE adds a blue bar beneath the title.

Table Views

The other view that OBIEE includes in the compound layout by default is a Table view.

Table views yield reasonably

fancy tables, with lots of options

in the editor.

In the “Layout” pane of the

editor, you can add totals and

subtotals, change headings for

fields, exclude fields, and much

more. To make changes, you can

drag fields into different sections

of this pane, and use the

“Totals” , “Properties” , and

“Options” buttons.

Cal Answers Analysis Training Part II – Viewing Analysis Results in OBIEE

March, 2012 Page 6

The toolbar at the top of the editor includes

several of your old favorites, but has one button

in particular that you’ll find useful when working

with tables. Clicking this button will open the

“Table Properties” dialog box, where you can

choose:

 Where paging controls appear;

 How many rows to show per page;

 What sort of headings to display;

 Whether to use green bar styling; and

 Whether to repeat cell values.

In addition, this dialog lets you work with a

couple of advanced features, Master-Detail

events and Write Back, that IST has not currently

enabled.

Graph Views

Graphs are one of the more complex views, with a very large number of options, as you can see below:

Cal Answers Analysis Training Part II – Viewing Analysis Results in OBIEE

March, 2012 Page 7

Chart Types, Subtypes, Styles, and Effects

The Graph Editor toolbar has lots of the standard

buttons, plus some specific to graphs, such as the four

buttons on the far right side of the toolbar.

 The “Bar” button lets you choose from 10 different kinds of charts: bar, line, area, pie, line-bar,

time series line, pareto, scatter, bubble, and radar. (Note: If you aren’t sure what kind of chart

to use, http://www.perceptualedge.com/articles/misc/Graph_Selection_Matrix.pdf is a very

helpful reference.)

 The second button (which says, “Default (Vertical)” here) lets you choose a subtype for selected

graph type. With a bar graph, for instance, you can have vertical, horizontal, stacked vertical, or

stacked horizontal subtypes. You’ll see different options here, depending on the graph type.

 The third button (“Default” here) determines the appearance of the series on your graph. For a

bar graph, for instance, the bars can appear as rectangles, triangles, cylinders, diamonds,

gradient rectangles, or pattern-filled rectangles. This button’s options also depend on the type

of graph that you’ve selected.

 Finally, the “2D” button lets you choose whether the chart should appear in 2-D or 3-D (there’s

almost no situation where a 3-D chart improves readability, so the default, 2-D, should be your

choice).

Graph Properties

The “Graph Properties” toolbar button ( ) opens a

dialog box that lets you change some additional things

about the appearance of the graph:

 General – size, legend location, zooming, and

animation;

 Style – display effects, colors, gridlines, and

borders;

 Scale – axis minimums and maximums, tick

marks, and axis scales;

 Titles and Labels – graph and axis titles, and

legend, axis, and data labels.

Cal Answers Analysis Training Part II – Viewing Analysis Results in OBIEE

March, 2012 Page 8

Graph Layout

Finally, the graph

“Layout” pane at

the bottom of the

screen lets you

create prompts

and sections, add

sliders, and

decide what data

values should

appear as the

categories and

series of your

graph.

To set categories and series, you simply drag each of the fields into the various areas of this pane; note

that you can exclude fields that you don’t need. On the right side, a small sample of the chart shows you

what your changes look like.

One final option for graphs in this pane is the “Display as Slider” box .

When you put a field, such as a date, in the “Sections” area of your graph, and then check this box, your

graph will appear with a slider:

This will then display your graph as an animation that changes depending on the date value selected on

the slider. You can use the double-arrow buttons to move one value at a time on the slider, or you can

click the “Play” button to have it automatically advance every 2 seconds.

Pivot Table Views

Pivot tables are probably the view that you’ll wind up using the most, since we often report data over

time, and pivot tables are good at doing that. These look and work a lot like Excel pivot tables.

The pivot table editor, shown on the next page, has a lot going on. There’s a toolbar at the top, six

different table sections (pivot table prompts, sections, rows, columns, measures, and excluded) that can

contain the fields in your query, and a view showing the results of your design. Note that most of the

table sections have their own set of toolbar buttons, too.

Also note that sorting is handled in the results display area, rather than in the

“Layout” pane. If you move your mouse over the actual pivot table, you’ll see two

arrowheads appear in each row and column header. You can click on these to sort

values in a given row or column.

Cal Answers Analysis Training Part II – Viewing Analysis Results in OBIEE

March, 2012 Page 9

Pivot Table Editor Toolbar

The pivot table editor toolbar has lots of the standard

buttons, but includes two that you’ll use specifically for pivot

tables.

The first of these buttons, “Pivot Table View Properties,”

( ) shows the dialog box to the right. It lets you set paging

options, show headings, apply alternating row formatting in

your pivot table, and define master-detail events (an

advanced drilling feature that you’re very unlikely to use).

Cal Answers Analysis Training Part II – Viewing Analysis Results in OBIEE

March, 2012 Page 10

The “Graph Pivoted Results” button ( ) will place a chart next to or in place of the pivot table. You

don’t have as much control over these pivot charts as you would with a Chart view, so use them

carefully.

Excluded, Prompts, Sections, Rows, and Columns

The pivot table editor mostly consists of areas into which you can drag fields in order to display them in

different ways inside a pivot table.

The “Excluded” section, which appears at the bottom of the “Layout” pane, is for any field in your query

that you don’t want to see in your pivot table. If you drag a field to that area, it will no longer appear in

the pivot.

OBIEE will remove from the pivot table any fields that

you put in the “Prompts” area and combine them into

a dropdown that appears just above the pivot table.

In the example shown to the right, I’ve moved the

“Ungrad_Grad_Cd” field to the “Prompts” area.

The “Sections” area of the editor is for fields whose

values you want to use to break the pivot table into

sections. The example shown to the right moves the

“Ungrad_Grad_Cd” field into that area. You can

compare this to the pivot table shown on the previous

page.

The “Rows” and “Columns” areas allow you to define

the pivot table’s rows and columns, as you might have

guessed. You can put multiple fields in both areas.

Finally, notice that each area and each element within

each area have their own little toolbars, with up to

three buttons (although there are four different

buttons all together):

 - The “Properties” button appears for each area. It lets you change formatting, define how

headers look, and show or hide certain kinds of content.

 - The “More Options” button, which accompanies each field in an area, pops up a context-

specific menu, which could contain options allowing you to format headings and values, show or

hide items, create new calculated items, duplicate layers, or remove columns.

 - The “Totals” button lets you add, remove, and format a variety of grand and subtotals.

Measures

The “Measures” area is a little different from the others. It’s where you put

the value field(s) that you want to appear in the pivot table. Each field in

“Measures” has only one toolbar button, for “More Options.” (The popup

menu for it appears to the right.)

Cal Answers Analysis Training Part II – Viewing Analysis Results in OBIEE

March, 2012 Page 11

With this menu, you can format headings and values, as well as duplicate layers and remove columns,

like with the other “More Options” buttons. The other three menu items, however, are unique to this

area.

One of these options, “Display as Running Sum,” seems like it would be really useful, but may not work

the way you think it should, so be careful when using it. One of the other two options, “Aggregation

Rule,” lets you choose if you want to sum, count, average, etc. the pivot table values. This might

occasionally be useful, but you’d usually want to do that in the query itself so that the database handles

it, rather than bringing back lots of data and doing it in OBIEE.

Finally, the “Show Data As”

option is one you’ll use a lot.

The default is to show data as

values, but you can also make

it a “Percent of” based on

many different possibilities,

such as the row, column, or

layer. The “Index of” option

does the same thing, except

that it returns a decimal value

instead of a percentage (i.e., 0.22 instead of 22%).

Filters Views

A filter view is probably more useful in

a dashboard report rather than just in

a query. It’s just a section of text that

shows the values used to filter this particular query, as you can see in this screenshot. Using the view’s

toolbar buttons, you can format the text in a variety of ways.

Static Text Views

Finally, the static text view lets you display formatted

text along with your query results. No matter what

results you get from your query, the static text will

always appear the same.

Using the various buttons in the static text view

editor, you can change colors, fonts, sizes, and styles.

If you check the “Contains HTML Markup” box, you

can even include HTML tags in your static text.

Other Views

There are quite a few other results views available for your use. We’ll cover at least a couple more

(Column Selector and View Selector) in the subsequent class, but will leave some of the more obscure

ones for your own research.

Cal Answers Analysis Training Part II – Viewing Analysis Results in OBIEE

March, 2012 Page 12

Practice Queries and Result Views

Degree Practice Query

For the Department of Chemistry, create a query and compound layout that look as much like the

following as possible (make sure you get the correct answers, too). Save your query when you’re done,

then try changing it so that it shows a college/school of your choice.

Cal Answers Analysis Training Part II – Viewing Analysis Results in OBIEE

March, 2012 Page 13

Census Count Practice Query

For the College of Environmental Design, create a query and compound layout that look like this:

When you’re done, save the query, then change it so that it shows results for the college/school of your

choice.

Cal Answers Analysis Training Part II – Viewing Analysis Results in OBIEE

March, 2012 Page 14

Cohort Grad Rates Practice Query

Finally, here’s an existing dashboard report, using the “Student Counts - Undergrad Cohorts” subject

area. Can you duplicate this pivot table?