Reference_5

MF_FacultyNonTeachingAssignment

MD_CollPers_SurrogateKey

Relationship_6

MF_FacultyMilestones

MD_CollPers_SurrogateKey

Relationship_39

MF_CommitteeParticipation

MD_CollPers_SurrogateKey

Relationship_62

MF_BenefitDates

MD_CollPers_SurrogateKey

Relationship_68

MF_PerPosition

MD_CollPers_SupervisorKey

Relationship_88

MF_SurveyAdminFact

MD_CollPers_SurrogateKeyEmp

Relationship_144

MF_RoomAssignment

MD_CollPers_SurrogateKeyEmp

Relationship_148

MF_BenefitCarletonContribution

MD_CollPers_SurrogateKey

Relationship_172

MF_AlumEventParticipation

MD_CollPers_SurrogateKeyEmp

Relationship_177

MF_AlumContact

MD_CollPers_SurrogateKeyEmp

Relationship_259

MF_KeyedComputerSoftwareUsage

MD_CollPers_SurrogateKey

Relationship_266

MF_KeyedComputerEvent

MD_CollPers_SurrogateKey

MD_Pers_AlienStatus

As opposed to MD_Pers_IsNonResidentAlien, which simply classifies a person as a nonresident alien or not (and is normally more than sufficient for reporting), this field houses detailed alien status information.

As of 2011, this field is not populated in the data warehouse, and is not even implemented in the physical data warehouse tables. It will probably need to be added and populated at some point.

DW_CodeDesc

varchar(32)

32

MD_Pers_EnterpriseUniqueID

Unique "person" ID assigned to everyone connected with Carleton. Maintained by the identity management system. Should be mandatory, but we cannot make it so until the IdM system is complete and fully functioning - and we can call out to it in our ETL code.

This attribute is currently not populated.

DW_EnterpriseUniqueID

char(8)

8

MD_Pers_Surname

Surname in the US means the same thing as "last name." Does not include suffixes like Jr., or honorifics.

This attribute is mandatory and has no default.

DW_Surname

nvarchar(32)

32

MD_Pers_MiddleName

Middle name or initial (if available). This field is fairly "dirty" and poorly maintained in some of our systems, especially in Recruitment Plus (not because our staff is doing a poor job, but because they sources from which they obtain their data are of uneven quality). This field should be avoided, therefore, if possible, particularly for prospective student data.

The default value is "missing".

DW_GivenName

nvarchar(32)

32

MD_Pers_PrimaryAffiliation

Main relationship to Carleton. Values should be taken from the identity management system, if available. Defaults to 'Unknown'. Values are taken from a controlled vocabulary including: Student, Faculty, Staff, Alum, Parent, Friend.

Where populated, this attribute is an excellent measure of general affiliation. It is not, however, populated for all 'people' dimensions.

DW_CarlAffiliation

varchar(16)

16

MD_Pers_AgeRange

Age of person, discretized into a set of ranges useful for reporting and demographic classification. If age is unknown, the value of this attribute will be "Unknown."

Note that the age is calculated from the birth date, and does not take death into account. The age range, therefore, indicates the age if the person were alive (and in most cases they are).

DW_AgeRange

varchar(12)

12

MD_Pers_HomeAddrUSState

State of home address. E.g., if my home address is 203 Woodley Street West, Northfield, MN 55057 USA, then my state is 'Minnesota'. If I live outside the US, this attribute will be 'Not Applicable'. If no home address is available for me, the value will be 'Unknown'. If my home address is unavailable, and all we have is a mailing address, we use the mailing address (in preference to nothing at all) because, presumably, I receive mail somewhere near where I live, or in a place where I spend a lot of time.

DW_Addr_USState

varchar(24)

24

MD_Pers_HomeAddrUSRegionFIPS

Contains the FIPS US region (e.g., "Middle Atlantic" or "New England"). Defaults to "Unknown." Used for various reporting. Compare the USRegionCarleton, which is also used for institutional reporting. Everyone is encouraged to use one of these two systems, if at all possible - for consistency's sake.

FIPS codes, in general, are a set of standardized numeric or alphabetic codes issued by the US National Institute of Standards and Technology (NIST) to ensure uniform identification of geographic entities throughout all federal government agencies.

DW_USRegion

varchar(64)

64

MD_Pers_HomeAddrCountry

DO NOT USE THIS FIELD FOR SERIOUS REPORTING. The data is entered for operational/addressing purposes, and is not of sufficiently high quality to work for analysis.

In Colleague, at least as of 2011, international addresses are not handled well, and therefore the country may not be well or correctly populated. Colleague, for example (again, as of 2011), has no place for a foreign postal code, so often people place that, along with the country, in the space that's supposed to house the city. This makes things print correctly, but it means the country will not be populated. And since the data warehouse defaults to 'USA' if this field is blank (which is generally appropriate, across most systems), this means that many international addresses end up in the warehouse with a country of the United States.

In general, therefore, it is wise to use MD_Pers_Citizenship instead of MD_Pers_HomeAddrCountry, although the challenge there is that citizenship is designated in different ways across systems (not necessarily with consistent ISO country names or codes).

Note that country data for prospective students, especially ones who are only 'suspects' (early in the admissions cycle - not applicants or admits) may not be wholly reliable, because we obtain their addresses from third parties who don't consistently mark the country.

DW_Addr_Country

nvarchar(64)

64

MD_Pers_HomeAddrLongitude

Longitude of home address, as a floating point number (negative = west longitude). Best populated for US addresses, and populated in a very coarse fashion for those (using the zipcode) before late summer 2011.

Warning: Note that there is no default, and the value here may be NULL.

Values for this attribute from before late summer 2011 are not truly fit for user consumption, except by special request, and should not (yet) be exposed via views.

Note that the longitude and latitude for a given address may change over time, without any change in the address, because we occasionally change geocoding providers. The geocoding providers are also continually improving their algorithms. If a longitude or latitude changes, without any address change, a new row is generated in the data warehouse. But history is not rewritten. The earlier, presumably less accurate figure remains in non-current rows.

<None>

float

MD_Pers_HomeAddrMilesFromCampus

Distance in miles from Carleton main campus. Populated using the home address latitude and longitude, and only for US addresses with a zipcode. The value here has poor resolution and should not be used except for very general groupings (nothing with a resolution less than twenty miles). Eventually we may license a geocoding service, and populate this attribute well. Until then, YMMV.

This attribute is not 'fit' for user consumption, except by special request, and should not (yet) be exposed via views.

Warning: Note that there is no default, and the value here may be NULL.

<None>

float

MD_Pers_IsOfUnknownRaceEthnicity

"Yes" if neither the race nor ethnicity is known. "No" otherwise. Calculated from Race and IsHispanicOrLatino, as well as from OLDPrimaryRaceList. If the old race list is empty or 'Unknown' and if Race is 'Unknown', and IsHispanicOrLatino = 'Unknown' or 'No', then this attribute will be 'No'. It basically means we don't know enough to classify someone by ethnicity or race, in either the old or the new 2010 census system.

DW_YesOrNoNOTNULL

char(3)

3

MD_Pers_Race

Holds the person's race. This attribute can be treated as "primary race" although the way this information is collected makes the two not entirely equivalent, since in some cases Carleton may choose to collect fuller information on people of multiple races.

This attribute (as opposed to MD_Pers_OldPrimaryRace) does not tell us whether a person is of Hispanic, Latino, or Spanish origin.

Note that in the new ethnicity/race classification system (2010 census and beyond), multi-racial people cannot indicate what races they belong to. They are simply multi-racial. Also, Hispanic is not considered a race, as noted. It's an ethnicity.

Hence this attribute will always have a single value, one of:

African American
American Indian-Alaska Native
Asian
Hawaiian-Pacific Islander
Two or More Races
Unknown
White

This attribute is not populated before 2009, and is unevently populated until 2011 (as of 2011, employees have not been fully resurveyed).

Note that some systems store only one race/ethnicity. Others do not. The data here will therefore be of uneven completeness and consistency. When reporting, stick with a single system of record, or a single constituency (like 'prospective students') and you'll be fine. Otherwise, be extremely careful, and be sure to work with people who administer the source systems who can advise you.

General documentation on how race is now stored (2010) in major systems: http://apps.carleton.edu/campus/ira/fed_race_ethnic/

DW_RaceForCensus2010

varchar(64)

64

MD_Pers_OLDPrimaryRace

Until the census-2010 race/ethnicity system is fully implemented, this is our main "race" attribute. It should eventually become, hence the designation "old." It will still be populated, however, even after we have converted to the new census 2010 system, so that we can report easily across multiple years, both before and after the transition. And it is the only attribute you can reliably use to report on ethnicity data, longitudinally, before and after the transition (which began in late 2009).

Possible values of this attribute include:

Asian
Black or African American
Hispanic
Mixed race or other
Native American-Alaskan Native
Unknown
White

The primary race is not necessarily selected by the person reflected in the data. For students it is inferred using a 'trumping' system that's complex and that most folks don't understand fully (e.g., if you're white and African American, the latter trumps the former and becomes your primary ethnicity). In this old system, 'Hispanic' was considered a race. Or, conversely, everything was an ethnicity. In the new 2010 census system, 'Hispanic' is an ethnicity, and most everything else ('Asian', 'White', etc.) is a race.

When reporting, stick with a single system of record, or a single constituency (like prospective students or degree students) and you'll be fine. Otherwise, be extremely careful, and be sure to work with people who administer the source systems who can advise you.

Note also that, as per institutional standards, ethnicities like 'Unknown' are not automatically counted as white, as sometimes happens in reports generated, e.g., by Student Financial Services and other offices.

DW_CodeDescLong

varchar(64)

64

MD_Pers_IsVeteran

"Yes" if a person is a veteran by federal government reporting standards, particularly EOE, for employees. "No" otherwise. Although the database also allows "Unknown" here, this value is not used.

Veteran status is available in Recruitment Plus for prospective students, although not well maintained. Veteran status is maintained in Colleague, for students, but we rarely have anything to put in this field, since so few of our students are veterans (e.g., eight as of March 2009).

DW_YesNoOrUnknown

varchar(7)

7

MD_Pers_Disability2

DW_Disability

varchar(32)

32

MD_Pers_DisabilityList

Comma-separated list of all disabilities. Feds will be asking us to track this for staff and students.

This attribute is currently, as of 2009, not populated.

DW_CommaDelimitedList

varchar(128)

128

MD_Emp_PrimaryTitle

Primary title for employee's primary position at Carleton. It is unclear whether this attribute is genuinely needed in the data warehouse. If you need to report on titles, you are probably generating lists or mailing labels, and for this you should go to the appropriate system of record - not the data warehouse.

Taken from PERPOS.TITLE in Colleague, from the record for the employee's primary position.

DW_Title

varchar(64)

64

MD_Emp_PrimaryOfficeBuilding

Building where office (or primary office, if there are more than one) is located.

DW_Building

nvarchar(32)

32

MD_Emp_Status

Indicates whether someone affiliated with Carleton is an employee, dining services employee, student worker, retired, etc. Based on Colleague XHRP_CURRENT_STATUS, but here we use the description, not the two-letter code. E.g., for code 'EM' we use 'Employee' here; for 'DI' we use 'Directory-Only.' This is to make the attributes usable in reports without decode tables and data dictionaries. The descriptions are fairly lucid in and of themselves.

Note that when counting faculty, we are looking for people with status Employee (EM). Retired (emeritus) faculty are codes as EM if they are teaching, but as RE (Retired) if not. The timing of the switch to EM will naturally affect reports.

DW_CodeDesc

varchar(32)

32

MD_Emp_AppointmentReason

This field provides information on why someone was hired (new leave replacement) or moved into a new position (e.g., promotion).

The information contained here applies only to the employee's primary position, and it applies only to that position, not to previous positions held at Carleton.

Typically this field should not be exposed to users, as it is not useful for most reporting/analysis purposes, and because its coverage, for any given employee, is only partial.

DW_CodeDescLong

varchar(64)

64

MD_Emp_IPEDSPositionCategory

These are the IPEDS/EEO categories for the employee's primary position at Carleton. We use descriptions here, not codes. E.g., instead of 2B3, we use "Secretarial/Clerical".

This data, now part of the Human Resources (HR) component of IPEDS, was previously a separate collection. It was instituted by IPEDS in 2001–02 as a response to a proposal by the National Postsecondary Education Cooperative focus group on faculty and staff. It allows institutions to “assign” all faculty and staff to distinct categories. The EAP collects information on all employees on the institution’s payroll as of November 1 of the reporting year, by full- and part-time status; by function or occupational category; and by faculty status and tenure status. Institutions with medical schools are required to report their medical school employees separately. This obviously would not apply to Carleton.

DW_CodeDescLong

varchar(64)

64

MD_Emp_PrimaryPositionType

This field tells us whether a person's employment at Carleton is permanent, temporary, student work, student break work, etc.

Corresponds to MD_Pos_Type in the MD_Position dimension. Records the 'type' of the person's main position, if a person holds multiple positions.

Because its coverage may not be complete, for any given staff member, it is recommended that users avoid this field in reports. It is provided here for completeness' sake. To analyze Carleton's workforce by position type, one would typically use the MD_Position dimension, and join it to a fact table.

DW_CodeDesc

varchar(32)

32

MD_Emp_PrimaryPositionStartDate

Start date for current position. If multiple positions are held, then we take the primary position. If the employee has left the organization, or for some other reason has no position start date, then the value here will be 9999-12-31.

This field, being a date (rather than a range or a textual attribute) should generally not be exposed to users.

<None>

datetime

MD_Emp_PrimaryPositionEndDate

End date for primary position. Note that in general the value here will be 9999-12-31, indicating either that there is no end date, or that the end date is not available.

Because of the ambiguity here, this attribute should NOT BE EXPOSED TO USERS. It will confuse them.

<None>

datetime