EnterpriseDW
 dbo.DimChartOfAccount (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.DimChartOfAccount
  Properties
Property Value
Name DimChartOfAccount
Schema dbo
Row Count 1991532
Data Size 257728 KB
Index Size 968 KB
Create Date 8/28/2013 9:53 AM
Description Chart of Account information, one row per OrgUnit/GL Code combination per year.
  Columns
 
 
Column Name
Data Type
Allow Nulls
Row Guid
Fulltext Indexed
Identity
Default
Description / Computed Formula
AccountStatus
varchar(10)
False
False
False
 
The description of the Account Status. Example: A=Active, I=Inactive, S=Summary
AccountStatusCode
varchar(3)
False
False
False
 
The Status of the Account. Example A, I, S
BudgetPoolNo
varchar(9)
False
False
False
 
The GL Code if this account is pooled. Example: 60000-69999, budget is pooled to 60000.
EndDate
datetime(23, 3)
False
False
False
 
The End Date of the Account
FiscalYear
varchar(4)
False
False
False
 
Year for which record is active
GlAccountNumber
varchar(30)
False
False
False
 
The Account Number including the '-'
In Primary Key
GlAccountSK
int(10, 0)
False
False
False
(1 / 1)
Surrogate primary key
IsAccountBudgeted
varchar(3)
False
False
False
 
Indicator showin gif Account is budgeted or not. Budget Indicator
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?
StartDate
datetime(23, 3)
False
False
False
 
The Start Date of the Account
YearGlAccountNumberAK
varchar(29)
False
False
False
 
Business key
  Triggers
Name Insert Update Delete Instead
  Indexes
Name Clustered Columns
True
GlAccountSK
  Foreign Key Constraints
Name Columns Referenced Table Referenced Columns Checked
  Check Constraints
Name Text Checked
  Parent Dependencies (objects that dbo.DimChartOfAccount depends on)
Name Type
Table
Table
Table
Table
Table
Table
  Child Dependencies (objects that depend on dbo.DimChartOfAccount)
Name Type
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
  Extended Properties
Object Property Value
DimChartOfAccount
Display Name
ChartOfAccount
DimChartOfAccount
MS_Description
Chart of Account information, one row per OrgUnit/GL Code combination per year.
DimChartOfAccount
Table Description
Chart of Account Information
DimChartOfAccount
Table Type
Dimension
AccountStatus
Description
The description of the Account Status
AccountStatus
Display Name
AccountStatus
AccountStatus
ETL Rules
Derived in ETL based on Active_ind
AccountStatus
Example Values
Active, In-active or Summary
AccountStatus
MS_Description
The description of the Account Status. Example: A=Active, I=Inactive, S=Summary
AccountStatus
SCD Type
1
AccountStatus
Source Field Name
AccountStatus
Source System
Derived
AccountStatus
Source Table
AccountStatusCode
Description
The Status of the Account
AccountStatusCode
Display Name
AccountStatusCode
AccountStatusCode
Example Values
A,I or S
AccountStatusCode
MS_Description
The Status of the Account. Example A, I, S
AccountStatusCode
SCD Type
1
AccountStatusCode
Source Field Name
ACTIVE_IND
AccountStatusCode
Source System
DWStaging
AccountStatusCode
Source Table
COA_ORG_UNIT_FILE
BudgetPoolNo
Description
The GL Code if this account is pooled
BudgetPoolNo
Display Name
BudgetPoolNo
BudgetPoolNo
Example Values
60000
BudgetPoolNo
MS_Description
The GL Code if this account is pooled. Example: 60000-69999, budget is pooled to 60000.
BudgetPoolNo
SCD Type
1
BudgetPoolNo
Source Field Name
BUDGET_POOL_NO
BudgetPoolNo
Source System
DWStaging
BudgetPoolNo
Source Table
COA_ORG_UNIT_FILE
EndDate
Description
The End Date of the Account
EndDate
Display Name
EndDate
EndDate
ETL Rules
END_DATE, Convert to datetime field
EndDate
MS_Description
The End Date of the Account
EndDate
SCD Type
1
EndDate
Source Field Name
END_DATE
EndDate
Source System
DWStaging
EndDate
Source Table
COA_ORG_UNIT_FILE
FiscalYear
Description
Year for which record is active
FiscalYear
Display Name
FiscalYear
FiscalYear
Example Values
2012
FiscalYear
MS_Description
Year for which record is active
FiscalYear
SCD Type
1
FiscalYear
Source Field Name
GL_ACCT_NO
FiscalYear
Source System
DWStaging
FiscalYear
Source Table
COA_ORG_UNIT_FILE
GlAccountNumber
Display Name
GlAccountNumber
GlAccountNumber
Example Values
26321000-263112-60501-9999
GlAccountNumber
MS_Description
The Account Number including the '-'
GlAccountNumber
Source Field Name
GL_ACCT_NO
GlAccountNumber
Source System
DWStaging
GlAccountNumber
Source Table
COA_ORG_UNIT_FILE
GlAccountSK
Description
Surrogate primary key
GlAccountSK
Display Name
GlAccountSK
GlAccountSK
Example Values
1, 2, 3?
GlAccountSK
MS_Description
Surrogate primary key
GlAccountSK
SCD Type
key
GlAccountSK
Source System
Derived
IsAccountBudgeted
Description
Indicator showin gif Account is budgeted or not
IsAccountBudgeted
Display Name
IsAccountBudgeted
IsAccountBudgeted
ETL Rules
BUDGETED-ACCT-IND changed from Y/N/ to Yes or No
IsAccountBudgeted
Example Values
Yes or No
IsAccountBudgeted
MS_Description
Indicator showin gif Account is budgeted or not. Budget Indicator
IsAccountBudgeted
SCD Type
1
IsAccountBudgeted
Source Field Name
BUDGETED_ACCT_IND
IsAccountBudgeted
Source System
DWStaging
IsAccountBudgeted
Source Table
COA_ORG_UNIT_FILE
RowEndDate
Description
When did this row become invalid? (12/31/9999 if current row)
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
Description
Is this the current row for this member (Y/N)?
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
Description
When did this row become valid for this member?
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
StartDate
Description
The Start Date of the Account
StartDate
Display Name
StartDate
StartDate
ETL Rules
Start_DATE, Convert to datetime field
StartDate
MS_Description
The Start Date of the Account
StartDate
SCD Type
1
StartDate
Source Field Name
START_DATE
StartDate
Source System
DWStaging
StartDate
Source Table
COA_ORG_UNIT_FILE
YearGlAccountNumberAK
Description
Business key
YearGlAccountNumberAK
Display Name
YearGlAccountNumberAK
YearGlAccountNumberAK
ETL Rules
Extract all rows from COA_ORG_UNITS_FILE where COA_IND = 'Y'
YearGlAccountNumberAK
MS_Description
Business key
YearGlAccountNumberAK
SCD Type
key
YearGlAccountNumberAK
Source Field Name
FISCAL_YEAR + GL_ACCT_NO
YearGlAccountNumberAK
Source System
DWStaging
YearGlAccountNumberAK
Source Table
COA_ORG_UNIT_FILE
   Annotations
Object Property Value
  DDL
/****** Object: Table [dbo].[DimChartOfAccount] Script Date: 03/09/2017 17:14:53 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[DimChartOfAccount](
    [GlAccountSK] [int] IDENTITY(1,1) NOT NULL,
    [YearGlAccountNumberAK] [varchar](29) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [GlAccountNumber] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [FiscalYear] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [AccountStatusCode] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [AccountStatus] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [StartDate] [datetime] NOT NULL,
    [EndDate] [datetime] NOT NULL,
    [IsAccountBudgeted] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [BudgetPoolNo] [varchar](9) 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.DimChartOfAccount] PRIMARY KEY CLUSTERED 
(
    [GlAccountSK] 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'ChartOfAccount' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimChartOfAccount'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Chart of Account information, one row per OrgUnit/GL Code combination per year.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimChartOfAccount'


EXEC sys.sp_addextendedproperty @name=N'Table Description', @value=N'Chart of Account Information' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimChartOfAccount'


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


EXEC sys.sp_addextendedproperty @name=N'Description', @value=N'The description of the Account Status' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimChartOfAccount', @level2type=N'COLUMN',@level2name=N'AccountStatus'


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


EXEC sys.sp_addextendedproperty @name=N'ETL Rules', @value=N'Derived in ETL based on Active_ind' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimChartOfAccount', @level2type=N'COLUMN',@level2name=N'AccountStatus'


EXEC sys.sp_addextendedproperty @name=N'Example Values', @value=N'Active, In-active or Summary' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimChartOfAccount', @level2type=N'COLUMN',@level2name=N'AccountStatus'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The description of the Account Status. Example: A=Active, I=Inactive, S=Summary' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimChartOfAccount', @level2type=N'COLUMN',@level2name=N'AccountStatus'


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


EXEC sys.sp_addextendedproperty @name=N'Source Field Name', @value=N' ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimChartOfAccount', @level2type=N'COLUMN',@level2name=N'AccountStatus'


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


EXEC sys.sp_addextendedproperty @name=N'Source Table', @value=N' ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimChartOfAccount', @level2type=N'COLUMN',@level2name=N'AccountStatus'


EXEC sys.sp_addextendedproperty @name=N'Description', @value=N'The Status of the Account' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimChartOfAccount', @level2type=N'COLUMN',@level2name=N'AccountStatusCode'


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


EXEC sys.sp_addextendedproperty @name=N'Example Values', @value=N'A,I or S' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimChartOfAccount', @level2type=N'COLUMN',@level2name=N'AccountStatusCode'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The Status of the Account. Example A, I, S' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimChartOfAccount', @level2type=N'COLUMN',@level2name=N'AccountStatusCode'


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


EXEC sys.sp_addextendedproperty @name=N'Source Field Name', @value=N'ACTIVE_IND' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimChartOfAccount', @level2type=N'COLUMN',@level2name=N'AccountStatusCode'


EXEC sys.sp_addextendedproperty @name=N'Source System', @value=N'DWStaging' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimChartOfAccount', @level2type=N'COLUMN',@level2name=N'AccountStatusCode'


EXEC sys.sp_addextendedproperty @name=N'Source Table', @value=N'COA_ORG_UNIT_FILE' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimChartOfAccount', @level2type=N'COLUMN',@level2name=N'AccountStatusCode'


EXEC sys.sp_addextendedproperty @name=N'Description', @value=N'The GL Code if this account is pooled' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimChartOfAccount', @level2type=N'COLUMN',@level2name=N'BudgetPoolNo'


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


EXEC sys.sp_addextendedproperty @name=N'Example Values', @value=N'60000' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimChartOfAccount', @level2type=N'COLUMN',@level2name=N'BudgetPoolNo'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The GL Code if this account is pooled. Example: 60000-69999, budget is pooled to 60000.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimChartOfAccount', @level2type=N'COLUMN',@level2name=N'BudgetPoolNo'


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


EXEC sys.sp_addextendedproperty @name=N'Source Field Name', @value=N'BUDGET_POOL_NO' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimChartOfAccount', @level2type=N'COLUMN',@level2name=N'BudgetPoolNo'


EXEC sys.sp_addextendedproperty @name=N'Source System', @value=N'DWStaging' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimChartOfAccount', @level2type=N'COLUMN',@level2name=N'BudgetPoolNo'


EXEC sys.sp_addextendedproperty @name=N'Source Table', @value=N'COA_ORG_UNIT_FILE' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimChartOfAccount', @level2type=N'COLUMN',@level2name=N'BudgetPoolNo'


EXEC sys.sp_addextendedproperty @name=N'Description', @value=N'The End Date of the Account' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimChartOfAccount', @level2type=N'COLUMN',@level2name=N'EndDate'


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


EXEC sys.sp_addextendedproperty @name=N'ETL Rules', @value=N'END_DATE, Convert to datetime field' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimChartOfAccount', @level2type=N'COLUMN',@level2name=N'EndDate'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The End Date of the Account' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimChartOfAccount', @level2type=N'COLUMN',@level2name=N'EndDate'


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


EXEC sys.sp_addextendedproperty @name=N'Source Field Name', @value=N'END_DATE' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimChartOfAccount', @level2type=N'COLUMN',@level2name=N'EndDate'


EXEC sys.sp_addextendedproperty @name=N'Source System', @value=N'DWStaging' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimChartOfAccount', @level2type=N'COLUMN',@level2name=N'EndDate'


EXEC sys.sp_addextendedproperty @name=N'Source Table', @value=N'COA_ORG_UNIT_FILE' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimChartOfAccount', @level2type=N'COLUMN',@level2name=N'EndDate'


EXEC sys.sp_addextendedproperty @name=N'Description', @value=N'Year for which record is active' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimChartOfAccount', @level2type=N'COLUMN',@level2name=N'FiscalYear'


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


EXEC sys.sp_addextendedproperty @name=N'Example Values', @value=N'2012' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimChartOfAccount', @level2type=N'COLUMN',@level2name=N'FiscalYear'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Year for which record is active' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimChartOfAccount', @level2type=N'COLUMN',@level2name=N'FiscalYear'


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


EXEC sys.sp_addextendedproperty @name=N'Source Field Name', @value=N'GL_ACCT_NO' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimChartOfAccount', @level2type=N'COLUMN',@level2name=N'FiscalYear'


EXEC sys.sp_addextendedproperty @name=N'Source System', @value=N'DWStaging' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimChartOfAccount', @level2type=N'COLUMN',@level2name=N'FiscalYear'


EXEC sys.sp_addextendedproperty @name=N'Source Table', @value=N'COA_ORG_UNIT_FILE' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimChartOfAccount', @level2type=N'COLUMN',@level2name=N'FiscalYear'


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


EXEC sys.sp_addextendedproperty @name=N'Example Values', @value=N'26321000-263112-60501-9999' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimChartOfAccount', @level2type=N'COLUMN',@level2name=N'GlAccountNumber'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The Account Number including the ''-''' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimChartOfAccount', @level2type=N'COLUMN',@level2name=N'GlAccountNumber'


EXEC sys.sp_addextendedproperty @name=N'Source Field Name', @value=N'GL_ACCT_NO' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimChartOfAccount', @level2type=N'COLUMN',@level2name=N'GlAccountNumber'


EXEC sys.sp_addextendedproperty @name=N'Source System', @value=N'DWStaging' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimChartOfAccount', @level2type=N'COLUMN',@level2name=N'GlAccountNumber'


EXEC sys.sp_addextendedproperty @name=N'Source Table', @value=N'COA_ORG_UNIT_FILE' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimChartOfAccount', @level2type=N'COLUMN',@level2name=N'GlAccountNumber'


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


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


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


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


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


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


EXEC sys.sp_addextendedproperty @name=N'Description', @value=N'Indicator showin gif Account is budgeted or not' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimChartOfAccount', @level2type=N'COLUMN',@level2name=N'IsAccountBudgeted'


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


EXEC sys.sp_addextendedproperty @name=N'ETL Rules', @value=N'BUDGETED-ACCT-IND changed from Y/N/ to Yes or No' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimChartOfAccount', @level2type=N'COLUMN',@level2name=N'IsAccountBudgeted'


EXEC sys.sp_addextendedproperty @name=N'Example Values', @value=N'Yes or No' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimChartOfAccount', @level2type=N'COLUMN',@level2name=N'IsAccountBudgeted'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Indicator showin gif Account is budgeted or not. Budget Indicator' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimChartOfAccount', @level2type=N'COLUMN',@level2name=N'IsAccountBudgeted'


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


EXEC sys.sp_addextendedproperty @name=N'Source Field Name', @value=N'BUDGETED_ACCT_IND' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimChartOfAccount', @level2type=N'COLUMN',@level2name=N'IsAccountBudgeted'


EXEC sys.sp_addextendedproperty @name=N'Source System', @value=N'DWStaging' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimChartOfAccount', @level2type=N'COLUMN',@level2name=N'IsAccountBudgeted'


EXEC sys.sp_addextendedproperty @name=N'Source Table', @value=N'COA_ORG_UNIT_FILE' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimChartOfAccount', @level2type=N'COLUMN',@level2name=N'IsAccountBudgeted'


EXEC sys.sp_addextendedproperty @name=N'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'DimChartOfAccount', @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'DimChartOfAccount', @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'DimChartOfAccount', @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'DimChartOfAccount', @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'DimChartOfAccount', @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'DimChartOfAccount', @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'DimChartOfAccount', @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'DimChartOfAccount', @level2type=N'COLUMN',@level2name=N'RowEndDate'


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


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


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


EXEC sys.sp_addextendedproperty @name=N'Description', @value=N'The Start Date of the Account' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimChartOfAccount', @level2type=N'COLUMN',@level2name=N'StartDate'


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


EXEC sys.sp_addextendedproperty @name=N'ETL Rules', @value=N'Start_DATE, Convert to datetime field' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimChartOfAccount', @level2type=N'COLUMN',@level2name=N'StartDate'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The Start Date of the Account' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimChartOfAccount', @level2type=N'COLUMN',@level2name=N'StartDate'


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


EXEC sys.sp_addextendedproperty @name=N'Source Field Name', @value=N'START_DATE' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimChartOfAccount', @level2type=N'COLUMN',@level2name=N'StartDate'


EXEC sys.sp_addextendedproperty @name=N'Source System', @value=N'DWStaging' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimChartOfAccount', @level2type=N'COLUMN',@level2name=N'StartDate'


EXEC sys.sp_addextendedproperty @name=N'Source Table', @value=N'COA_ORG_UNIT_FILE' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimChartOfAccount', @level2type=N'COLUMN',@level2name=N'StartDate'


EXEC sys.sp_addextendedproperty @name=N'Description', @value=N'Business key' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimChartOfAccount', @level2type=N'COLUMN',@level2name=N'YearGlAccountNumberAK'


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


EXEC sys.sp_addextendedproperty @name=N'ETL Rules', @value=N'Extract all rows from COA_ORG_UNITS_FILE where COA_IND = ''Y''' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimChartOfAccount', @level2type=N'COLUMN',@level2name=N'YearGlAccountNumberAK'


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


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


EXEC sys.sp_addextendedproperty @name=N'Source Field Name', @value=N'FISCAL_YEAR + GL_ACCT_NO' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimChartOfAccount', @level2type=N'COLUMN',@level2name=N'YearGlAccountNumberAK'


EXEC sys.sp_addextendedproperty @name=N'Source System', @value=N'DWStaging' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimChartOfAccount', @level2type=N'COLUMN',@level2name=N'YearGlAccountNumberAK'


EXEC sys.sp_addextendedproperty @name=N'Source Table', @value=N'COA_ORG_UNIT_FILE' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DimChartOfAccount', @level2type=N'COLUMN',@level2name=N'YearGlAccountNumberAK'
  Sample Data
AccountStatus AccountStatusCode BudgetPoolNo EndDate FiscalYear GlAccountNumber GlAccountSK IsAccountBudgeted RowEndDate RowIsCurrent RowStartDate StartDate YearGlAccountNumberAK
Active A N/A 2017-06-30T00:00:00-04:00 2017 31320000- -00-24000 2904726 No 9999-12-31T23:59:00-05:00 Y 2017-03-09T11:43:00-05:00 2016-07-01T00:00:00-04:00 201731320000 0024000
Active A N/A 2010-06-30T00:00:00-04:00 2010 15900000-590000-20-60501 2951979 Yes 9999-12-31T23:59:00-05:00 Y 2017-03-09T11:43:00-05:00 2009-07-01T00:00:00-04:00 2010159000005900002060501
Active A 60000 2017-06-30T00:00:00-04:00 2017 11172000-172000-40-64605 3264331 Yes 9999-12-31T23:59:00-05:00 Y 2017-03-09T11:43:00-05:00 2016-07-01T00:00:00-04:00 2017111720001720004064605
Active A N/A 2017-06-30T00:00:00-04:00 2017 20000091-000000-00-40330-SPRG 3010319 No 9999-12-31T23:59:00-05:00 Y 2017-03-09T11:43:00-05:00 2016-07-01T00:00:00-04:00 2017200000910000000040330SPRG
Active A N/A 2018-06-30T00:00:00-04:00 2018 48114100-811414-90-40101-1703 4332898 No 9999-12-31T23:59:00-05:00 Y 2017-03-09T11:43:00-05:00 2017-07-01T00:00:00-04:00 20184811410081141490401011703
Active A 60000 2017-06-30T00:00:00-04:00 2017 14600000-460000-20-67505 3470861 Yes 9999-12-31T23:59:00-05:00 Y 2017-03-09T11:43:00-05:00 2016-07-01T00:00:00-04:00 2017146000004600002067505
Active A 60000 2011-06-30T00:00:00-04:00 2011 14601000-460100-30-65501 2975843 Yes 9999-12-31T23:59:00-05:00 Y 2017-03-09T11:43:00-05:00 2010-07-01T00:00:00-04:00 2011146010004601003065501
Active A N/A 2018-06-30T00:00:00-04:00 2018 58215000-215045-90-19559 4380663 No 9999-12-31T23:59:00-05:00 Y 2017-03-09T11:43:00-05:00 2017-07-01T00:00:00-04:00 2018582150002150459019559
Active A 60000 2017-06-30T00:00:00-04:00 2017 11150500-150502-20-64505 3253868 Yes 9999-12-31T23:59:00-05:00 Y 2017-03-09T11:43:00-05:00 2016-07-01T00:00:00-04:00 2017111505001505022064505
Active A N/A 2017-06-30T00:00:00-04:00 2017 21240100-240142-20-26420 3141154 No 9999-12-31T23:59:00-05:00 Y 2017-03-09T11:43:00-05:00 2016-07-01T00:00:00-04:00 2017212401002401422026420
Powered by BI Documenter