Warehouse table: PersonOrgs

Draws from A422_PersonOrg.

PersonOrg records are joined to parent records by the PersonGUID. 

 

This table is made available to clients via the Advanced Export.

 

This table represents the "Stage II" and up versioning of the warehouse.  For the stage I version, see below.


Warehouse tableWarehouse columnField typeCRM viewCRM columnNotesStandard Export nameCRM tab
PersonOrgsActive A422_PersonOrga422_activeorg1 = active / 0 = inactiveOrg_01_ActivePerson Organization
PersonOrgsClassRank A422_PersonOrga422_classrank Org_01_ClassRankPerson Organization
PersonOrgsClassSize A422_PersonOrga422_classsize Org_01_ClassSizePerson Organization
PersonOrgsCompleted A422_PersonOrga422_completed Org_01_GraduatedCompletedPerson Organization
PersonOrgsCreditsCompleted A422_PersonOrga422_creditscompleted Org_01_CreditsCompletedPerson Organization
PersonOrgsFinalTranscript A422_PersonOrga422_finaltranscript Org_01_FinalTranscriptPerson Organization
PersonOrgsGPA A422_PersonOrga422_gpa Org_01_GPAPerson Organization
PersonOrgsLastAttendedDateUTC A422_PersonOrga422_lastattendedutc Org_01_LastAttendedDatePerson Organization
PersonOrgsOrganizationGUID A422_PersonOrga422_organizationid   
PersonOrgsOrganizationName A422_PersonOrga422_organizationidname Org_01_NamePerson Organization
PersonOrgsPersonGUID A422_PersonOrga422_personid   
PersonOrgsPersonOrgGUID A422_PersonOrga422_personorgidprimary key  
PersonOrgsRankPercentile A422_PersonOrga422_rankpercentile Org_01_RankPercentilePerson Organization
PersonOrgsDegreeDiplomaGUID A422_PersonOrga422_rel_degreediplomaid   
PersonOrgsDegreeDiploma A422_PersonOrga422_rel_degreediplomaidname Org_01_DegreeDiplomaRcvdPerson Organization
PersonOrgsOrgType Accounta422_rel_orgtypeidname   
PersonOrgsGPASelfReported A422_PersonOrga422_selfreportedgpa Org_01_GPASelfReportedPerson Organization
PersonOrgsClassRankSelfReported A422_PersonOrga422_selfreportedrank Org_01_ClassRankSelfReportePerson Organization
PersonOrgsTranscriptRecievedDateUTC A422_PersonOrga422_transcriptreceivedutc Org_01_TranscriptRcvdDatePerson Organization
PersonOrgsAICode Accountsic Org_01_CodePerson Organization
PersonOrgscreatedby A422_PersonOrgcreatedby   
PersonOrgscreatedbyname A422_PersonOrgcreatedbyname   
PersonOrgscreatedonutc A422_PersonOrgcreatedonutc   
PersonOrgsmodifiedby A422_PersonOrgmodifiedby   
PersonOrgsmodifiedbyname A422_PersonOrgmodifiedbyname   
PersonOrgsmodifiedonutc A422_PersonOrgmodifiedonutc Org_01_ModifiedOnPerson Organization
PersonOrgsstatecode A422_PersonOrgstatecode   
PersonOrgsstatecodename FilteredA422_PersonOrgstatecodename   
PersonOrgsstatuscode A422_PersonOrgstatuscode   
PersonOrgsstatuscodename FilteredA422_PersonOrgstatuscodename   
PersonOrgsordinalintn/an/acalculated based on modified date for active records (Stage III and later)

 

 


 


 

 

 


 

 

 


 

 

As of version "Stage I":

Fields which have been struck through are recommended for removal.  The "-name" fields which are the yes/no translations of corresponding bit fields can be shifted to use the value of the bit field, with a verification that the bit values are consistently translated.

 

columntyperequiredextract linkagesample dataanalytics useexport use
a422_activeorgbit unfiltered1filter = 1 (proposed)filter = 1 (proposed) / Xport.Org_01_Active
a422_activeorgnamenvarchar(255) needs filterYesfilter = 'Yes' filter = 'Yes' 
a422_classrankint unfiltered150 Xport.Org_01_ ClassRank
a422_classsizeint unfiltered391 Xport.Org_01_ ClassSize
a422_completedbit unfiltered0 Xport.Org_01_ GraduatedCompleted
a422_completednamenvarchar(255) needs filterNo  
a422_creditscompletedint unfiltered20 Xport.Org_01_ CreditsCompleted
a422_currentlyattendingbit unfiltered1  
a422_currentlyattendingnamenvarchar(255) needs filterYes  
a422_finaltranscriptbit unfiltered 0 Xport.Org_01_ FinalTranscript
a422_finaltranscriptnamenvarchar(255) needs filterNo  
a422_gpafloat unfiltered3.7vw_ActiveHS. HS_GPA Xport.Org_01_GPA
a422_lastattendeddatetime needs UTC conversionJun 1 2010 12:00AM Xport.Org_01_ LastAttendedDate
a422_lastattendedutcdatetime unfilteredJun 1 2010 4:00AM Xport.Org_01_ LastAttendedDate (proposed)
a422_namenvarchar(100) unfilteredCourtney Hawkins : Scecina Memorial High School  
a422_org_sicnvarchar(20) warehouse gets data from a different CRM view151745vw_ActiveHS. AICODE Xport.Org_01_Code 
a422_organizationiduniqueidentifier unfiltered[guid]  
a422_organizationiddscint unfiltered0  
a422_organizationidnamenvarchar(160) unfilteredScecina Memorial High Schoolvw_ActiveHS. HSName  Xport.Org_01_Name
a422_organizationidyominamenvarchar(160) unfilteredNULL  
a422_personiduniqueidentifier unfiltered[guid]joinjoin
a422_personiddscint unfiltered 0  
a422_personidnamenvarchar(160) unfilteredCourtney Hawkins  
a422_personidyominamenvarchar(450) unfiltered Courtney Hawkins  
a422_personorgiduniqueidentifieryesunfiltered[guid]primary key  
a422_positionnvarchar(100) unfiltered NULL  
a422_rankpercentilefloat unfiltered62vw_ActivsHS. RankPercentile Xport.Org_01_ RankPercentile
a422_rel_degreediplomaiduniqueidentifier unfiltered[guid]  
a422_rel_degreediplomaiddscint unfiltered0  
a422_rel_degreediplomaidnamenvarchar(100) unfilteredHS diploma Xport.Org_01_ DiplomaDegreeRcvd
a422_rel_orgtypeidnamenvarchar(100) warehouse gets data from a different CRM viewHigh schoolfilter = 'High School'  
a422_selfreportedgpabit unfiltered1vw_ActiveHS. HS_GPA_ SelfReported (proposed)Xport.Org_01_ GPASelfReported
a422_selfreportedgpanamenvarchar(255) needs filterYesvw_ActiveHS. HS_GPA_ SelfReported  
a422_selfreportedrankbit unfiltered 1vw_ActiveHS. RankPercentile_ SelfReported (proposed)Xport.Org_01_ ClassRankSelfReported
a422_selfreportedranknamenvarchar(255) needs filterYesvw_ActiveHS. RankPercentile_ SelfReported  
a422_temp_degreediplomaint not warehousedNULL  
a422_temp_degreediplomanamenvarchar(255) not warehousedNULL  
a422_transcriptreceiveddatetime needs UTC conversionNov 11 2009 12:00AM Xport.Org_01_ TranscriptRcvdDate
a422_transcriptreceivedutcdatetime unfilteredNov 11 2009 5:00AM Xport.Org_01_ TranscriptRcvdDate (proposed)
createdbyuniqueidentifier unfiltered[guid]  
createdbydscint unfiltered0  
createdbynamenvarchar(160) unfilteredC422 System  
createdondatetime unfilteredOct 15 2009 12:02PM  
createdonutcdatetime needs filterOct 15 2009 4:02PM  
importsequencenumberint unfilteredNULL  
modifiedbyuniqueidentifier unfiltered[guid]  
modifiedbydscint unfiltered0  
modifiedbynamenvarchar(160) unfilteredTiffany Hendrix  
modifiedondatetime needs UTC conversionDec 3 2009 10:18AMsort order for unique viewXport.Org_01_ ModifiedOn
modifiedonutcdatetimeyesunfilteredDec 3 2009 3:18PMprimary key / sort order for unique view (proposed)Xport.Org_01_ ModifiedOn (proposed)
overriddencreatedondatetime needs UTC conversionNULL  
overriddencreatedonutcdatetime unfilteredNULL  
owneriduniqueidentifier unfiltered[guid]  
owneriddscint unfiltered0  
owneridnamenvarchar(160) unfilteredNick Torres  
owneridtypeint unfiltered8  
owningbusinessunituniqueidentifier unfiltered[guid]  
owninguseruniqueidentifier unfiltered[guid]  
statecodeintyesunfiltered0  
statecodenamenvarchar(255) needs filterActive  
statuscodeint unfiltered1  
statuscodenamenvarchar(255) needs filterActive  

 

This warehouse table is used for two analytics views, vw_DescAnalytics_ActiveHighSchool andvw_DescAnalytics_ActiveHighSchool_NonUnique.  The only difference between the two is that the first view gives only the most recently modified record and also provides an AsOfDate, which the second view shows all (distinct) records which otherwise match the criteria.


Comments