EnterpriseDW
 dbo.DimTerm (Table)
Home  Diagrams  Documents  Images  EnterpriseDW  Storage  Users  Roles  Schemas  Tables  Views  Indexes  Triggers  Stored Procedures  Functions  Data Types  XML Schemas  Rules  Defaults  Fulltext Catalogs  Synonyms  Keys  Partitions  Assemblies 
Current Path:  EnterpriseDW   Tables   dbo.DimTerm
  Properties
Property Value
Name DimTerm
Schema dbo
Row Count 302
Data Size 48 KB
Index Size 16 KB
Create Date 8/28/2013 9:53 AM
Description The Term Dimension contains information including title, start date and end date for session '1' for the term. There is one row per term
  Columns
 
 
Column Name
Data Type
Allow Nulls
Row Guid
Fulltext Indexed
Identity
Default
Description / Computed Formula
AcademicYear
varchar(30)
False
False
False
 
An expanded literal label for the academic year (Academic Year 2012).
AcademicYearCode
varchar(7)
False
False
False
 
A literal representation of the Academic Year on the format CCYY-YY. Samples are '2010-11' and '2011-12'.
ActiveTerm
varchar(3)
False
False
False
 
An indication the term is an 'Active' term. A term is 'Active' is the start date of the term is within one year of the current or processing date. The valid values are 'Yes' or 'No'.
DefaultTerm
varchar(3)
False
False
False
 
An indication the term is the 'Default' term. A term is the 'Default' if the start date of the term is the closest to the current or processing date. The valid values are 'Yes' or 'No'. The 'Default' term is the term that will be used to display enrollment data on dashboards and reports.
PriorYearTerm
varchar(6)
False
False
False
 
The Term identifier of the Prior Year Term, to be used for comparison of year over year values.
RelativeDay
int(10, 0)
False
False
False
 
A numeric value which indicates the current or processing day as calculated from the start date of the term. The smaller the number, the closer the day is to the start of the term. Negative numbers indicate the day is prior to the start of the term.
RowEndDate
datetime(23, 3)
True
False
False
 
The effective end date-time for the current Term row.
RowIsCurrent
char(1)
False
False
False
 
Indicator if the row is the most current representation of the day. The valid values are 'N' or 'Y'.
RowStartDate
datetime(23, 3)
False
False
False
 
The effective start date-time for the current Term row.
StateReportingTermSequence
tinyint(3, 0)
False
False
False
 
A numeric representation of the term sequence within the state reporting year. The state reporting year starts in Summer term which is sequence 1.
StateReportingTermYear
varchar(4)
False
False
False
 
A numeric representation of the state reporting year for the term. The state reporting year starts in Summer term. The format is CCYY.
TermAK
int(10, 0)
False
False
False
 
The business unique identifier for the term. The format is YYYYTT and sample values are 20111, 20113 and 20122.
TermEndDate
date(10, 0)
False
False
False
 
The end date of the term from the control session, session '1', in the 'date' format.
In Primary Key
TermSK
int(10, 0)
False
False
False
 
Primary unique identifier for the term. The format is YYYYTT, samples are 20111, 20112 or 20123.
TermStartDate
date(10, 0)
False
False
False
 
The start date of the term from the control session, session '1', in the 'date' format.
TermTitle
varchar(30)
False
False
False
 
The title of the 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).
TermType
varchar(15)
False
False
False
 
Literal label for the term. Consists of the term literal followed by the word 'Term' (Summer Term).
TermTypeCode
varchar(3)
False
False
False
 
Numeric representation for the term, the values are 1 through 4.
TermYear
varchar(4)
False
False
False
 
A numeric representation of the academic year for the term. The format is CCYY.
  Triggers
Name Insert Update Delete Instead
  Indexes
Name Clustered Columns
True
TermSK
  Foreign Key Constraints
Name Columns Referenced Table Referenced Columns Checked
  Check Constraints
Name Text Checked
  Parent Dependencies (objects that dbo.DimTerm depends on)
Name Type
Table
Table
Table
Table
Table
Table
Table
Table
Table
Table
Table
Table
Table
Table
Table
  Child Dependencies (objects that depend on dbo.DimTerm)
Name Type
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
  Extended Properties
Object Property Value
DimTerm
MS_Description
The Term Dimension contains information including title, start date and end date for session '1' for the term. There is one row per term
AcademicYear
MS_Description
An expanded literal label for the academic year (Academic Year 2012).
AcademicYearCode
MS_Description
A literal representation of the Academic Year on the format CCYY-YY. Samples are '2010-11' and '2011-12'.
ActiveTerm
MS_Description
An indication the term is an 'Active' term. A term is 'Active' is the start date of the term is within one year of the current or processing date. The valid values are 'Yes' or 'No'.
DefaultTerm
MS_Description
An indication the term is the 'Default' term. A term is the 'Default' if the start date of the term is the closest to the current or processing date. The valid values are 'Yes' or 'No'. The 'Default' term is the term that will be used to display enrollment data on dashboards and reports.
PriorYearTerm
MS_Description
The Term identifier of the Prior Year Term, to be used for comparison of year over year values.
RelativeDay
MS_Description
A numeric value which indicates the current or processing day as calculated from the start date of the term. The smaller the number, the closer the day is to the start of the term. Negative numbers indicate the day is prior to the start of the term.
RowEndDate
MS_Description
The effective end date-time for the current Term row.
RowIsCurrent
MS_Description
Indicator if the row is the most current representation of the day. The valid values are 'N' or 'Y'.
RowStartDate
MS_Description
The effective start date-time for the current Term row.
StateReportingTermSequence
MS_Description
A numeric representation of the term sequence within the state reporting year. The state reporting year starts in Summer term which is sequence 1.
StateReportingTermYear
MS_Description
A numeric representation of the state reporting year for the term. The state reporting year starts in Summer term. The format is CCYY.
TermAK
MS_Description
The business unique identifier for the term. The format is YYYYTT and sample values are 20111, 20113 and 20122.
TermEndDate
MS_Description
The end date of the term from the control session, session '1', in the 'date' format.
TermSK
MS_Description
Primary unique identifier for the term. The format is YYYYTT, samples are 20111, 20112 or 20123.
TermStartDate
MS_Description
The start date of the term from the control session, session '1', in the 'date' format.
TermTitle
MS_Description
The title of the 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).
TermType
MS_Description
Literal label for the term. Consists of the term literal followed by the word 'Term' (Summer Term).
TermTypeCode
MS_Description
Numeric representation for the term, the values are 1 through 4.
TermYear
MS_Description
A numeric representation of the academic year for the term. The format is CCYY.
   Annotations
Object Property Value
  DDL
/****** Object: Table [dbo].[DimTerm] Script Date: 03/09/2017 17:14:57 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[DimTerm](
    [TermSK] [int] NOT NULL,
    [TermAK] [int] NOT NULL,
    [TermStartDate] [date] NOT NULL,
    [TermEndDate] [date] NOT NULL,
    [TermTitle] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [AcademicYearCode] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [AcademicYear] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [TermYear] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [TermTypeCode] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [TermType] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [ActiveTerm] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [DefaultTerm] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [RelativeDay] [int] NOT NULL,
    [PriorYearTerm] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [StateReportingTermYear] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [StateReportingTermSequence] [tinyint] NOT NULL,
    [RowIsCurrent] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [RowStartDate] [datetime] NOT NULL,
    [RowEndDate] [datetime] NULL,
 CONSTRAINT [PK_dbo_DimTerm] PRIMARY KEY CLUSTERED 
(
    [TermSK] 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 Term Dimension contains information including title, start date and end date for session ''1'' for the term. There is one row per term' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimTerm'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'An expanded literal label for the academic year (Academic Year 2012).' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimTerm', @level2type=N'COLUMN',@level2name=N'AcademicYear'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'A literal representation of the Academic Year on the format CCYY-YY. Samples are ''2010-11'' and ''2011-12''.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimTerm', @level2type=N'COLUMN',@level2name=N'AcademicYearCode'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'An indication the term is an ''Active'' term. A term is ''Active'' is the start date of the term is within one year of the current or processing date. The valid values are ''Yes'' or ''No''.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimTerm', @level2type=N'COLUMN',@level2name=N'ActiveTerm'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'An indication the term is the ''Default'' term. A term is the ''Default'' if the start date of the term is the closest to the current or processing date. The valid values are ''Yes'' or ''No''. The ''Default'' term is the term that will be used to display enrollment data on dashboards and reports.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimTerm', @level2type=N'COLUMN',@level2name=N'DefaultTerm'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The Term identifier of the Prior Year Term, to be used for comparison of year over year values. ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimTerm', @level2type=N'COLUMN',@level2name=N'PriorYearTerm'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'A numeric value which indicates the current or processing day as calculated from the start date of the term. The smaller the number, the closer the day is to the start of the term. Negative numbers indicate the day is prior to the start of the term.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimTerm', @level2type=N'COLUMN',@level2name=N'RelativeDay'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The effective end date-time for the current Term row.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimTerm', @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 day. The valid values are ''N'' or ''Y''.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimTerm', @level2type=N'COLUMN',@level2name=N'RowIsCurrent'


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


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'A numeric representation of the term sequence within the state reporting year. The state reporting year starts in Summer term which is sequence 1.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimTerm', @level2type=N'COLUMN',@level2name=N'StateReportingTermSequence'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'A numeric representation of the state reporting year for the term. The state reporting year starts in Summer term. The format is CCYY.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimTerm', @level2type=N'COLUMN',@level2name=N'StateReportingTermYear'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The business unique identifier for the term. The format is YYYYTT and sample values are 20111, 20113 and 20122.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimTerm', @level2type=N'COLUMN',@level2name=N'TermAK'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The end date of the term from the control session, session ''1'', in the ''date'' format.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimTerm', @level2type=N'COLUMN',@level2name=N'TermEndDate'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Primary unique identifier for the term. The format is YYYYTT, samples are 20111, 20112 or 20123.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimTerm', @level2type=N'COLUMN',@level2name=N'TermSK'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The start date of the term from the control session, session ''1'', in the ''date'' format.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimTerm', @level2type=N'COLUMN',@level2name=N'TermStartDate'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The title of the 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'DimTerm', @level2type=N'COLUMN',@level2name=N'TermTitle'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Literal label for the term. Consists of the term literal followed by the word ''Term'' (Summer Term).' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimTerm', @level2type=N'COLUMN',@level2name=N'TermType'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Numeric representation for the term, the values are 1 through 4.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimTerm', @level2type=N'COLUMN',@level2name=N'TermTypeCode'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'A numeric representation of the academic year for the term. The format is CCYY.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimTerm', @level2type=N'COLUMN',@level2name=N'TermYear'
  Sample Data
AcademicYear AcademicYearCode ActiveTerm DefaultTerm PriorYearTerm RelativeDay RowIsCurrent RowStartDate StateReportingTermSequence StateReportingTermYear TermAK TermEndDate TermSK TermStartDate TermTitle TermType TermTypeCode TermYear
Academic Year 1969-70 1969-70 No No 19692 17227 Y 2017-03-09T11:13:00-05:00 3 1970 19702 1970-05-06T00:00:00-04:00 19702 1970-01-08T00:00:00-05:00 Winter Term 1970 Spring Term 2 1970
Academic Year 1941-42 1941-42 No No 19411 27567 Y 2017-03-09T11:13:00-05:00 2 1942 19421 1942-01-30T00:00:00-05:00 19421 1941-09-17T00:00:00-04:00 Fall Term 1941 Fall Term 1 1942
N/A N/A N/A N/A N/A -999 Y 1900-01-01T00:00:00-05:00 0 N/A -1 1900-01-01T00:00:00-05:00 -1 1900-01-01T00:00:00-05:00 N/A N/A N/A N/A
Academic Year 1938-39 1938-39 No No 19382 28528 Y 2017-03-09T11:13:00-05:00 3 1939 19392 1939-06-03T00:00:00-04:00 19392 1939-01-30T00:00:00-05:00 Winter Term 1939 Spring Term 2 1939
Academic Year 1952-53 1952-53 No No 19521 23555 Y 2017-03-09T11:13:00-05:00 2 1953 19531 1953-01-28T00:00:00-05:00 19531 1952-09-11T00:00:00-04:00 Fall Term 1952 Fall Term 1 1953
Academic Year 1936-37 1936-37 No No 19361 29396 Y 2017-03-09T11:13:00-05:00 3 1937 19371 1937-01-28T00:00:00-05:00 19371 1936-09-14T00:00:00-04:00 Fall Term 1936 Fall Term 1 1937
Academic Year 1990-91 1990-91 No No 19904 9389 Y 2017-03-09T11:13:00-05:00 2 1992 19914 1991-08-06T00:00:00-04:00 19914 1991-06-25T00:00:00-04:00 Summer Term 1991 Sum 2 Term 4 1991
Academic Year 1987-88 1987-88 No No 19872 10655 Y 2017-03-09T11:13:00-05:00 4 1988 19882 1988-05-03T00:00:00-04:00 19882 1988-01-06T00:00:00-05:00 Winter Term 1988 Spring Term 2 1988
Academic Year 1974-75 1974-75 No No 19743 15277 Y 2017-03-09T11:13:00-05:00 1 1976 19753 1975-06-20T00:00:00-04:00 19753 1975-05-12T00:00:00-04:00 Spring Term 1975 Summer Term 3 1975
Academic Year 1998-99 1998-99 No No 19983 6506 Y 2017-03-09T11:13:00-05:00 1 2000 19993 1999-08-10T00:00:00-04:00 19993 1999-05-17T00:00:00-04:00 Summer Term 1999 Summer Term 3 1999
Powered by BI Documenter