EnterpriseDW
 dbo.DimRetentionCohortType (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.DimRetentionCohortType
  Properties
Property Value
Name DimRetentionCohortType
Schema dbo
Row Count 6
Data Size 8 KB
Index Size 8 KB
Create Date 1/28/2014 9:47 AM
Description Categories of time comparisons for purposes of Retention tracking and measurements.
  Columns
 
 
Column Name
Data Type
Allow Nulls
Row Guid
Fulltext Indexed
Identity
Default
Description / Computed Formula
FromTermTypeCode
varchar(3)
False
False
False
 
The value from the Term that designates the term within the academic year indicating the From term of the retention comparison.
RetentionCohort
varchar(60)
False
False
False
 
Descriptive name for the type of Retention measurement.
RetentionCohortCode
varchar(15)
False
False
False
 
Unique identifier for the type of Retention measurement.
RetentionCohortTypeAK
varchar(15)
False
False
False
 
Business key
In Primary Key
RetentionCohortTypeSK
int(10, 0)
False
False
False
(1 / 1)
Surrogate primary key
RowEndDate
datetime(23, 3)
True
False
False
 
('12/31/9999')
When did this row become invalid? (12/31/9999 if current row)
RowIsCurrent
char(1)
False
False
False
 
Indication the current row within the dimension is the most up to date version.
RowStartDate
datetime(23, 3)
False
False
False
 
When did this row become valid for this member?
ToTermTypeCode
varchar(3)
False
False
False
 
The value from the Term that designates the term within the academic year indicating the To term of the retention comparison.
  Triggers
Name Insert Update Delete Instead
  Indexes
Name Clustered Columns
True
RetentionCohortTypeSK
  Foreign Key Constraints
Name Columns Referenced Table Referenced Columns Checked
  Check Constraints
Name Text Checked
  Parent Dependencies (objects that dbo.DimRetentionCohortType depends on)
Name Type
Table
  Child Dependencies (objects that depend on dbo.DimRetentionCohortType)
Name Type
TABLE
  Extended Properties
Object Property Value
DimRetentionCohortType
Display Name
RetCohortType
DimRetentionCohortType
MS_Description
Categories of time comparisons for purposes of Retention tracking and measurements.
DimRetentionCohortType
Table Type
Dimension
FromTermTypeCode
Display Name
From Term Type Code
FromTermTypeCode
Example Values
1,2,3
FromTermTypeCode
MS_Description
The value from the Term that designates the term within the academic year indicating the From term of the retention comparison.
FromTermTypeCode
SCD Type
1
FromTermTypeCode
Source System
Derived
RetentionCohort
Display Name
Retention Cohort
RetentionCohort
Example Values
Fall to Fall Retention by Year, Spring to Spring Retention by year, Fall to Spring Retention by Term, Spring to Fall Retention b
RetentionCohort
MS_Description
Descriptive name for the type of Retention measurement.
RetentionCohort
SCD Type
1
RetentionCohort
Source System
Derived
RetentionCohortCode
Display Name
Retontion Cohort Code
RetentionCohortCode
Example Values
FallToFall, SpringToSring, FallToSpring, SpringToSummer and SringToFall
RetentionCohortCode
MS_Description
Unique identifier for the type of Retention measurement.
RetentionCohortCode
SCD Type
key
RetentionCohortCode
Source System
Derived
RetentionCohortTypeAK
Display Name
RetontionCohortTypeAK
RetentionCohortTypeAK
Example Values
FallToFall, SpringToSring, FallToSpring, SpringToSummer and SringToFall
RetentionCohortTypeAK
MS_Description
Business key
RetentionCohortTypeAK
SCD Type
key
RetentionCohortTypeAK
Source System
Derived
RetentionCohortTypeSK
Display Name
RetentionCohortTypSK
RetentionCohortTypeSK
Example Values
1, 2, 3?
RetentionCohortTypeSK
MS_Description
Surrogate primary key
RetentionCohortTypeSK
SCD Type
key
RetentionCohortTypeSK
Source System
Derived
RowEndDate
Display Folder
Exclude from cube
RowEndDate
Display Name
Row End Date
RowEndDate
Example Values
1/14/1998, 12/31/9999
RowEndDate
MS_Description
When did this row become invalid? (12/31/9999 if current row)
RowEndDate
SCD Type
n/a
RowEndDate
Source System
Derived
RowIsCurrent
Display Folder
Exclude from cube
RowIsCurrent
Display Name
Row Is Current
RowIsCurrent
Example Values
Y, N
RowIsCurrent
MS_Description
Indication the current row within the dimension is the most up to date version.
RowIsCurrent
SCD Type
n/a
RowIsCurrent
Source System
Derived
RowStartDate
Display Folder
Exclude from cube
RowStartDate
Display Name
Row Start Date
RowStartDate
Example Values
1/24/2011
RowStartDate
MS_Description
When did this row become valid for this member?
RowStartDate
SCD Type
n/a
RowStartDate
Source System
Derived
ToTermTypeCode
Display Name
To Term Type Code
ToTermTypeCode
Example Values
1,2,3
ToTermTypeCode
MS_Description
The value from the Term that designates the term within the academic year indicating the To term of the retention comparison.
ToTermTypeCode
SCD Type
1
ToTermTypeCode
Source System
Derived
   Annotations
Object Property Value
  DDL
/****** Object: Table [dbo].[DimRetentionCohortType] Script Date: 03/09/2017 17:14:56 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[DimRetentionCohortType](
    [RetentionCohortTypeSK] [int] IDENTITY(1,1) NOT NULL,
    [RetentionCohortTypeAK] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [RetentionCohortCode] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [RetentionCohort] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [FromTermTypeCode] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [ToTermTypeCode] [varchar](3) 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_dbo.DimRetentionCohortType] PRIMARY KEY CLUSTERED 
(
    [RetentionCohortTypeSK] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [dbo].[DimRetentionCohortType] ADD  CONSTRAINT [DF__DimRetent__RowEn__6A85CC04]  DEFAULT ('12/31/9999') FOR [RowEndDate]


EXEC sys.sp_addextendedproperty @name=N'Display Name', @value=N'RetCohortType' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimRetentionCohortType'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Categories of time comparisons for purposes of Retention tracking and measurements.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimRetentionCohortType'


EXEC sys.sp_addextendedproperty @name=N'Table Type', @value=N'Dimension' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimRetentionCohortType'


EXEC sys.sp_addextendedproperty @name=N'Display Name', @value=N'From Term Type Code' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimRetentionCohortType', @level2type=N'COLUMN',@level2name=N'FromTermTypeCode'


EXEC sys.sp_addextendedproperty @name=N'Example Values', @value=N'1,2,3' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimRetentionCohortType', @level2type=N'COLUMN',@level2name=N'FromTermTypeCode'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The value from the Term that designates the term within the academic year indicating the From term of the retention comparison. ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimRetentionCohortType', @level2type=N'COLUMN',@level2name=N'FromTermTypeCode'


EXEC sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimRetentionCohortType', @level2type=N'COLUMN',@level2name=N'FromTermTypeCode'


EXEC sys.sp_addextendedproperty @name=N'Source System', @value=N'Derived' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimRetentionCohortType', @level2type=N'COLUMN',@level2name=N'FromTermTypeCode'


EXEC sys.sp_addextendedproperty @name=N'Display Name', @value=N'Retention Cohort' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimRetentionCohortType', @level2type=N'COLUMN',@level2name=N'RetentionCohort'


EXEC sys.sp_addextendedproperty @name=N'Example Values', @value=N'Fall to Fall Retention by Year, Spring to Spring Retention by year, Fall to Spring Retention by Term, Spring to Fall Retention b' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimRetentionCohortType', @level2type=N'COLUMN',@level2name=N'RetentionCohort'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Descriptive name for the type of Retention measurement.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimRetentionCohortType', @level2type=N'COLUMN',@level2name=N'RetentionCohort'


EXEC sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimRetentionCohortType', @level2type=N'COLUMN',@level2name=N'RetentionCohort'


EXEC sys.sp_addextendedproperty @name=N'Source System', @value=N'Derived' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimRetentionCohortType', @level2type=N'COLUMN',@level2name=N'RetentionCohort'


EXEC sys.sp_addextendedproperty @name=N'Display Name', @value=N'Retontion Cohort Code' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimRetentionCohortType', @level2type=N'COLUMN',@level2name=N'RetentionCohortCode'


EXEC sys.sp_addextendedproperty @name=N'Example Values', @value=N'FallToFall, SpringToSring, FallToSpring, SpringToSummer and SringToFall' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimRetentionCohortType', @level2type=N'COLUMN',@level2name=N'RetentionCohortCode'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Unique identifier for the type of Retention measurement.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimRetentionCohortType', @level2type=N'COLUMN',@level2name=N'RetentionCohortCode'


EXEC sys.sp_addextendedproperty @name=N'SCD Type', @value=N'key' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimRetentionCohortType', @level2type=N'COLUMN',@level2name=N'RetentionCohortCode'


EXEC sys.sp_addextendedproperty @name=N'Source System', @value=N'Derived' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimRetentionCohortType', @level2type=N'COLUMN',@level2name=N'RetentionCohortCode'


EXEC sys.sp_addextendedproperty @name=N'Display Name', @value=N'RetontionCohortTypeAK' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimRetentionCohortType', @level2type=N'COLUMN',@level2name=N'RetentionCohortTypeAK'


EXEC sys.sp_addextendedproperty @name=N'Example Values', @value=N'FallToFall, SpringToSring, FallToSpring, SpringToSummer and SringToFall' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimRetentionCohortType', @level2type=N'COLUMN',@level2name=N'RetentionCohortTypeAK'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Business key' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimRetentionCohortType', @level2type=N'COLUMN',@level2name=N'RetentionCohortTypeAK'


EXEC sys.sp_addextendedproperty @name=N'SCD Type', @value=N'key' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimRetentionCohortType', @level2type=N'COLUMN',@level2name=N'RetentionCohortTypeAK'


EXEC sys.sp_addextendedproperty @name=N'Source System', @value=N'Derived' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimRetentionCohortType', @level2type=N'COLUMN',@level2name=N'RetentionCohortTypeAK'


EXEC sys.sp_addextendedproperty @name=N'Display Name', @value=N'RetentionCohortTypSK' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimRetentionCohortType', @level2type=N'COLUMN',@level2name=N'RetentionCohortTypeSK'


EXEC sys.sp_addextendedproperty @name=N'Example Values', @value=N'1, 2, 3?' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimRetentionCohortType', @level2type=N'COLUMN',@level2name=N'RetentionCohortTypeSK'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Surrogate primary key' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimRetentionCohortType', @level2type=N'COLUMN',@level2name=N'RetentionCohortTypeSK'


EXEC sys.sp_addextendedproperty @name=N'SCD Type', @value=N'key' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimRetentionCohortType', @level2type=N'COLUMN',@level2name=N'RetentionCohortTypeSK'


EXEC sys.sp_addextendedproperty @name=N'Source System', @value=N'Derived' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimRetentionCohortType', @level2type=N'COLUMN',@level2name=N'RetentionCohortTypeSK'


EXEC sys.sp_addextendedproperty @name=N'Display Folder', @value=N'Exclude from cube' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimRetentionCohortType', @level2type=N'COLUMN',@level2name=N'RowEndDate'


EXEC sys.sp_addextendedproperty @name=N'Display Name', @value=N'Row End Date' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimRetentionCohortType', @level2type=N'COLUMN',@level2name=N'RowEndDate'


EXEC sys.sp_addextendedproperty @name=N'Example Values', @value=N'1/14/1998, 12/31/9999' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimRetentionCohortType', @level2type=N'COLUMN',@level2name=N'RowEndDate'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'When did this row become invalid? (12/31/9999 if current row)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimRetentionCohortType', @level2type=N'COLUMN',@level2name=N'RowEndDate'


EXEC sys.sp_addextendedproperty @name=N'SCD Type', @value=N'n/a' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimRetentionCohortType', @level2type=N'COLUMN',@level2name=N'RowEndDate'


EXEC sys.sp_addextendedproperty @name=N'Source System', @value=N'Derived' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimRetentionCohortType', @level2type=N'COLUMN',@level2name=N'RowEndDate'


EXEC sys.sp_addextendedproperty @name=N'Display Folder', @value=N'Exclude from cube' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimRetentionCohortType', @level2type=N'COLUMN',@level2name=N'RowIsCurrent'


EXEC sys.sp_addextendedproperty @name=N'Display Name', @value=N'Row Is Current' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimRetentionCohortType', @level2type=N'COLUMN',@level2name=N'RowIsCurrent'


EXEC sys.sp_addextendedproperty @name=N'Example Values', @value=N'Y, N' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimRetentionCohortType', @level2type=N'COLUMN',@level2name=N'RowIsCurrent'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Indication the current row within the dimension is the most up to date version.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimRetentionCohortType', @level2type=N'COLUMN',@level2name=N'RowIsCurrent'


EXEC sys.sp_addextendedproperty @name=N'SCD Type', @value=N'n/a' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimRetentionCohortType', @level2type=N'COLUMN',@level2name=N'RowIsCurrent'


EXEC sys.sp_addextendedproperty @name=N'Source System', @value=N'Derived' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimRetentionCohortType', @level2type=N'COLUMN',@level2name=N'RowIsCurrent'


EXEC sys.sp_addextendedproperty @name=N'Display Folder', @value=N'Exclude from cube' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimRetentionCohortType', @level2type=N'COLUMN',@level2name=N'RowStartDate'


EXEC sys.sp_addextendedproperty @name=N'Display Name', @value=N'Row Start Date' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimRetentionCohortType', @level2type=N'COLUMN',@level2name=N'RowStartDate'


EXEC sys.sp_addextendedproperty @name=N'Example Values', @value=N'1/24/2011' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimRetentionCohortType', @level2type=N'COLUMN',@level2name=N'RowStartDate'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'When did this row become valid for this member?' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimRetentionCohortType', @level2type=N'COLUMN',@level2name=N'RowStartDate'


EXEC sys.sp_addextendedproperty @name=N'SCD Type', @value=N'n/a' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimRetentionCohortType', @level2type=N'COLUMN',@level2name=N'RowStartDate'


EXEC sys.sp_addextendedproperty @name=N'Source System', @value=N'Derived' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimRetentionCohortType', @level2type=N'COLUMN',@level2name=N'RowStartDate'


EXEC sys.sp_addextendedproperty @name=N'Display Name', @value=N'To Term Type Code' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimRetentionCohortType', @level2type=N'COLUMN',@level2name=N'ToTermTypeCode'


EXEC sys.sp_addextendedproperty @name=N'Example Values', @value=N'1,2,3' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimRetentionCohortType', @level2type=N'COLUMN',@level2name=N'ToTermTypeCode'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The value from the Term that designates the term within the academic year indicating the To term of the retention comparison. ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimRetentionCohortType', @level2type=N'COLUMN',@level2name=N'ToTermTypeCode'


EXEC sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimRetentionCohortType', @level2type=N'COLUMN',@level2name=N'ToTermTypeCode'


EXEC sys.sp_addextendedproperty @name=N'Source System', @value=N'Derived' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimRetentionCohortType', @level2type=N'COLUMN',@level2name=N'ToTermTypeCode'
  Sample Data
FromTermTypeCode RetentionCohort RetentionCohortCode RetentionCohortTypeAK RetentionCohortTypeSK RowEndDate RowIsCurrent RowStartDate ToTermTypeCode
2 Spring To Fall Term Retention SpringToFall SpringToFall 5 9999-12-31T00:00:00-05:00 Y 1899-12-31T00:00:00-05:00 1
1 Fall To Fall Retention by Year FallToFall FallToFall 2 9999-12-31T00:00:00-05:00 Y 1899-12-31T00:00:00-05:00 1
N/A N/A -1 -1 -1 9999-12-31T00:00:00-05:00 Y 1899-12-31T00:00:00-05:00 N/A
2 Spring To Summer Term Retention SpringToSummer SpringToSummer 6 9999-12-31T00:00:00-05:00 Y 1899-12-31T00:00:00-05:00 3
2 Spring To Spring Retention by Year SpringToSpring SpringToSpring 3 9999-12-31T00:00:00-05:00 Y 1899-12-31T00:00:00-05:00 2
1 Fall To Spring Term Retention FallToSpring FallToSpring 4 9999-12-31T00:00:00-05:00 Y 1899-12-31T00:00:00-05:00 2
Powered by BI Documenter