Table - dbo.DimStudentSuccessCohort

Column Name	Data Type	Allow Nulls	Row Guid	Fulltext Indexed	Identity	Default	Description / Computed Formula
AwardType	varchar(30)	False	False	False			Descriptive name for the type of award the student is pursuing for the cohort term.
AwardTypeCode	varchar(3)	False	False	False			Code indicating the type of award the student is pursuing at the time the student is captured for the student success cohort.
CohortTerm	varchar(6)	False	False	False			Time indication, relates to the academic calendar of the institution. The Cohort Term indicates when success tracking for the student is initiated. Also used as the business unique identifier for a term.
CohortTermTitle	varchar(30)	False	False	False			The title of the cohort term. The title consists of a literal description of the term, followed by the word 'Term' and then the academic year in the format of YYYY (Fall Term 2012).
FirstTerm_FT_PT	varchar(10)	False	False	False			Indication of the enrollment status of the student during their initial student success cohort term.
FirstYearTerm	varchar(6)	False	False	False			The First Year Term indicates the term that is just prior to one full year since the student's cohort term. For example, if the cohort term is term 20132, then the first year term is 20141. This is used for determining the student's status at the end of their first year at the college.
InitialPrepStatus	varchar(20)	False	False	False			Descriptive name for how prepared is the student to take college level course work when they first attend the institution.
LastTermCaptured	varchar(6)	False	False	False			Time indication, relates to the academic calendar of the institution. The Last Term Captured indicates the latest term we have for the student from the End of Term file.
LastTermCapturedTitle	varchar(30)	False	False	False			The title of the Last Term Captured. The title consists of a literal description of the term, followed by the word 'Term' and then the academic year in the format of YYYY (Fall Term 2012).
Program	varchar(30)	False	False	False			Descriptive title of the primary program objective for the student for the cohort term.
ProgramAdmitStatus	varchar(3)	False	False	False			Indication if the student was admited into their Program at the end of the student success cohort term.
ProgramCode	varchar(5)	False	False	False			Code indicating the program objective declared by the student at the time the Student is captured in the student success cohort.
RowEndDate	datetime(23, 3)	True	False	False			The effective end date-time for the current student success cohort row.
RowIsCurrent	char(1)	False	False	False			Indicator if the row is the most current representation of the student success cohort. The valid values are 'N' or 'Y'.
RowStartDate	datetime(23, 3)	False	False	False			The effective start date-time for the current student success cohort row.
StudentId	varchar(9)	False	False	False			Student identifier assigned by the source application and used by the Data Warehouse as the Business Key.
StudentSuccessCohortAK	varchar(15)	False	False	False			The unique identifier assigned to the Student Success Cohort, used by the data warehouse as the business key. Consists of the Student ID and Term
StudentSuccessCohortSK	int(10, 0)	False	False	False	(1 / 1)		Unique identifier for the Student Success Cohort in the data warehouse, in numeric format.

Name

Insert

Update

Delete

Instead

Name	Clustered	Columns
PK_DimStudentSuccessCohort	True	StudentSuccessCohortSK

Name

Columns

Referenced Table

Referenced Columns

Checked

Name	Type
dbo.FactStudentSuccess_FirstYearSnapshot	Table
dbo.FactStudentSuccess_TermSnapshot	Table

Name	Type
dbo.FactStudentSuccess_FirstYearSnapshot	TABLE
dbo.FactStudentSuccess_TermSnapshot	TABLE

Object	Property	Value
DimStudentSuccessCohort	MS_Description	The dimension will contain one row per credit student. The Cohort will capture information about the student as they initiate their degree seeking path at the institution, including initial program objective, FT vs. PT status and initial prep status. The student will be added to the cohort for the first major term at the institution when enrolled in one credit class (after high school graduation) when seeking a degree.
AwardType	MS_Description	Descriptive name for the type of award the student is pursuing for the cohort term.
AwardTypeCode	MS_Description	Code indicating the type of award the student is pursuing at the time the student is captured for the student success cohort.
CohortTerm	MS_Description	Time indication, relates to the academic calendar of the institution. The Cohort Term indicates when success tracking for the student is initiated. Also used as the business unique identifier for a term.
CohortTermTitle	MS_Description	The title of the cohort term. The title consists of a literal description of the term, followed by the word 'Term' and then the academic year in the format of YYYY (Fall Term 2012).
FirstTerm_FT_PT	MS_Description	Indication of the enrollment status of the student during their initial student success cohort term.
FirstYearTerm	MS_Description	The First Year Term indicates the term that is just prior to one full year since the student's cohort term. For example, if the cohort term is term 20132, then the first year term is 20141. This is used for determining the student's status at the end of their first year at the college.
InitialPrepStatus	MS_Description	Descriptive name for how prepared is the student to take college level course work when they first attend the institution.
LastTermCaptured	MS_Description	Time indication, relates to the academic calendar of the institution. The Last Term Captured indicates the latest term we have for the student from the End of Term file.
LastTermCapturedTitle	MS_Description	The title of the Last Term Captured. The title consists of a literal description of the term, followed by the word 'Term' and then the academic year in the format of YYYY (Fall Term 2012).
Program	MS_Description	Descriptive title of the primary program objective for the student for the cohort term.
ProgramAdmitStatus	MS_Description	Indication if the student was admited into their Program at the end of the student success cohort term.
ProgramCode	MS_Description	Code indicating the program objective declared by the student at the time the Student is captured in the student success cohort.
RowEndDate	MS_Description	The effective end date-time for the current student success cohort row.
RowIsCurrent	MS_Description	Indicator if the row is the most current representation of the student success cohort. The valid values are 'N' or 'Y'.
RowStartDate	MS_Description	The effective start date-time for the current student success cohort row.
StudentId	MS_Description	Student identifier assigned by the source application and used by the Data Warehouse as the Business Key.
StudentSuccessCohortAK	MS_Description	The unique identifier assigned to the Student Success Cohort, used by the data warehouse as the business key. Consists of the Student ID and Term
StudentSuccessCohortSK	MS_Description	Unique identifier for the Student Success Cohort in the data warehouse, in numeric format.

Object

Property

Value

/****** Object: Table [dbo].[DimStudentSuccessCohort] Script Date: 03/09/2017 17:14:56 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[DimStudentSuccessCohort](
    [StudentSuccessCohortSK] [int] IDENTITY(1,1) NOT NULL,
    [StudentSuccessCohortAK] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [StudentId] [varchar](9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [CohortTerm] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [CohortTermTitle] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [FirstYearTerm] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [LastTermCaptured] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [LastTermCapturedTitle] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [ProgramCode] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Program] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [ProgramAdmitStatus] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [AwardTypeCode] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [AwardType] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [InitialPrepStatus] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [FirstTerm_FT_PT] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [RowIsCurrent] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [RowStartDate] [datetime] NOT NULL,
    [RowEndDate] [datetime] NULL,
CONSTRAINT [PK_DimStudentSuccessCohort] PRIMARY KEY CLUSTERED
(
    [StudentSuccessCohortSK] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The dimension will contain one row per credit student. The Cohort will capture information about the student as they initiate their degree seeking path at the institution, including initial program objective, FT vs. PT status and initial prep status. The student will be added to the cohort for the first major term at the institution when enrolled in one credit class (after high school graduation) when seeking a degree.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimStudentSuccessCohort'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Descriptive name for the type of award the student is pursuing for the cohort term.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimStudentSuccessCohort', @level2type=N'COLUMN',@level2name=N'AwardType'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Code indicating the type of award the student is pursuing at the time the student is captured for the student success cohort.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimStudentSuccessCohort', @level2type=N'COLUMN',@level2name=N'AwardTypeCode'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Time indication, relates to the academic calendar of the institution. The Cohort Term indicates when success tracking for the student is initiated. Also used as the business unique identifier for a term.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimStudentSuccessCohort', @level2type=N'COLUMN',@level2name=N'CohortTerm'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The title of the cohort term. The title consists of a literal description of the term, followed by the word ''Term'' and then the academic year in the format of YYYY (Fall Term 2012).' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimStudentSuccessCohort', @level2type=N'COLUMN',@level2name=N'CohortTermTitle'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Indication of the enrollment status of the student during their initial student success cohort term.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimStudentSuccessCohort', @level2type=N'COLUMN',@level2name=N'FirstTerm_FT_PT'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The First Year Term indicates the term that is just prior to one full year since the student''s cohort term. For example, if the cohort term is term 20132, then the first year term is 20141. This is used for determining the student''s status at the end of their first year at the college.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimStudentSuccessCohort', @level2type=N'COLUMN',@level2name=N'FirstYearTerm'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Descriptive name for how prepared is the student to take college level course work when they first attend the institution.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimStudentSuccessCohort', @level2type=N'COLUMN',@level2name=N'InitialPrepStatus'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Time indication, relates to the academic calendar of the institution. The Last Term Captured indicates the latest term we have for the student from the End of Term file.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimStudentSuccessCohort', @level2type=N'COLUMN',@level2name=N'LastTermCaptured'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The title of the Last Term Captured. The title consists of a literal description of the term, followed by the word ''Term'' and then the academic year in the format of YYYY (Fall Term 2012).' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimStudentSuccessCohort', @level2type=N'COLUMN',@level2name=N'LastTermCapturedTitle'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Descriptive title of the primary program objective for the student for the cohort term.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimStudentSuccessCohort', @level2type=N'COLUMN',@level2name=N'Program'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Indication if the student was admited into their Program at the end of the student success cohort term.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimStudentSuccessCohort', @level2type=N'COLUMN',@level2name=N'ProgramAdmitStatus'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Code indicating the program objective declared by the student at the time the Student is captured in the student success cohort.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimStudentSuccessCohort', @level2type=N'COLUMN',@level2name=N'ProgramCode'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The effective end date-time for the current student success cohort row.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimStudentSuccessCohort', @level2type=N'COLUMN',@level2name=N'RowEndDate'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Indicator if the row is the most current representation of the student success cohort. The valid values are ''N'' or ''Y''.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimStudentSuccessCohort', @level2type=N'COLUMN',@level2name=N'RowIsCurrent'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The effective start date-time for the current student success cohort row.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimStudentSuccessCohort', @level2type=N'COLUMN',@level2name=N'RowStartDate'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Student identifier assigned by the source application and used by the Data Warehouse as the Business Key.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimStudentSuccessCohort', @level2type=N'COLUMN',@level2name=N'StudentId'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The unique identifier assigned to the Student Success Cohort, used by the data warehouse as the business key. Consists of the Student ID and Term' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimStudentSuccessCohort', @level2type=N'COLUMN',@level2name=N'StudentSuccessCohortAK'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Unique identifier for the Student Success Cohort in the data warehouse, in numeric format.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimStudentSuccessCohort', @level2type=N'COLUMN',@level2name=N'StudentSuccessCohortSK'

AwardType

AwardTypeCode

CohortTerm

CohortTermTitle

FirstTerm_FT_PT

FirstYearTerm

InitialPrepStatus

LastTermCaptured

LastTermCapturedTitle

Program

ProgramAdmitStatus

ProgramCode

RowIsCurrent

RowStartDate

StudentId

StudentSuccessCohortAK

StudentSuccessCohortSK

Associate In Arts Degree

20151

Fall Term 2014

Part-time

20153

Did Not Require Prep

20171

Fall Term 2016

Associate In Arts

Yes

1000

2017-03-09T12:18:00-05:00

V14806707

V1480670720151

57254

Associate In Arts Degree

20161

Fall Term 2015

Part-time

20163

Did Not Require Prep

20171

Fall Term 2016

Associate In Arts

Yes

1000

2017-03-09T12:18:00-05:00

J15039647

J1503964720161

41293

Associate In Arts Degree

20171

Fall Term 2016

Part-time

20173

Required Prep

20171

Fall Term 2016

Associate In Arts

Yes

1000

2017-03-09T12:18:00-05:00

S15314701

S1531470120171

56140

Associate In Arts Degree

20171

Fall Term 2016

Part-time

20173

Did Not Require Prep

20171

Fall Term 2016

Associate In Arts

Yes

1000

2017-03-09T12:18:00-05:00

M15294119

M1529411920171

49964

Associate In Arts Degree

20141

Fall Term 2013

Part-time

20143

Did Not Require Prep

20171

Fall Term 2016

Associate In Arts

Yes

1000

2017-03-09T12:18:00-05:00

J14432512

J1443251220141

39026

Associate In Arts Degree

20171

Fall Term 2016

Part-time

20173

Did Not Require Prep

20171

Fall Term 2016

Associate In Arts

Yes

1000

2017-03-09T12:18:00-05:00

G15419682

G1541968220171

35087

Associate In Arts Degree

20161

Fall Term 2015

Part-time

20163

Did Not Require Prep

20171

Fall Term 2016

Associate In Arts

Yes

1000

2017-03-09T12:18:00-05:00

J14973705

J1497370520161

38007

Associate In Arts Degree

20141

Fall Term 2013

Part-time

20143

Did Not Require Prep

20171

Fall Term 2016

Associate In Arts

Yes

1000

2017-03-09T12:18:00-05:00

A14552483

A1455248320141

25114

Associate In Arts Degree

20141

Fall Term 2013

Part-time

20143

Did Not Require Prep

20171

Fall Term 2016

Associate In Arts

Yes

1000

2017-03-09T12:18:00-05:00

D14422224

D1442222420141

32927

Associate In Arts Degree

20151

Fall Term 2014

Part-time

20153

Required Prep

20171

Fall Term 2016

Associate In Arts

Yes

1000

2017-03-09T12:18:00-05:00

R14836860

R1483686020151

51981

Property	Value
Name	DimStudentSuccessCohort
Schema	dbo
Row Count	37385
Data Size	7704 KB
Index Size	48 KB
Create Date	1/28/2014 9:47 AM
Description	The dimension will contain one row per credit student. The Cohort will capture information about the student as they initiate their degree seeking path at the institution, including initial program objective, FT vs. PT status and initial prep status. The student will be added to the cohort for the first major term at the institution when enrolled in one credit class (after high school graduation) when seeking a degree.