EnterpriseDW
 dbo.DimZipCounty (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.DimZipCounty
  Properties
Property Value
Name DimZipCounty
Schema dbo
Row Count 41259
Data Size 3200 KB
Index Size 24 KB
Create Date 1/28/2014 9:47 AM
Description
  Columns
 
 
Column Name
Data Type
Allow Nulls
Row Guid
Fulltext Indexed
Identity
Default
Description / Computed Formula
City
varchar(35)
False
False
False
 
The main city associated with the Zip Code
County
varchar(45)
False
False
False
 
The name of the county
CountyFIPS
varchar(5)
False
False
False
 
The FIPS code associated with a County. Used for matching County instead of using Name
RowEndDate
datetime(23, 3)
True
False
False
 
When did this row become invalid? (12/31/9999 if current row)
RowIsCurrent
char(1)
False
False
False
 
Is this the current row for this member (Y/N)?
RowStartDate
datetime(23, 3)
False
False
False
 
When did this row become valid for this member?
State
varchar(3)
False
False
False
 
The State abbreviation associated with the ZipCode
ZipCode
varchar(5)
False
False
False
 
The first 5 digits of the zip code
ZipCodeAK
varchar(5)
False
False
False
 
Business Key for ZipCode
In Primary Key
ZipCountySK
int(10, 0)
False
False
False
(1 / 1)
Surrogate primary key
  Triggers
Name Insert Update Delete Instead
  Indexes
Name Clustered Columns
True
ZipCountySK
  Foreign Key Constraints
Name Columns Referenced Table Referenced Columns Checked
  Check Constraints
Name Text Checked
  Parent Dependencies (objects that dbo.DimZipCounty depends on)
Name Type
Table
Table
  Child Dependencies (objects that depend on dbo.DimZipCounty)
Name Type
TABLE
TABLE
  Extended Properties
Object Property Value
DimZipCounty
Display Name
ZipCounty
DimZipCounty
Table Description
This table will contain a lookup of ZipCode and County information that will link zipcodes to counties.
DimZipCounty
Table Type
Dimension
City
Display Name
City
City
Example Values
Fort Lauderdale, Miami
City
MS_Description
The main city associated with the Zip Code
City
SCD Type
1
City
Source System
Derived
County
Display Name
County
County
Example Values
Broward
County
MS_Description
The name of the county
County
SCD Type
1
CountyFIPS
Display Name
CountyFIPS
CountyFIPS
Example Values
112
CountyFIPS
MS_Description
The FIPS code associated with a County. Used for matching County instead of using Name
CountyFIPS
SCD Type
1
RowEndDate
Display Folder
Exclude from cube
RowEndDate
Display Name
Row End Date
RowEndDate
ETL Rules
Standard SCD-2
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
ETL Rules
Standard SCD-2
RowIsCurrent
Example Values
Y, N
RowIsCurrent
MS_Description
Is this the current row for this member (Y/N)?
RowIsCurrent
SCD Type
n/a
RowIsCurrent
Source System
Derived
RowStartDate
Display Folder
Exclude from cube
RowStartDate
Display Name
Row Start Date
RowStartDate
ETL Rules
Standard SCD-2
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
State
Display Name
State
State
Example Values
FL
State
MS_Description
The State abbreviation associated with the ZipCode
State
SCD Type
1
ZipCode
Display Name
ZipCode
ZipCode
Example Values
33308
ZipCode
MS_Description
The first 5 digits of the zip code
ZipCode
SCD Type
1
ZipCodeAK
Display Name
ZipCodeAK
ZipCodeAK
Example Values
33308-2413
ZipCodeAK
MS_Description
Business Key for ZipCode
ZipCodeAK
SCD Type
key
ZipCountySK
Display Name
ZipCountySK
ZipCountySK
Example Values
1, 2, 3?
ZipCountySK
MS_Description
Surrogate primary key
ZipCountySK
SCD Type
key
ZipCountySK
Source System
Derived
   Annotations
Object Property Value
  DDL
/****** Object: Table [dbo].[DimZipCounty] Script Date: 03/09/2017 17:14:57 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[DimZipCounty](
    [ZipCountySK] [int] IDENTITY(1,1) NOT NULL,
    [ZipCodeAK] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [ZipCode] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [CountyFIPS] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [County] [varchar](45) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [City] [varchar](35) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [State] [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.DimZipCounty] PRIMARY KEY CLUSTERED 
(
    [ZipCountySK] 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'Display Name', @value=N'ZipCounty' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimZipCounty'


EXEC sys.sp_addextendedproperty @name=N'Table Description', @value=N'This table will contain a lookup of ZipCode and County information that will link zipcodes to counties. ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimZipCounty'


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


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


EXEC sys.sp_addextendedproperty @name=N'Example Values', @value=N'Fort Lauderdale, Miami' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimZipCounty', @level2type=N'COLUMN',@level2name=N'City'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The main city associated with the Zip Code' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimZipCounty', @level2type=N'COLUMN',@level2name=N'City'


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


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


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


EXEC sys.sp_addextendedproperty @name=N'Example Values', @value=N'Broward' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimZipCounty', @level2type=N'COLUMN',@level2name=N'County'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The name of the county' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimZipCounty', @level2type=N'COLUMN',@level2name=N'County'


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


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


EXEC sys.sp_addextendedproperty @name=N'Example Values', @value=N'112' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimZipCounty', @level2type=N'COLUMN',@level2name=N'CountyFIPS'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The FIPS code associated with a County. Used for matching County instead of using Name' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimZipCounty', @level2type=N'COLUMN',@level2name=N'CountyFIPS'


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


EXEC sys.sp_addextendedproperty @name=N'Display Folder', @value=N'Exclude from cube' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimZipCounty', @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'DimZipCounty', @level2type=N'COLUMN',@level2name=N'RowEndDate'


EXEC sys.sp_addextendedproperty @name=N'ETL Rules', @value=N'Standard SCD-2' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimZipCounty', @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'DimZipCounty', @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'DimZipCounty', @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'DimZipCounty', @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'DimZipCounty', @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'DimZipCounty', @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'DimZipCounty', @level2type=N'COLUMN',@level2name=N'RowIsCurrent'


EXEC sys.sp_addextendedproperty @name=N'ETL Rules', @value=N'Standard SCD-2' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimZipCounty', @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'DimZipCounty', @level2type=N'COLUMN',@level2name=N'RowIsCurrent'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Is this the current row for this member (Y/N)?' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimZipCounty', @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'DimZipCounty', @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'DimZipCounty', @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'DimZipCounty', @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'DimZipCounty', @level2type=N'COLUMN',@level2name=N'RowStartDate'


EXEC sys.sp_addextendedproperty @name=N'ETL Rules', @value=N'Standard SCD-2' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimZipCounty', @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'DimZipCounty', @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'DimZipCounty', @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'DimZipCounty', @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'DimZipCounty', @level2type=N'COLUMN',@level2name=N'RowStartDate'


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


EXEC sys.sp_addextendedproperty @name=N'Example Values', @value=N'FL' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimZipCounty', @level2type=N'COLUMN',@level2name=N'State'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The State abbreviation associated with the ZipCode' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimZipCounty', @level2type=N'COLUMN',@level2name=N'State'


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


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


EXEC sys.sp_addextendedproperty @name=N'Example Values', @value=N'33308' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimZipCounty', @level2type=N'COLUMN',@level2name=N'ZipCode'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The first 5 digits of the zip code' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimZipCounty', @level2type=N'COLUMN',@level2name=N'ZipCode'


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


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


EXEC sys.sp_addextendedproperty @name=N'Example Values', @value=N'33308-2413' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimZipCounty', @level2type=N'COLUMN',@level2name=N'ZipCodeAK'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Business Key for ZipCode' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimZipCounty', @level2type=N'COLUMN',@level2name=N'ZipCodeAK'


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


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


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


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


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


EXEC sys.sp_addextendedproperty @name=N'Source System', @value=N'Derived' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimZipCounty', @level2type=N'COLUMN',@level2name=N'ZipCountySK'
  Sample Data
City County CountyFIPS RowIsCurrent RowStartDate State ZipCode ZipCodeAK ZipCountySK
Enon Clark 023 Y 2017-03-09T12:10:00-05:00 OH 45323 45323 61714
Aliceville Pickens 107 Y 2017-03-09T12:10:00-05:00 AL 35442 35442 57703
Stumpy Point Dare 055 Y 2017-03-09T12:10:00-05:00 NC 27978 27978 54051
Toms River Ocean 029 Y 2017-03-09T12:10:00-05:00 NJ 08756 08756 44428
Omaha Sarpy 153 Y 2017-03-09T12:10:00-05:00 NE 68136 68136 71924
Jonesburg Montgomery 139 Y 2017-03-09T12:10:00-05:00 MO 63351 63351 70070
Ketchum Blaine 013 Y 2017-03-09T12:10:00-05:00 ID 83340 83340 77896
Aripeka Pasco 101 Y 2017-03-09T12:10:00-05:00 FL 34679 34679 57403
Kress Swisher 437 Y 2017-03-09T12:10:00-05:00 TX 79052 79052 76413
Walhalla Oconee 073 Y 2017-03-09T12:10:00-05:00 SC 29691 29691 55063
Powered by BI Documenter