Breakdown: Input Form Usage for Comparison and Dynamic visuals

Taylor
Taylor Member Posts: 9 Type 1
in Blogs

A few people have been curious about how I implemented the use of Input fields within my GoodViz-ESG submission so I figured it may prove useful if I gave a breakdown!

My Targets

What I wanted to achieve was a more dynamic user experience, whereby the user could alter the content displayed within a subset of visualisation objects as opposed to making global selections with conventional filtering.

The main things I wanted to accomplish were:

  1. A comparison of user input values vs the top 100 companies for specific measures
  2. Dynamically changing visualisations

The Challenges

  1. Astrato's Input Form at the time only allowed for records to be inserted into a given table and these records needed to be entered as free text.
  2. There was also no variable support

Comments

  • Taylor
    Taylor Member Posts: 9 Type 1

    Solution 1: Comparisons

    The first thing I knew I needed to start off with were some tables to capture the input values. I wanted to keep my user input values separate from the original data so I created a simple table with an auto incrementing key field:

    CREATE OR REPLACE TABLE WB_COMPANY (
  WB_ID int autoincrement start 1 increment 1 not null
  ,WB_COMPANYNAME VARCHAR(16777216)
);

    After inserting a couple of test records I can see the sequence is working as expected, time to move on!

    +-------+----------------+
| WB_ID | WB_COMPANYNAME |
+-------+----------------+
|     1 | TAYLOR PLC     |
|     2 | ASTRATO        |
+-------+----------------+

    In my case, there were only a handful of specific ESG measures that I was interested in for comparisons so following the same approach I constructed a few more tables:

    --- ENERGY
CREATE OR REPLACE TABLE WB_ENERGY_MEASURE (
    WB_ID int autoincrement start 1 increment 1 not null
    ,WB_MEASUREVALUE FLOAT
);

--- WATER
CREATE OR REPLACE TABLE WB_WATER_MEASURE (
    WB_ID int autoincrement start 1 increment 1 not null
    ,WB_MEASUREVALUE FLOAT
);

--- WASTE
CREATE OR REPLACE TABLE WB_WASTE_MEASURE (
    WB_ID int autoincrement start 1 increment 1 not null
    ,WB_MEASUREVALUE FLOAT
);

--- ENVIRONMENTAL
CREATE OR REPLACE TABLE WB_ENV_MEASURE (
    WB_ID int autoincrement start 1 increment 1 not null
    ,WB_MEASUREVALUE FLOAT
);

--- GHG
CREATE OR REPLACE TABLE WB_GHG_MEASURE (
    WB_ID int autoincrement start 1 increment 1 not null
    ,WB_MEASUREVALUE FLOAT
);

--- E_PILLAR
CREATE OR REPLACE TABLE WB_E_PILLAR_MEASURE (
    WB_ID int autoincrement start 1 increment 1 not null
    ,WB_MEASUREVALUE FLOAT
);

--- E_PILLAR_TRANSPARENCY
CREATE OR REPLACE TABLE WB_E_PILLAR_TRANSPARENCY_MEASURE (
    WB_ID int autoincrement start 1 increment 1 not null
    ,WB_MEASUREVALUE FLOAT
);

    Alright, so at this stage I had my tables, I could insert values, all good so far!

    Next up was wrangling my freshly created tables together for ease of use within Astrato.

    To accomplish this I scribbled up a view: WB_COMPARISON_MEASURES_VW which utilised Snowflakes Qualify clause against the WB_ID of each of the tables so as to leave me with only the most recent record from each.

    Knowing I would only be returning a single row from each of my tables I introduced an ID column with a constant value of 1 to facilitate joining everything together:

    CREATE OR REPLACE VIEW WB_COMPARISON_MEASURES_VW AS
SELECT WATER.ID, WB_COMPANYNAME, E_PILLAR_MEASURE, E_PILLAR_TRANSPARENCY_MEASURE, WATER_MEASURE, ENERGY_MEASURE, WASTE_MEASURE, ENV_MEASURE, GHG_MEASURE
FROM
    (SELECT 1 AS ID, WB_COMPANYNAME FROM WB_COMPANY qualify row_number() over (order by WB_ID desc) = 1) COMP
    ,(SELECT 1 AS ID, NVL(WB_MEASUREVALUE,0) AS WATER_MEASURE FROM WB_WATER_MEASURE qualify row_number() over (order by WB_ID desc) = 1) WATER
    ,(SELECT 1 AS ID, NVL(WB_MEASUREVALUE,0) AS ENERGY_MEASURE FROM WB_ENERGY_MEASURE qualify row_number() over (order by WB_ID desc) = 1) ENERGY
    ,(SELECT 1 AS ID, NVL(WB_MEASUREVALUE,0) AS WASTE_MEASURE FROM WB_WASTE_MEASURE qualify row_number() over (order by WB_ID desc) = 1) WASTE
    ,(SELECT 1 AS ID, NVL(WB_MEASUREVALUE,0) AS ENV_MEASURE FROM WB_ENV_MEASURE qualify row_number() over (order by WB_ID desc) = 1) ENV
    ,(SELECT 1 AS ID, NVL(WB_MEASUREVALUE,0) AS GHG_MEASURE FROM WB_GHG_MEASURE qualify row_number() over (order by WB_ID desc) = 1) GHG
    ,(SELECT 1 AS ID, NVL(WB_MEASUREVALUE,0) AS E_PILLAR_MEASURE FROM WB_E_PILLAR_MEASURE qualify row_number() over (order by WB_ID desc) = 1) EP
    ,(SELECT 1 AS ID, NVL(WB_MEASUREVALUE,0) AS E_PILLAR_TRANSPARENCY_MEASURE FROM WB_E_PILLAR_TRANSPARENCY_MEASURE qualify row_number() over (order by WB_ID desc) = 1) EPT
WHERE 
    COMP.ID = WATER.ID
AND COMP.ID = ENERGY.ID
AND COMP.ID = WASTE.ID
AND COMP.ID = ENV.ID
AND COMP.ID = GHG.ID
AND COMP.ID = EP.ID
AND COMP.ID = EPT.ID
;

    Looking alright:

    +----+----------------+------------------+-------------------------------+---------------+----------------+---------------+-------------+
| ID | WB_COMPANYNAME | E_PILLAR_MEASURE | E_PILLAR_TRANSPARENCY_MEASURE | WATER_MEASURE | ENERGY_MEASURE | WASTE_MEASURE | ENV_MEASURE |
+----+----------------+------------------+-------------------------------+---------------+----------------+---------------+-------------+
|  1 | ASTRATO        |                0 |                             0 |             0 |              0 |             0 |           0 |
+----+----------------+------------------+-------------------------------+---------------+----------------+---------------+-------------+

    OK, so at this point I have my 7 orphan tables for capturing inputs, plus a view that pulls everything together.

    User: "image.png"


    Next on the chopping block was to ready the data so the comparison was achievable. This was nice and simple and only required me to union the SCORES_ESG_SAMPLE data that was provided by Gaialens with my new WB_COMPARISON_MEASURES_VW plus the addition of a new "SOURCE" column so I can identify what rows came from where.

    CREATE OR REPLACE VIEW SCORES_ESG_SAMPLE_VW AS
SELECT
 'ESG' AS SOURCE
 ,"companyname"
 ,"symbol"
 ,"country"
,"sector"
,"industry"
,"sub_industry"
,"fiscalyear"
,"E_pillar_score_adjusted"
,"E_pillar_transparency_score"
,"energy_production_and_consumption"
,"environmental_impacts"
,"ghg_emissions"
,"waste_management"
,"water_management"
,"bitumns_and_lignit_surface_coal_mng_rev_div_total_rev_ipctl"
,"bitumns_undergrnd_coal_mining_rev_div_total_revenue_ipctl"
,"coal_mining_revenue_div_total_revenue_ipctl"
,"coal_power_generation_div_total_power_generation_ipctl"
,"coal_power_generation_revenue_div_total_revenue_ipctl"
,"company_no_revenue_from_fossil_fuels_flag_ipctl"
,"electricity_generation_score_ipctl"
...
FROM SCORES_ESG_SAMPLE_TABLE

UNION

SELECT
'WB' AS SOURCE
,WB_COMPANYNAME AS "companyname"
,NULL AS "symbol"
,NULL AS "country"
,NULL AS "sector"
,NULL AS "industry"
,NULL AS "sub_industry"
,NULL AS "fiscalyear"
,E_PILLAR_MEASURE AS "E_pillar_score_adjusted"
,E_PILLAR_TRANSPARENCY_MEASURE AS "E_pillar_transparency_score"
,ENERGY_MEASURE AS "energy_production_and_consumption"
,ENV_MEASURE AS "environmental_impacts"
,GHG_MEASURE AS "ghg_emissions"
,WASTE_MEASURE AS "waste_management"
,WATER_MEASURE AS "water_management"
,0"bitumns_and_lignit_surface_coal_mng_rev_div_total_rev_ipctl"
,0"bitumns_undergrnd_coal_mining_rev_div_total_revenue_ipctl"
,0"coal_mining_revenue_div_total_revenue_ipctl"
,0"coal_power_generation_div_total_power_generation_ipctl"
,0"coal_power_generation_revenue_div_total_revenue_ipctl"
,0"company_no_revenue_from_fossil_fuels_flag_ipctl"
,0"electricity_generation_score_ipctl"
...
FROM WB_COMPARISON_MEASURES_VW
;

    With the data prep done it was time to shift over to a bit of dashboard design!

    Using a Vertical bar chart I plotted the following fields and applied categorical conditional colouring based on the "SOURCE" field so the Input Company is easily discernible:

    1. Dimension 1: SCORES_ESG_SAMPLE_VW."companyname"
    2. Dimension 2: SCORES_ESG_SAMPLE_VW.SOURCE
    3. Measure: AVG(SCORES_ESG_SAMPLE_VW."ghg_emissions")
    User: "image.png"
  • dataemily
    dataemily Member Posts: 93 Type 2

    This is awesome @Taylor! Thanks for sharing how you created your winning entry! 🥈 I agree that the coming features in Astrato will simplify this process, but it is awesome to see what can be accomplished by harnessing the power of snowflake too! 💪

Categories