EnterpriseDW
 dbo.FactGlSummaryBalances (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.FactGlSummaryBalances
  Properties
Property Value
Name FactGlSummaryBalances
Schema dbo
Row Count 1142916
Data Size 42928 KB
Index Size 176 KB
Create Date 8/28/2013 9:53 AM
Description This is the Summary Balances for each Calendar Period. Will contain one row for each chart of account per calendar period that had transactions.
  Columns
 
 
Column Name
Data Type
Allow Nulls
Row Guid
Fulltext Indexed
Identity
Default
Description / Computed Formula
Foreign Key
DateSK
int(10, 0)
False
False
False
 
Key to the Date dimension
Foreign Key
GlAccountSK
int(10, 0)
False
False
False
 
Key to ChartOfAccount dimension
Foreign Key
GlCodeSK
int(10, 0)
False
False
False
 
Key to GlCode dimension
In Primary Key
GlSummarySK
int(10, 0)
False
False
False
(1 / 1)
Primary Key for fact to ensure uniqueness
Foreign Key
OrgUnitSK
int(10, 0)
False
False
False
 
Key to OrgUnit Dimension
SummaryAmount
numeric(11, 2)
False
False
False
 
Summary Amount per Calendar Period
  Triggers
Name Insert Update Delete Instead
  Indexes
Name Clustered Columns
True
GlSummarySK
  Foreign Key Constraints
Name Columns Referenced Table Referenced Columns Checked
GlAccountSK
DimChartOfAccount
GlAccountSK
True
DateSK
DimDate
DateSK
True
GlCodeSK
DimGlCode
GlCodeSK
True
OrgUnitSK
DimOrgUnit
OrgUnitSK
True
  Check Constraints
Name Text Checked
  Parent Dependencies (objects that dbo.FactGlSummaryBalances depends on)
Name Type
Table
Table
Table
Table
  Child Dependencies (objects that depend on dbo.FactGlSummaryBalances)
Name Type
TABLE
TABLE
TABLE
TABLE
  Extended Properties
Object Property Value
FactGlSummaryBalances
Display Name
General Ledger Monthly Balances
FactGlSummaryBalances
MS_Description
This is the Summary Balances for each Calendar Period. Will contain one row for each chart of account per calendar period that had transactions.
FactGlSummaryBalances
Table Description
This is the Summary Balances for each Calendar Period.
FactGlSummaryBalances
Table Type
Fact
DateSK
Description
Key to the Date dimension
DateSK
Display Name
DateSK
DateSK
ETL Rules
Use calendar period and add 01
DateSK
Example Values
1,2,3
DateSK
MS_Description
Key to the Date dimension
DateSK
Source System
Derived
GlAccountSK
Description
Key to ChartOfAccount dimension
GlAccountSK
Display Name
GlAccountSK
GlAccountSK
Example Values
1,2,3
GlAccountSK
MS_Description
Key to ChartOfAccount dimension
GlAccountSK
Source System
Derived
GlCodeSK
Description
Key to GlCode dimension
GlCodeSK
Display Name
GlCodeSK
GlCodeSK
Example Values
1,2,3
GlCodeSK
MS_Description
Key to GlCode dimension
GlCodeSK
Source System
Derived
GlSummarySK
Description
Primary Key for fact to ensure uniqueness
GlSummarySK
Display Name
GlSummarySK
GlSummarySK
ETL Rules
None
GlSummarySK
Example Values
1,2,3
GlSummarySK
MS_Description
Primary Key for fact to ensure uniqueness
GlSummarySK
Source System
Derived
OrgUnitSK
Description
Key to OrgUnit Dimension
OrgUnitSK
Display Name
OrgUnitSK
OrgUnitSK
Example Values
1,2,3
OrgUnitSK
MS_Description
Key to OrgUnit Dimension
OrgUnitSK
Source System
Derived
SummaryAmount
Description
Summary Amount per Calendar Period
SummaryAmount
Display Name
Amount
SummaryAmount
ETL Rules
Extract all data from GENERAL_LEDGER_FILE and GENERAL_LEDGER_MONTHLY_ACCUM with cnxarray=1 thru 12, 1 = July, 12 = June
SummaryAmount
Example Values
100
SummaryAmount
MS_Description
Summary Amount per Calendar Period
SummaryAmount
SCD Type
1
SummaryAmount
Source Field Name
AMOUNT
SummaryAmount
Source System
DWStaging
SummaryAmount
Source Table
GENERAL_LEDGER_MONTHLY_ACCUM
   Annotations
Object Property Value
  DDL
/****** Object: Table [dbo].[FactGlSummaryBalances] Script Date: 03/09/2017 17:14:58 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[FactGlSummaryBalances](
    [GlSummarySK] [int] IDENTITY(1,1) NOT NULL,
    [DateSK] [int] NOT NULL,
    [OrgUnitSK] [int] NOT NULL,
    [GlCodeSK] [int] NOT NULL,
    [GlAccountSK] [int] NOT NULL,
    [SummaryAmount] [numeric](11, 2) NOT NULL,
 CONSTRAINT [PK_FactGlSummaryBalances] PRIMARY KEY CLUSTERED 
(
    [GlSummarySK] 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].[FactGlSummaryBalances]  WITH CHECK ADD  CONSTRAINT [FK_FactGlSummaryBalances_DimChartOfAccount] FOREIGN KEY([GlAccountSK])
REFERENCES [DimChartOfAccount] ([GlAccountSK])
ALTER TABLE [dbo].[FactGlSummaryBalances] CHECK CONSTRAINT [FK_FactGlSummaryBalances_DimChartOfAccount]
ALTER TABLE [dbo].[FactGlSummaryBalances]  WITH CHECK ADD  CONSTRAINT [FK_FactGlSummaryBalances_DimDate] FOREIGN KEY([DateSK])
REFERENCES [DimDate] ([DateSK])
ALTER TABLE [dbo].[FactGlSummaryBalances] CHECK CONSTRAINT [FK_FactGlSummaryBalances_DimDate]
ALTER TABLE [dbo].[FactGlSummaryBalances]  WITH CHECK ADD  CONSTRAINT [FK_FactGlSummaryBalances_DimGlCode] FOREIGN KEY([GlCodeSK])
REFERENCES [DimGlCode] ([GlCodeSK])
ALTER TABLE [dbo].[FactGlSummaryBalances] CHECK CONSTRAINT [FK_FactGlSummaryBalances_DimGlCode]
ALTER TABLE [dbo].[FactGlSummaryBalances]  WITH CHECK ADD  CONSTRAINT [FK_FactGlSummaryBalances_DimOrgUnit] FOREIGN KEY([OrgUnitSK])
REFERENCES [DimOrgUnit] ([OrgUnitSK])
ALTER TABLE [dbo].[FactGlSummaryBalances] CHECK CONSTRAINT [FK_FactGlSummaryBalances_DimOrgUnit]


EXEC sys.sp_addextendedproperty @name=N'Display Name', @value=N'General Ledger Monthly Balances' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FactGlSummaryBalances'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'This is the Summary Balances for each Calendar Period. Will contain one row for each chart of account per calendar period that had transactions.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FactGlSummaryBalances'


EXEC sys.sp_addextendedproperty @name=N'Table Description', @value=N'This is the Summary Balances for each Calendar Period.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FactGlSummaryBalances'


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


EXEC sys.sp_addextendedproperty @name=N'Description', @value=N'Key to the Date dimension' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FactGlSummaryBalances', @level2type=N'COLUMN',@level2name=N'DateSK'


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


EXEC sys.sp_addextendedproperty @name=N'ETL Rules', @value=N'Use calendar period and add 01 ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FactGlSummaryBalances', @level2type=N'COLUMN',@level2name=N'DateSK'


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


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Key to the Date dimension' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FactGlSummaryBalances', @level2type=N'COLUMN',@level2name=N'DateSK'


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


EXEC sys.sp_addextendedproperty @name=N'Description', @value=N'Key to ChartOfAccount dimension' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FactGlSummaryBalances', @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'FactGlSummaryBalances', @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'FactGlSummaryBalances', @level2type=N'COLUMN',@level2name=N'GlAccountSK'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Key to ChartOfAccount dimension' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FactGlSummaryBalances', @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'FactGlSummaryBalances', @level2type=N'COLUMN',@level2name=N'GlAccountSK'


EXEC sys.sp_addextendedproperty @name=N'Description', @value=N'Key to GlCode dimension' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FactGlSummaryBalances', @level2type=N'COLUMN',@level2name=N'GlCodeSK'


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


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


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Key to GlCode dimension' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FactGlSummaryBalances', @level2type=N'COLUMN',@level2name=N'GlCodeSK'


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


EXEC sys.sp_addextendedproperty @name=N'Description', @value=N'Primary Key for fact to ensure uniqueness' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FactGlSummaryBalances', @level2type=N'COLUMN',@level2name=N'GlSummarySK'


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


EXEC sys.sp_addextendedproperty @name=N'ETL Rules', @value=N'None' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FactGlSummaryBalances', @level2type=N'COLUMN',@level2name=N'GlSummarySK'


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


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Primary Key for fact to ensure uniqueness' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FactGlSummaryBalances', @level2type=N'COLUMN',@level2name=N'GlSummarySK'


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


EXEC sys.sp_addextendedproperty @name=N'Description', @value=N'Key to OrgUnit Dimension' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FactGlSummaryBalances', @level2type=N'COLUMN',@level2name=N'OrgUnitSK'


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


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


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Key to OrgUnit Dimension' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FactGlSummaryBalances', @level2type=N'COLUMN',@level2name=N'OrgUnitSK'


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


EXEC sys.sp_addextendedproperty @name=N'Description', @value=N'Summary Amount per Calendar Period ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FactGlSummaryBalances', @level2type=N'COLUMN',@level2name=N'SummaryAmount'


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


EXEC sys.sp_addextendedproperty @name=N'ETL Rules', @value=N'Extract all data from GENERAL_LEDGER_FILE and GENERAL_LEDGER_MONTHLY_ACCUM with cnxarray=1 thru 12, 1 = July, 12 = June' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FactGlSummaryBalances', @level2type=N'COLUMN',@level2name=N'SummaryAmount'


EXEC sys.sp_addextendedproperty @name=N'Example Values', @value=N'100' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FactGlSummaryBalances', @level2type=N'COLUMN',@level2name=N'SummaryAmount'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Summary Amount per Calendar Period ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FactGlSummaryBalances', @level2type=N'COLUMN',@level2name=N'SummaryAmount'


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


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


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


EXEC sys.sp_addextendedproperty @name=N'Source Table', @value=N'GENERAL_LEDGER_MONTHLY_ACCUM ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FactGlSummaryBalances', @level2type=N'COLUMN',@level2name=N'SummaryAmount'
  Sample Data
DateSK GlAccountSK GlCodeSK GlSummarySK OrgUnitSK SummaryAmount
20041001 2819697 6222 1106977 15108 -14000.00
20151001 3122821 6415 980687 13491 7.70
20030701 2793507 6415 914369 15114 21.84
20111101 3004994 6362 973187 12695 53.16
20050701 2826266 6237 939862 13017 5319.78
20141001 3092512 6415 592223 12819 15.09
20130201 3048249 6361 1063681 14229 298.16
20100801 2982225 6419 906971 13576 55.56
20080701 2912042 6432 347964 12786 722.00
20150701 3122677 6518 924577 13459 1186.50
Powered by BI Documenter