Heat maps for APEX applications usage

Y

You have a lot of valuable insights in APEX logs and you most likely don't realize that. And that is because you don't have a tool to display this data in a meaningful way.

Some things you can extract from the apex_workspace_activity_log table:

• average or maximum times for each page for several actions (rendering, processing, AJAX calls...)
• number of requests, so you can identify highly used pages (or not used pages)
• number of users and sessions as a complementary metrics
• you could also extract browsers, IP addresses, buttons clicked, errors...

There are other things you can track.

I love heat maps. So I created this mini app, which will allow you to explore the data from apex_workspace_activity_log as a heat maps. I use whole month data and the T1..T30 columns represents dates in past, T1 as yesterday, T2 as day before yesterday... You just have to set the colors and thresholds to suits your needs.

What I created:

• simple page with IG to show data as heat map and allow easy extendability
• bunch of LOVs (and views) for the filters
• view to preprocess data from log based on the filters
• function to calculate color based on the value and setup table
• setup table to adjust colors and thresholds

All of this is on my Tracking repo and as always you can use it on your projects.

And there is an article dedicated to coloring IG.

The most challenging part was to calculate the color in between two colors to represent value in between two tresholds:

WITH t AS (
    SELECT /*+ MATERIALIZE */
        t.left_treshold,
        t.right_treshold,
        in_value            AS value,
        (in_value - t.left_treshold) / (t.right_treshold - t.left_treshold) AS value_perc,
        --
        TO_NUMBER(SUBSTR(t.left_color,  2, 2), 'XX') AS left_r,
        TO_NUMBER(SUBSTR(t.left_color,  4, 2), 'XX') AS left_g,
        TO_NUMBER(SUBSTR(t.left_color,  6, 2), 'XX') AS left_b,
        TO_NUMBER(SUBSTR(t.right_color, 2, 2), 'XX') AS right_r,
        TO_NUMBER(SUBSTR(t.right_color, 4, 2), 'XX') AS right_g,
        TO_NUMBER(SUBSTR(t.right_color, 6, 2), 'XX') AS right_b
    FROM (
        SELECT
            MIN(t.color_bg) KEEP (DENSE_RANK FIRST ORDER BY t.treshold DESC)    AS left_color,
            MIN(t.treshold) KEEP (DENSE_RANK FIRST ORDER BY t.treshold DESC)    AS left_treshold,
            MIN(r.color_bg) KEEP (DENSE_RANK FIRST ORDER BY r.treshold)         AS right_color,
            MIN(r.treshold) KEEP (DENSE_RANK FIRST ORDER BY r.treshold)         AS right_treshold
        FROM trc_lov_colors t
        JOIN trc_lov_colors r
            ON r.name       = t.name
        WHERE 1 = 1
            AND t.name      = in_name
            AND t.treshold  <= in_value
            AND r.treshold  > in_value
    ) t
)
SELECT
    TO_CHAR(GREATEST(LEAST(ROUND((t.right_r - t.left_r) * t.value_perc + t.left_r, 0), 255), 0), 'FM0X') ||
    TO_CHAR(GREATEST(LEAST(ROUND((t.right_g - t.left_g) * t.value_perc + t.left_g, 0), 255), 0), 'FM0X') ||
    TO_CHAR(GREATEST(LEAST(ROUND((t.right_b - t.left_b) * t.value_perc + t.left_b, 0), 255), 0), 'FM0X')
INTO out_color
FROM t;