Warehouse table: Scores

Draws from CRM view A422_PersonScore.

Score records are joined to parent records by the PersonGUID. 

Note that this table, like the CRM view, does not flatten subscores: composite scores and their subscore scores are all given as single records here.  This table joins to itself from the composite's ScoreGUID to the subscore's ParentScoreGUID.  (In Stage III, a view over this table is provided which flattens composite scores and their subscores: see vw_Export_Scores.)

The ordinal field (in version Stage III and later) represents two distinct orders within this table. For composite scores with official sources, the ordinal lists records with official sources in order of test date with duplicates over test name + test date removed. For subscores, the subscores for each score are ordered by subscore name.

For example:

  • SAT on 2/1/2010: ordinal 1
  • SAT Critical Reading for this test: ordinal 1
  • SAT Mathematics for this test: ordinal 2
  • SAT Writing for this test: ordinal 3
  • SAT on 1/1/2010: ordinal 2
  • SAT Critical Reading for this test: ordinal 1
  • SAT Mathematics for this test: ordinal 2
  • SAT Writing for this test: ordinal 3
  • test on any date reported by student: ordinal is null 

 

 

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

 

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


Warehouse tableWarehouse columnField typeCRM viewCRM columnNotesStandard Export nameCRM tab
ScoresPercentile A422_PersonScorea422_a422_compositepercentile   
ScoresScore A422_PersonScorea422_compositescore Score_0N_TestValue,Score_0Scores
ScoresOfficialSource A422_dom_scoresourcea422_officialsource1 = official  
ScoresScoreGUID A422_PersonScorea422_personscoreidprimary key  
ScoresParentScoreGUID A422_PersonScorea422_rel_parentscoreid   
ScoresPersonGUID A422_PersonScorea422_rel_personid   
ScoresSourceGUID A422_PersonScorea422_rel_scoresourceid   
ScoresSource A422_PersonScorea422_rel_scoresourceidname Score_0N_TestSourceScores
ScoresTestNameGUID A422_PersonScorea422_rel_testnameid   
ScoresTestName A422_PersonScorea422_rel_testnameidname Score_0N_TestName,Score_0Scores
ScoresTestDateUTC A422_PersonScorea422_testdateutc Score_0N_TestDateScores
Scorescreatedby A422_PersonScorecreatedby   
Scorescreatedbyname A422_PersonScorecreatedbyname   
Scorescreatedonutc A422_PersonScorecreatedonutc   
Scoresmodifiedby A422_PersonScoremodifiedby   
Scoresmodifiedbyname A422_PersonScoremodifiedbyname   
Scoresmodifiedonutc A422_PersonScoremodifiedonutc   
Scoresstatecode A422_PersonScorestatecode   
Scoresstatecodename FilteredA422_PersonScorestatecodename   
Scoresstatuscode A422_PersonScorestatuscode   

Scores

statuscodename
FilteredA422_PersonScorestatuscodename


Scoresordinal  intn/an/acalculated (Stage III and later)   

 

 



 

 

As of version "Stage I":

Fields which have been struck through are recommended for removal. 

columntyperequiredextract linkagesample dataanalytics useexport use
a422_a422_compositepercentilefloat unfilteredNULL  
a422_compositescoredecimal unfiltered1170.0000000000vw__Scores. all fields except CRM_ContactID and AsOfDate / sort order for SAT2_High[2]XScore.TestValue, .SubscoreValue_0N
a422_namenvarchar(100) unfilteredAshley Burkins - SAT  
a422_officialsourcebit warehouse gets data from a different CRM view1filter = 1filter = 1
a422_personscoreiduniqueidentifieryesunfiltered[guid]primary keyXScore.ScoreID
a422_rel_parentscoreiduniqueidentifier unfiltered[guid] join
a422_rel_parentscoreiddscint unfiltered0  
a422_rel_parentscoreidnamenvarchar(100) unfilteredAshley Burkins - SAT  
a422_rel_personiduniqueidentifier unfiltered[guid]joinjoin
a422_rel_personiddscint unfiltered0  
a422_rel_personidnamenvarchar(160) unfilteredAshley Burkins  
a422_rel_personidyominamenvarchar(450) unfilteredAshley Burkins  
a422_rel_scoresourceiduniqueidentifier unfiltered[guid]  
a422_rel_scoresourceiddscint unfiltered0  
a422_rel_scoresourceidnamenvarchar(100) unfilteredSchool/transcript XScore.TestSource
a422_rel_testnameiduniqueidentifier unfiltered[guid]  
a422_rel_testnameiddscint unfiltered0  
a422_rel_testnameidnamenvarchar(100) unfilteredSATfilter = various hardcodedXScore.TestName, .SubscoreName_0N / sort order
a422_temp_parentscoreint unfilteredNULL  
a422_temp_parentscorenamenvarchar(255) needs filterNULL  
a422_temp_scoresourceint unfilteredNULL  
a422_temp_scoresourcenamenvarchar(255) needs filterNULL  
a422_temp_testnameint unfilteredNULL  
a422_temp_testnamenamenvarchar(255) needs filterNULL  
a422_testdatedatetime unfilteredMar 1 2009 12:00AMfilter is not nullsort order / XScore.TestDate
a422_testdateutcdatetime needs filterMay 1 2009 4:00AMfilter is not null (proposed)sort order (proposed) / XScore.TestDate (proposed)
createdbyuniqueidentifier unfiltered[guid]  
createdbydscint unfiltered0  
createdbynamenvarchar(160) unfilteredSally Eck  
createdondatetime unfilteredOct 15 2009 10:55AM
 
createdonutcdatetime needs filterOct 26 2009 2:27PMfilter < @Today 
importsequencenumberint unfilteredNULL  
modifiedbyuniqueidentifier unfiltered[guid]  
modifiedbydscint unfiltered0  
modifiedbynamenvarchar(160) unfilteredSally Eck  
modifiedondatetime unfilteredOct 26 2009 10:28AM  
modifiedonutcdatetimeyesneeds filterOct 15 2009 2:56PMprimary keysort order
overriddencreatedondatetime unfilteredNULL  
overriddencreatedonutcdatetime needs filterNULL  
owneriduniqueidentifier unfiltered[guid]  
owneriddscint unfiltered0  
owneridnamenvarchar(160) unfilteredSally Eck  
owneridtypeint unfiltered8  
owningbusinessunituniqueidentifier unfiltered[guid]  
owninguseruniqueidentifier unfiltered[guid]  
statecodeintyesunfiltered0  
statecodenamenvarchar(255) needs filterActive  
statuscodeint unfiltered1  
statuscodenamenvarchar(255) needs filterActive  
Comments