11 Aug 2022
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”).
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
- The actual answer (
field_value, e.g. “Azerbaijan“) goes into a field such as
- The original common app ID (
record_id, e.g. “123“) helps pick an appropriate parent
Application__clookup record ID.
- The column name (
field_key, e.g. “COUNTRY“) helps pick an appropriate parent
Question__clookup record ID.
- The column name (
field_key, e.g. “COUNTRY“) also helps create/pick an appropriate parent
Requirement_Response__clookup record ID.
- Other data (e.g.
name_lf) might not directly get inserted into any part of a
Question_Response__crecord, 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
- The source data originally contained one row per application.
- The data-load-ready
UNPIVOTresults now contain one row per detail noted on the application.
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
|COUNTRY||Home Country||Contact Info|
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:
|123||Home Country||Contact Info||Azerbaijan||Amjit, Anush|
|123||Stated Gender||Demographics||F||Amjit, Anush|
|456||Home Country||Contact Info||Zimbabwe||Borges, Benita|
|456||Stated Gender||Demographics||N||Borges, Benita|
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