Transforming CommonApp into Salesforce EASY question responses

11 Aug 2022 🔖 salesforce integration databases
💬 EN

I just posted how to use Oracle’s UNPIVOT to turn a “wide” table into a “skinny” one with just “field name” & “field value” columns … but why? Let’s take a look at an example from higher education with the Common App and Salesforce EASY / EDA.

I was helping someone in Higher Ed build a daily data-load between Common App CSV files as received by SFTP and their Salesforce-based admissions application database, which leverages the Education Data Architecture (“EDA” / “HEDA”) and the open-source package Enterprise Application Solution for Yield (“EASY”).

Basline transformation

Common App input data

The Common App’s dataset looks like my input from the UNPIVOT tutorial:

Each Common App source record represents 1 admissions application to the school.

Each source record has dozens of columns/fields describing things like home-country, gender, and more.

Salesforce EASY output requirements

For any given Common App record, each of these dozens of field values needs to be inserted as a standalone record into a Salesforce object called Question_Response__c that comes with “EASY” (as a child-object of an Application__c object).

• The actual answer (field_value, e.g. “Azerbaijan“) goes into a field such as ValueText__c.
• The original common app ID (record_id, e.g. “123“) helps pick an appropriate parent Application__c lookup record ID.
• The column name (field_key, e.g. “COUNTRY“) helps pick an appropriate parent Question__c lookup record ID.
• The column name (field_key, e.g. “COUNTRY“) also helps create/pick an appropriate parent Requirement_Response__c lookup record ID.
• Other data (e.g. name_lf) might not directly get inserted into any part of a Question_Response__c record, but some of it might be helpful for humans if they need to troubleshoot problems with the data-load.

Transform with Oracle UNPIVOT

This school often facilitates transforming CSV files by dumping them into Oracle tables of the same structure – hence being interested in UNPIVOT:

• The source data originally contained one row per application.
• The data-load-ready UNPIVOT results now contain one row per detail noted on the application.

Lookup helpers

There’s still work to do. For example, helpers still need to be built out that transform a record_id value like “123” into a Question_Response__c.Application__c suggestion, and that transform a field_key like “country” into a specific Question_Response__c.Question__c suggestion. But at least now the data is in the correct “shape” for tacking such details onto.

Build a helper Oracle table

For example, the school might decide to build out an Oracle table called common_app_to_easy_questions that looks like this, which could be joined into the query’s field_key column on common_app_column_name:

CREATE TABLE common_app_to_easy_questions (
    common_app_column_name VARCHAR2(60) PRIMARY KEY NOT NULL -- No idea if there's a limit on the CSV file column name
    , question_name VARCHAR2(80) -- Salesforce built-in "Name" field is, as of 8/11/22, max 80.
    , question_requirement_item_name VARCHAR2(80) -- Salesforce built-in "Name" field is, as of 8/11/22, max 80.
);

INSERT ALL
    INTO common_app_to_easy_questions (common_app_column_name, question_name, question_requirement_item_name)
        VALUES ('COUNTRY', 'Home Country', 'Contact Info')
    INTO common_app_to_easy_questions (common_app_column_name, question_name, question_requirement_item_name)
        VALUES ('GENDER', 'Stated Gender', 'Demographics')
SELECT * FROM dual;
COMMIT;

Join the data

select
    record_id
    , common_app_to_easy_questions.question_name
    , common_app_to_easy_questions.question_requirement_item_name
    , qUnpivot.field_value
    , name_lf
from
    source_table
unpivot (
    the_value
    for the_key
    in (
        citz_status
        , race_ethnicity
    )
) qUnpivot
inner join common_app_to_easy_questions
    on qUnpivot.the_key = common_app_to_easy_questions.common_app_column_name
;

Once you did the join, your data for loading into Question Response could look more like this:

Insert with an enterprise ETL tool

As long as there exists, for example, some Salesforce record for which this SOQL query would return data…

select id
from Question__c
where Name = "Home Country"
and Requirement_Item__r.Name = "Contact Info"

…then a decent “Extract, Transform, Load” (ETL) tool ought to offer a mechanism by which you can tell it that you’d like it to look up what value to put into Salesforce’s Question_Response__c.Question__c field when inserting a new Salesforce Question_Response__c record, based on the data you have in your Oracle SQL query’s question_name and question_requirement_item_name columns.