# 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. --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://help.alumniq.com/signature/projection.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.