This table (warehouse) is the same for all customers.
xid text
, xid2 text
, prefix text
, firstName text
, middleName text
, lastName text
, maidenName text
, badgeNameOverride text
, badgeLastNameOverride text
, badgeAnnotation text
, address1 text
, address2 text
, city text
, state text
, postalCode text
, nation text
, emailAddress text
, homePhoneNumber text
, mobilePhoneNumber text
, prefclassyear text
, prefschool text
, prefdegree text
, prefdegreetype text
, isAlumnus text
, isGradAlumnus text
, isFamilyOfStudent text
, isFaculty text
, isStaff text
, isStudent text
, isGradStudent text
, prospectManager text
, prospectManagerEmail text
, isMember integer default 0
, memberType text
Client Customizations
A secondary table (warehouseB) contains at minimum xid and is used for client-specific warehouse additions. Additional fields that are unique to a customer are added like so (this example is from Penn):
All fields in warehouseB must contain the __c suffix to avoid conflicts with fields in the main warehouse or any registration-related tables. These fields are automatically included in the event and group reports on demand.
Projection
One recurring frustration with degree data is that in most cases we should be deferring to the warehouse for pref class and pref school information. However, people are flaky and goofy and weird, so there are situations where we'll need to override that information on a case-by-case basis.
The new rule is this:
if the registrant is matched (which they all should be if they're known to us), we'll use pref school and pref class info from the warehouse
if a different value is specified on the registration record, we'll use that instead
COALESCE(nullif(p.prefclassyearoverride, -1), nullif(w.prefclassyear, '')::int) AS prefclassyear COALESCE(nullif(p.prefschooloverride, ''), nullif(w.prefschool, '')) as prefschool
Loading the Warehouse
Most customers give us a static feed. Others provide data on demand via a web service call. The behaviors for managing the data vary accordingly.
Static Warehouse
The procedure is as follows:
load customer data into a custom, temporary(ish) w_constituents table that matches their import file format
-- review and if necessary customize the WarehouseService.loadWarehouse() method to ETL the data into both warehouse and warehouseB
run WarehouseService.loadWarehouse() to fill the tables
The registration warehouse will catch up in 10-15 minutes.
Web Service Warehouse
The procedure is slightly different, as we don't have a pool of data to draw upon to prefill warehouse/warehouseB. Good news is this isn't necessarily a problem. We simply load an individual constituent record every time getWarehouseRecord is called.
getWarehouseRecord calls an internal searchWarehouse method that hits the web service passing XID along
searchWarehouse is expected to invoke the web service and return a standard AlumnIQ common core recordset which generally requires some transformation (and possibly supplemental calls to do so)
getWarehouseRecord will then call loadWarehouse(xid) which will populate warehouse/warehouseB accordingly for reporting purposes
The registration warehouse will catch up in 10-15 minutes.
xid text
, pennkey__c text
, current_trustee__c text
, curfy_donor__c text
, pennfund_frequency__c text
, unit_assigned__c text
, ability_rating__c text
, pm_region_descr__c text
