OracleJET data densification 5.1.3 (thru 18.1)

Scott Wesley

G'day all,

After reading Hilary's comments in these posts, I was a little surprised to find us struggling with data densification behaviour in 5.1.3.

(I was looking forward to not including outer joins to small sets of months)

Oracle APEX 5.1 stacked bar chart

The release notes in both 5.1.3 and 5.1.4 state the following

8.1.4 JET Chart Data Densification

Oracle JET requires that each series of a multi-series chart contains a data point for each label that is represented on the x-axis. Otherwise, the multi-series chart may not render correctly by Oracle JET. Prior to release 5.1.3, the user was required to handle the densification of their data in their chart SQL query to ensure that each series had the same number of data points, even if they were null or zero. Oracle Application Express release 5.1.3 now includes support to automatically fill in gaps, injecting missing data points for multi-series charts.

I created the following tables, where sales is complete for the year, and budget only has data for the last half of the calendar year (lucky it's january)

create table data_sales as

select adD_months(trunc(sysdate,'mm'),-rownum) mth, round(dbms_random.value(70,100)) valfrom dual connect by level <= 12;create table data_budget asselect adD_months(trunc(sysdate,'mm'),-rownum) mth, round(dbms_random.value(70,100)) valfrom dual connect by level <= 6;select * from data_budget

Then I create region

Region Type: Chart

Type: Line

Time Axis Type: Enabled

And add the SQL for the two series

Here is the result in 5.1.4 - as expected

And in 5.1.3, the months in the second series have been erroneously transposed.

Reversing the series in 5.1.4 is fine, but in 5.1.3 it's... demon spawn.

We certainly need to get our ordering right.

Is the fix in 5.1.3 or 5.1.4?

Or have I missed something.

Cheers

edit: changed subject to include 18.1

Scott Wesley

Future readers, we can solve this by using a scalar subquery, iff there are guaranteed records in data_sales for entire year:

Each series Type: Region Source

select s.mth, s.val  ,(select b.val    from  data_budget b    where s.mth = b.mth) budgetfrom data_sales sorder by mth

And respective series label chooses val/budget

Or here's an example that ensures always rolling 12 months for the series

with mths as  (select add_months(trunc(sysdate,'mm'),-rownum) mth  from dual connect by level<=12)select mths.mth, valfrom data_budget bright outer join mthson mths.mth = b.mthorder by mth

So workarounds exists, but documentation doesn't seem to match behaviour.

Dimitri Gielis

There's a feature in #orclapex 18.1 in JET charts: "Fill Gaps in Chart Data - Yes/No (defaults to Yes)"

Scott Wesley

Now that I'm running examples with actual data on 5.1.4, the problem seems worse

I played with the Time Axis Type plotting data points for half of March (current month):

Enabled - produces "invalid data", for both charts. The second is not densified within the SQL.

Automatic - still does not produce as expected

Disabled - using formatted date string, same output as Automatic.

In 5.1.3, this would be the only workaround to producing the expected chart. The only difference is now we don't have to densify the SQL ourselves.

Edit: 5.1.4 has impacted the ordering of existing JET charts that use the to_char(date_col) as dt_fmt workaround, so now they're alphabetical, regardless of query instructions.

So I'm now baulking at 5.1.4.

So we won't be going to 5.1.4.

Scott Wesley

What an interesting exercise this was, pulling together a demo across 5.1.3, 5.14, and 18.1.

And I think I learnt something right at the end about what it means to densify data for multi-series charts.

I've defined charts that use densified (date only) vs a simpler query, without the outer join; and a set for each relevant time axis option - disabled (with string label), enabled, automatic.

There are some bar graphs at the bottom to explore ordering.

All densified statements only generate a date range

with dts as (select trunc(date '2018-03-20')-rownum+1 dt from dual connect by level <= 31)select null, d.dt dy ,count(s.rowid) c ,to_char(d.dt,'DD Mon') dt_fmt ,namefrom jet_demo sright outer join dts d   on d.dt = trunc(s.dt)where d.dt >= date '2018-03-01'group by d.dt ,nameorder by d.dt

The only JET line chart that works as expected in 5.1.3 is the "fully dense" region, where I've generated data for not only the dates, but cross joining with all expected series

with dts as (select dt, name from (    select trunc(date '2018-03-31')-rownum+1 dt    from dual    connect by level <= 31)    cross join (select distinct name from jet_demo))select null, d.dt dy,count(s.rowid) c,to_char(d.dt,'DD Mon') dt_fmt,d.namefrom jet_demo sright outer join dts d  on d.dt = trunc(s.dt)  and d.name = s.namewhere d.dt >= date '2018-03-01'group by d.dt,d.nameorder by d.dt

Here is a sample in 5.1.4, as apex.oracle.com currently stands.

https://apex.oracle.com/pls/apex/f?p=32532:14:

It's great that the densified - disabled chart works in 5.1.4

as 5.1.3 seems to justify everything to the left. Same with the densified - enabled.

Note, the charts sometimes don't honour the date format selected in the x-axis.

Also note the ordering in this region, which is not a problem in 5.1.3, or 18.1

So here is the same page in 18.1 EA

https://apexea.oracle.com/pls/apex/f?p=32532:14

Quite a few of the charts still have problems, but the semi-densified SQL is rendered better.

However, here is the chart after it was freshly imported from 5.1.4, back to being an issue

I had to set this to No, save, then back to Yes again for it to behave.

This freshly imported app still exhibits this

https://apexea.oracle.com/pls/apex/f?p=1542:14

So perhaps the underlying meta-data may need to be treated better during import/instance upgrade/app upgrade?

Scott

Scott Wesley

I meant to explicitly mention that when I remove the date filter, so the dates bleed from late Feb, across to late March, then rendering is bad again

--where d.dt >= date '2018-03-01'

This sample removes the filter from the densified SQL

https://apexea.oracle.com/pls/apex/f?p=1536:14

Knowing that most of these examples so far can be solved by fully densifying the data helps, but some of our reports to tend to roll across date boundaries like this, or have generally scattered information across the y-axis, so we seem to see a lot of the weirdness - which is not fixed by full densifying the data in 18.1

Unless of course I have a query wrong, or attributes to adjust.

Oleh Tyshchenko

I'm really sorry but I found this topic a little bit hard to follow. Too many screenshots of charts based on unknown data and this mean I can't play with them. What is the problem (if there is no problem and its just series of experiments please ignore this message) in couple of words? Did you try Time Axis Type = Mixed Frequency? I've just read in item-level help that its for scatter and bubble charts only (maybe documentation bug sine JET JSDoc does not have such restrictions) but it's do work for line as well (tested with 5.1.4 and 18.1ea2)

Scott Wesley

Thanks Oleh, time for a summary of what I've found so far.

a) 5.1.3 multi-series densification needs to include series, not just date (x-axis)

b) still need to densify by date in 18.1

c) multi-series x-axis ordering had hiccup in 5.1.4

d) charts across rolling months may still be a problem

The sample pages I produced have the expected outcome at the top (as an anychart), and is probably the best way to view the differences/problems.

I have the same set of charts, varying by time axis type.

At the bottom I have a report of the data. Joel kindly asked for an example (on Twitter), so that's what I produced.

I've just made the data downloadable from the apex.oracle.com example.

I've also had mixed results with Mixed Frequency, and I wanted to try stay as documented.