7 Replies Latest reply on Mar 21, 2018 2:24 AM by Scott Wesley

    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

      Series Name in JET Charts

       

      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)) val
      from dual connect by level <= 12;
      
      create table data_budget as
      select adD_months(trunc(sysdate,'mm'),-rownum) mth, round(dbms_random.value(70,100)) val
      from 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

        • 1. Re: OracleJET data densification 5.1.3
          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) budget
          from data_sales s
          order 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, val
          from data_budget b
          right outer join mths
          on mths.mth = b.mth
          order by mth
          

           

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

          • 2. Re: OracleJET data densification 5.1.3
            Dimitri Gielis

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

             

            1 person found this helpful
            • 3. Re: OracleJET data densification 5.1.3
              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.

              • 4. Re: OracleJET data densification 5.1.3
                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
                 ,name
                from jet_demo s
                right outer join dts d 
                  on d.dt = trunc(s.dt)
                where d.dt >= date '2018-03-01'
                group by d.dt
                 ,name
                order 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.name
                from jet_demo s
                right outer join dts d
                  on d.dt = trunc(s.dt)
                  and d.name = s.name
                where d.dt >= date '2018-03-01'
                group by d.dt
                ,d.name
                order 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

                • 5. Re: OracleJET data densification 5.1.3
                  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.

                  • 6. Re: OracleJET data densification 5.1.3
                    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)

                    1.png

                    • 7. Re: OracleJET data densification 5.1.3 (thru 18.1)
                      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.