Using the ADF DVT Radar Graph for comparing series – further analyzing ODTUG Kaleidoscope 2011 Session Schedule

I have always had a fascination for the Data Visualization capabilities of all the tools and technologies I have worked with. For example: I worked with Oracle Graphics 2.0, back in 1994, and liked it! Fast forwarding through the years – I am now wetting my appetite with ADF’s Data Visualization Tags (DVT) – an impressive array of graphs, gauges, charts and other ways of visualizing data.

One interesting type of graph that I have not actually used before, is the Radar Chart. It is an interesting type of visualization that can plot in one graph values for multiple series against multiple dimensions and make it easy to compare them (the series) with one another. Granted, you can do something similar with a multi-series line chart, yet the comparison is somewhat more pronounced in the radar graph.

See for example the next graph. It plots values for three series – each serie represents a region in the world. For each of the tracks at the ODTUG Kaleidoscope 2011 conference, the percentage of sessions in the track delivered by presenters from the region represented by a serie is plotted. Thus we can see that over 90% of the sessions in the BI and Oracle EPM track is delivered by presenters from the AMERICAS. The Fusion Middleware track has a completely different region-contribution pattern: over 60% of sessions in that track is from EMEA with only a little over 30% coming from the AMERICAS. The relative contributions from the AMERICAS and EMEA for the tracks APEX and Database Development seem fairly similar – both around 30% EMEA can close to 70% from the AMERICAS. The role of APAC is only fairly small at this conference – only contributing to the Fusion Middleware track.

This article will demonstrate how this Radar Graph is constructed, through a cleverly composed SQL Query and a small number of declarative steps.

The article will also introduce a second example of a Radar Graph. This one shows for each track (so we have four series, one for each track), for each of the Target Audience levels, the percentage of the track’s sessions at the Target Audience level. For example for the Fusion Middleware track, 43% of the sessions is for Novice/Beginner level with some 35% at the Intermediate level and only a very small percentage targeted at Advanced/Expert. The BI and Oracle EPM track in contrast has only a very small number of sessions for Novice/Beginner (although a fairly high percentage for All) and over 12% targeted at Advanced/Expert.

Developing the Radar Graph for ‘Contribution from Region per Track’

Create SQL query to retrieve the percentage of sessions in a track from each of the regions.

select track_label
,      target_audience
,      round(num_of_sessions/all_sessions_in_track,3) * 100 perc_sessions_for_audience
from (
select track_label
,      target_audience
,      num_of_sessions
,      sum(num_of_sessions) over (partition by track_label) as all_sessions_in_track
from (
select tr.track_label
,      s.target_audience
,      count(s.id) num_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)
group
by     tr.track_label, s.target_audience
))

Create read only ViewObject for this query

Add ViewObject to ApplicationModule’s Data Model

Create a new JSF page. Drag the the Collection for the new ViewObject to the page; drop as Graph.

Pick the Graph Type Radar.

Drag attribute TrackLabel to the X Axis field. Drag attributes PercSsssnFrmRgnInTrck (the numerical value to be plotted) and Region (the series selector) to the area Radar Lines. Set the Label for PercSsssnFrmRgnInTrck to <No Label>.

Press OK.

Run the page. Make some stylistic changes – such as the dimensions (width and height) of the chart – and set the titles. Refresh the page to see those changes.

Developing the Radar Graph for ‘Percentage Track Sessions per Target Audience Level’

Create SQL query to retrieve the percentage of sessions in a track from each of the regions.

select track_label
,      target_audience
,      round(num_of_sessions/all_sessions_in_track,3) * 100 perc_sessions_for_audience
from (
select track_label
,      target_audience
,      num_of_sessions
,      sum(num_of_sessions) over (partition by track_label) as all_sessions_in_track
from (
select tr.track_label
,      s.target_audience
,      count(s.id) num_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)
group
by     tr.track_label, s.target_audience
))

Create read only ViewObject for this query

Add ViewObject to ApplicationModule’s Data Model

Create a new JSF page. Drag the the Collection for the new ViewObject to the page; drop as Graph.

Pick the Graph Type Radar.

Drag attribute TrackLabel to the X Axis field. Drag attributes PercSsssnFrmRgnInTrck (the numerical value to be plotted) and Region (the series selector) to the area Radar Lines. Set the Label for PercSsssnFrmRgnInTrck to <No Label>.

Press OK.

Run the page. Make some stylistic changes – such as the dimensions (width and height) of the chart – and the fact that markers will be show:

Run the page to analyze the distribution of sessions over each of the target audience types for each track respectively:

Resources

Download JDeveloper 11gR2 application: ODTUG_Dashboarding_twoRadarGraphs.