Warehouse Structure and Projection

Warehouse Structure and Projection

Common Core

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):

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

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.