Dict-Physical

Table MF_SurveyCellData

Card of table MF_SurveyCellData

Name	MF_SurveyCellData
Dimensional Type	Fact
Comment	This fact table holds counts of particular answers to particular questions on particular survey versions, given to particular subjects on particular dates and times. It is really only useful for surveys where the answers can be treated as nominal data, although each fact row here has a SQL type and stat type (nominal, ordinal, etc.) attached to it, to facilitate type conversions, if needed. Free-form comments or discursive answers are not included here because the iinformation contained here is intended for quantitative analysis, mainly in reports and crosstabs/pivot tables. NOTE WELL: This table and other related ones (e.g., MD_SurveyInstance, MD_SurveyQuestionAndAnswer) are not intended, collectively, to replace SPSS or other such software. This table's sole purpose is to make it easy to analyze certain types of survey data (primarily nominal variables mapping to individual survey questions) in Excel and other simple BI tools. Note also that rows in this table link optionally to various "people" dimensions. Typically answers to any given survey will apply to one population (e.g., students) and so a subset of all possible foreign keys linking to people dimensions will be nonnull. Put more simply, not every answer to every survey question will be linked to information on alums, staff, students, and prospects. Typically it will only link to one or two constituencies. In such cases, foreign keys in the database linking rows in this table to rows in people dimensions will often be NULL. If you don't know what this means, or what it implies for queries, seek help from the data warehouse staff or Institutional Research and Assessment. Most fields in this table are not used and are therefore not exposed to users via views (VMF_SurveyCellData).

Check constraint name of the table MF_SurveyCellData

CKT_MF_SURVEYCELLDATA

List of outgoing references of the table MF_SurveyCellData

Relationship_12

MD_SurveyAdministration

MD_SurvAdm_SurrogateKey

Relationship_84

MD_Date (Shortcut)

MD_Date_DateInteger

Relationship_85

MD_DegreeStudent (Shortcut)

MD_CollPers_SurrogateKeyDStu

Relationship_85

MD_FinancialAidPerson (Shortcut)

MD__MD_CollPers_SurrogateKey3

Relationship_85

MD_Employee (Shortcut)

MD_CollPers_SurrogateKeyEmp

Relationship_85

MD_Prospect (Shortcut)

MD_Prosp_SurrogateKey

Relationship_85

MD_Alum (Shortcut)

MD_DevPers_SurrogateKey

Relationship_86

MD_SurveyQuestionAndAnswer

MD_SurvQA_SurrogateKey

Relationship_87

MD_Time (Shortcut)

MD_Time_TimeInteger

List of diagrams containing the table MF_SurveyCellData

StarsSurveyDiagram

List of columns of the table MF_SurveyCellData

MD_SurvQA_SurrogateKey

<None>

numeric

MD_SurvAdm_SurrogateKey

<None>

numeric

MD_Date_DateInteger

Integer representation of the date (e.g., 20070101 for 1 Jan 2007). Defaults to 99991231, for situations where a date is required but none is available.

DW_DateInteger

int

MD_Time_TimeInteger

Integer representing the time of day in the format HHMMSS (e.g., 23:01:01 at one minute one second after eleven o'clock in the evening). Not typically used for reporting.

Unlike MD_Date_Dateinteger (in MD_Date), this integer is not really human-readable, because it's not set up for base-10. Rather, it's calculated using the formula: (hour << 16) Or (min << 8) Or (sec), where the Or's are logical, and the << operator is a binary left shift.

<None>

int

MD_Prosp_SurrogateKey

<None>

numeric

MD_DevPers_SurrogateKey

<None>

numeric

MD_CollPers_SurrogateKeyEmp

<None>

numeric

MD_CollPers_SurrogateKeyDStu

<None>

numeric

MD__MD_CollPers_SurrogateKey3

<None>

numeric

MF_SData_PosNegOrNeutral

This field contains one of five values: Positive, Negative, Neutral, Not Applicable, or Unknown (the latter being the default).

The first three values may be used if responses to a survey question may be grouped further into positive, negative, or neutral. E.g., if a survey were to ask, "How do you feel about Carleton now that you've been kicked out for harassment?" and if possible answers were: great, OK, so-so, annoyed, really angry, we might group the first two (great and OK) as 'positive', so-so as 'neutral,' and so on.

The value "Not Applicable" is appropriate both for answers that are missing and answers to questions that can't be characterized as positive or negative.

This field is optional in the sense that there is no need to alter the default, Unknown. But by the same token it can be very useful for people creating pivot tables/crosstabs in BI tools and spreadsheets if we provide higher-level groupings for answers - which is what this field (if populated) does.

DW_CodeDescShort

varchar(16)

MF_SData_StatType

One of four keywords: Nominal, Ordinal, Interval, Ratio. Defaults to Nominal. In the data warehouse, answers are all recorded as text, so this field is supplied as a hint, to tell us when a type conversion (text -> float) might be helpful.

Normally this field should not be exposed to users. Most survey question responses are nominal - or are ordinal, but can be treated as nominal.

DW_CodeDescShort

varchar(16)

MF_SData_Weight

Not used currently. Would (if used) allow us to weight answers to questions on survey instruments. Defaults to 1. Do not expose to users. When and if we do use it, we should bundle it up in calculations.

Weight is calculated relative to other questions asked as part of the same survey instrument. It can't be attached to the question itself, because weights may vary from one version /instance of the instrument to another, while the questions may remain constant.

As of 2010 this attribute is not used, thank goodness. It would be unpleasantly complex to implement.

<None>

float

MF_SData_SQLDataType

Not currently used. Do not expose to users.

DW_CodeDesc

varchar(32)

MF_SData_Count

1 if a given subject answered a given question on a given survey. Zero otherwise.

This attribute's value is often used in calculations where the divisor is the total number of people answering a given question - the goal being to determine what proportion of people answering a given question answered it a certain way.

<None>

int

MF_SData_AuditKey

DW_AuditKey

bigint

MF_SData_Timestamp

<None>

timestamp