Forum Stats

  • 3,814,387 Users
  • 2,258,869 Discussions
  • 7,892,695 Comments

Discussions

OracleJET data densification 5.1.3 (thru 18.1)

Scott Wesley
Scott Wesley Member Posts: 6,181 Gold Crown
edited Mar 20, 2018 10:24PM in APEX Discussions

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

pastedImage_7.png

Then I create region

Region Type: Chart

Type: Line

Time Axis Type: Enabled

And add the SQL for the two series

pastedImage_10.png

Here is the result in 5.1.4 - as expected

pastedImage_6.png

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

pastedImage_5.png

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

pastedImage_8.png

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

Tagged:

Answers

  • Scott Wesley
    Scott Wesley Member Posts: 6,181 Gold Crown
    edited Jan 18, 2018 3:38AM

    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
    Dimitri Gielis Member Posts: 1,958 Bronze Trophy
    edited Mar 15, 2018 4:46AM

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

    Scott Wesley
  • Scott Wesley
    Scott Wesley Member Posts: 6,181 Gold Crown
    edited Mar 18, 2018 10:25PM

    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

    pastedImage_0.png

    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.

    pastedImage_6.png

    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.

    pastedImage_1.png

    So I'm now baulking at 5.1.4.

    So we won't be going to 5.1.4.

  • Scott Wesley
    Scott Wesley Member Posts: 6,181 Gold Crown
    edited Mar 19, 2018 11:56PM

    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

    pastedImage_6.png

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

    pastedImage_1.png

    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

    pastedImage_0.png

    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

    pastedImage_7.png

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

    pastedImage_8.png

    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
    Scott Wesley Member Posts: 6,181 Gold Crown
    edited Mar 20, 2018 12:11AM

    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

    pastedImage_0.png

    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

    pastedImage_6.png

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

  • Oleh Tyshchenko
    Oleh Tyshchenko Member Posts: 716 Gold Trophy
    edited Mar 20, 2018 11:14AM

    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)

    1.png

  • Scott Wesley
    Scott Wesley Member Posts: 6,181 Gold Crown
    edited Mar 20, 2018 10:24PM

    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.

This discussion has been closed.