Using ADF 11gR2 DVT component Pivot Table for an on-line analysis of the ODTUG Kaleidoscope session catalog

ADF comes with a rich collection of component that allow us to visualize and analyze data in ways that previously were only available in fancy OLAP and other BI tools. Now, our own custom developed ADF applications can offer those same fancy capabilities using ADF DVT. Note that Oracle’s BI tools – such as OBI EE – make use of those same components.

This article demonstrates the use of the Pivot Table component – as it is currently shipped in ADF 11gR2. This component presents data in initially very condensed, highly aggregated form and allows the user to ‘slice and dice’ and drill down and aggregate along various dimensions.

The Pivot Table is used in this case to analyze the data for the sessions scheduled for the ODTUG Kaleidoscope 2011 conference, later this month, in Long Beach, California. The article will demonstrate that just a few, declarative steps and about 10 minutes of your time are quite enough to include rich analytical capabilities in an ADF application.

The initial Pivot Table shown to the user looks as follows:

The data model that contains the session data is shown below. A bunch of normalized tables hold the data about sessions, their topics and indirectly their track, their slot and indirectly the day of conference and their presenter and indirectly the country from where the session originated.

The crucial step in the development of the data bound pivot table is the creation of the (read only) ViewObject that provides the data to analyze, slice and dice.

Since the pivot table should present the number of sessions, group by dimensions day and slot, track and topic as well as target_audience, the query should reflect (and group by) each of these dimensions:

select tr.track_label
,      t.topic
,      to_char( sl.session_date, 'DAY') day
,      sl.session_date
,      sl.start_time ||'-'||sl.end_time timeslot
, to_date( to_char(session_date,'DD-MM-YYYY')||' '||start_time, 'DD-MM-YYYY HH:MI AM') start_datetime
,      s.target_audience
,      count(s.id) number_of_sessions
from   sessions s
       join
	   topics t
	   on (s.topic_id = t.topic_id)
	   join
	   tracks tr
	   on (t.track_id = tr.track_id)
	   join
	   presenters p
	   on (p.presenter_id = s.presenter_id)
	   join countries c
	   on (p.country_id = c.id)
	   join
	   session_slots sl
	   on (sl.session_id = s.slot_id)
group
by     tr.track_label
,      t.topic
,      to_char( sl.session_date, 'DAY')
,      sl.session_date
,      sl.start_time ||'-'||sl.end_time
, to_date( to_char(session_date,'DD-MM-YYYY')||' '||start_time, 'DD-MM-YYYY HH:MI AM')
,      s.target_audience
order
by     start_datetime

The Model project contains a ViewObject based on this query. The ViewObject has been added to the Application Module and thereby becomes available in the Data Control palette:

Simply drag the PivotSessionsView1 collection to a JSF page and drop it as a Pivot Table.

The Pivot Table configuration wizard appears.

Drag NumberOfSessions to the table body (cell area). Draf Day and Timeslot to the column edge and Tracklabel, Topic and TargetAudience to the row edge.

Press Next. Accept the defaults on the Attribute Labels step. Press Next.

Click on radio group Insert Drilling. Enable all three Drill Paths. Click on Next.

The page to Configure Aggregation appears. Define an Data Aggregation of attribute NumberOfSessions with Function Sum. Open the tab Category Totals. Accept the three drill related totals. Add Category Totals for Day and TrackLabel, both with Function Sum. Specify the Total Label for both: # sessions.

Press Next. We can configure sorting. Especially to make sure that the Days are listed in the right order (Monday through Thursday), specify a Category Sort for Attribute Day based on the SessionDate (of type Date) Sort Attribute.

Press Finish to complete the configuration of the Pivot Table.

Complete the web page and run the application

Add a title to the page for completeness sake and run the page.

The following screenshots are all taken with the Pivot Table manipulated at run time in various ways:

Expand the Fusion Middleware track:

Inspect the (number of) ADF sessions on Tuesday:

Collapse the FMW track and inspect the numbers of sessions for each of the tracks in each of the slots on Tuesday:

By pivoting – moving dimensions from row to column – we can find a different perspective to look at the data. In the next screenshot has the Target Audience dimension been moved to the column edge, as top dog. Hence we see for the Novice/Beginner target audience category the number of sessions per track per time slot. Apparently, the FMW track offers the highest number of sessions for Novice/Beginner attendees (19 in total).

Another pivot action brings all the dimensions to the column edge. This allows for a detailed analysis of the sessions per topic, per target audience, per day and even per timeslot. So on Tuesday, there will be two sessions on Advanced/Expert level in APEX Plug-ins and Dynamic Actions. Cool!

Resources:

Download JDeveloper 11gR2 application with DDL/SQL scripts inside: ODTUG_DashboardingPivotTable.