Warehouse base tables: OpportunityPerson

This tables holds information about opportunities, students, and addresses. This table is available in four forms:

The OpportunityPerson_Slow holds opportunity and person data from "slow-changing" fields; it holds current and historical records for each opportunity, so it has IsCurrent flag column in addition to its data fields.  (This table was originally names "AnalyticsMaster", reflecting its original use in descriptive analytics.)

The OpportunityPerson_Fast holds current records for each opportunity; the data in this table is considered "fast-changing" in contrast to the "slow-changing" data tracked in the AnalyticsMaster table.

The OpportunityPerson_Local holds one record per opportunity, and is intended for use with Advanced Export; it holds only current records and not historical ones. This table flattens the opportunity and person data (slow- and fast-changing) as well as staff assignment data.

The OpportunityPerson_Deleted has a DeletedDate column and holds information about records deleted from the CRM system. This table retains the records as given in the AnalyticsMaster table, so has only slow-changing data.


The StaffAssigned field in the OpportunityPerson_Local flattens data from the StaffAssignment table.

Previously, the AnalyticsMaster table held flattened race and address subrecords. These are now available in the Races table and the Addresses table.

 

[An updated mapping of the OpportunityPerson_Slow and OpportunityPerson_Fast tables should be placed here.]

 

The old AnalyticsMaster table was defined as follows:

 

ColumnCRM viewCRM columnAvailabilityNotesStandard Export nameCRM tab    
AcademicMajorOpportunitya422_rel_majorvalueidName  AcademicMajor 
Address_01_CityContactAddress1_City  Address_01_City 
Address_01_CountryRegionContactAddress1_Country  Address_01_CountryRegion 
Address_01_CountyContactAddress1_County  Address_01_County 
Address_01_PostalCodeContactAddress1_PostalCode  Address_01_PostalCode 
Address_01_StateProvinceContactAddress1_StateOrProvince  Address_01_StateProvince 
AdvisorOpportunitya422_rel_advisornameNot yet available ">
     
ApplicationTypeOpportunitya422_applicationtypeidName  ApplicationType 
BirthDayContactBirthDate  BirthDay 
CampusOpportunitya422_rel_campusidName  Campus 
CitizenshipContacta422_rel_citizenshipidName  Citizenship 
ClassificationOpportunitya422_rel_oppclassificationidName  Classification 
ConditionOfAdmissionOpportunitya422_rel_condofadmissionidName  ConditionOfAdmission 
DeceasedYesNoContacta422_deceasedNot yet available DeceasedYesNo     
DecisionPlanOpportunitya422_rel_decisionplanidName  DecisionPlan 
Departmentn/an/a hardcoded by the extractDepartment 
EffectiveDaten/an/a day on which any information last changed  
EnrollmentStatusOpportunitya422_rel_enrollmentstatusidName  EnrollmentStatus 
EntryTermOpportunitya422_rel_entrytermidName  EntryTerm 
FelonyYesNoContacta422_felonyNot yet available FelonyYesNo     
FinancialAidInterestOpportunitya422_rel_finaidinterestidName  FinancialAidInterest 
FirstActivityOpportunityA422_FirstActivity  FirstActivity 
FirstActivityDateActivityPointeractualend Still a UTC dateFirstActivityDate 
FirstNameContactFirstName  FirstName 
GenderContacta422_rel_genderidName  Gender 
GeoMarketContactA422_GeoMarket  GeoMarket 
HighScoreContactA422_HighScore  HighScore 
HispanicContactA422_Hispanic  Hispanic 
HousingOpportunitya422_rel_housingidName  Housing 
HowDidYouHearContacta422_rel_howhearidnameNot yet available HowDidYouHear     
LastNameContactLastName  LastName 
LegacyOpportunitya422_rel_legacyidName  Legacy 
MaritalStatusContacta422_rel_maritalstatusidName  MaritalStatus 
MiddleNameContactMiddleName  MiddleName 
OpportunityGUIDOpportunityOpportunityId primary keyOpportunityID 
OpportunityTypeOpportunitya422_rel_opptypeidName  OpportunityType 
OrgNamen/an/a hardcoded by the extractOrgName 
PersonGUIDOpportunityContactId  PersonID 
PreProfessionalOpportunitya422_rel_preprofessionalidName  PreProfessional 
Race_0N_CategoryA422_Ethnicitya422_rel_ethnicitycategoryidnamethese fields will be removed when the Races table is availablen = 1 to 8      
Race_0N_ValueA422_Ethnicitya422_rel_ethnicityvalueidnamethese fields will be removed when the Races table is availablen = 1 to 8Race_0N_Value     
RatingOpportunitya422_rel_oppratingidName  Rating 
ReligiousPreferenceContacta422_rel_religiousprefidName  ReligiousPreference 
SecondAcademicAreaOpportunitya422_rel_secondacademicvalidName  SecondAcademicArea 
StaffAssignedOpportunityowneridnameAvailable with the Stage IIa upgradestored separately in the warehouse in the StaffAssignment tableStaffAssignedOpportunity/Admin    
StateOfResidencyOpportunitya422_rel_stateidName  StateOfResidency 
VeteranYesNoContacta422_veteranNot yet available VeteranYesNo     
VisaTypeContacta422_rel_visatypeidName  VisaType 


 

Comments