|
|
|||||
|
|
|||||
Properties
| Property | Value |
| Name | FactGlRollOverBalances |
| Schema | dbo |
| Row Count | 25915 |
| Data Size | 976 KB |
| Index Size | 16 KB |
| Create Date | 8/28/2013 9:53 AM |
| Description | The record of a posted journal line, one record for each Journal Line. Will contain one row for each chart of account per year that had a roll-over balance. |
Columns
Column Name |
Data Type |
Allow Nulls |
Row Guid |
Fulltext Indexed |
Identity |
Default |
Description / Computed Formula |
||
 |
|
Amount |
numeric(11, 2) |
False |
False |
False |
Amount in Bucket 1 |
||
|
 |
DateSK |
int(10, 0) |
False |
False |
False |
Key to the Date dimension |
||
|
|
GlAccountSK |
int(10, 0) |
False |
False |
False |
Key to ChartOfAccount dimension |
||
|
|
GlCodeSK |
int(10, 0) |
False |
False |
False |
Key to GlCode dimension |
||
 |
|
GlRollOverSK |
int(10, 0) |
False |
False |
False |
(1 / 1) |
Primary Key for Uniqueness |
|
|
|
OrgUnitSK |
int(10, 0) |
False |
False |
False |
Key to OrgUnit Dimension |
Triggers
| Name | Insert | Update | Delete | Instead |
Indexes
| Name | Clustered | Columns |
True |
GlRollOverSK |
Foreign Key Constraints
| Name | Columns | Referenced Table | Referenced Columns | Checked |
DateSK |
DimDate |
DateSK |
True |
|
GlAccountSK |
DimChartOfAccount |
GlAccountSK |
True |
|
GlCodeSK |
DimGlCode |
GlCodeSK |
True |
|
OrgUnitSK |
DimOrgUnit |
OrgUnitSK |
True |
Check Constraints
| Name | Text | Checked |
Parent Dependencies (objects that dbo.FactGlRollOverBalances depends on)
| Name | Type |
Table |
|
Table |
|
Table |
|
Table |
Child Dependencies (objects that depend on dbo.FactGlRollOverBalances)
| Name | Type |
TABLE |
|
TABLE |
|
TABLE |
|
TABLE |
Extended Properties
| Object | Property | Value |
FactGlRollOverBalances |
Display Name |
General Ledger Balances |
FactGlRollOverBalances |
MS_Description |
The record of a posted journal line, one record for each Journal Line. Will contain one row for each chart of account per year that had a roll-over balance. |
FactGlRollOverBalances |
Table Description |
The record of a posted journal line, one record for each Journal Line |
FactGlRollOverBalances |
Table Type |
Fact |
Amount |
Description |
Amount in Bucket 1 |
Amount |
Display Name |
Amount |
Amount |
ETL Rules |
Extract all data from GENERAL_LEDGER_FILE and GENERAL_LEDGER_MONTHLY_ACCUM with cnxarray=0 |
Amount |
Example Values |
100 |
Amount |
MS_Description |
Amount in Bucket 1 |
Amount |
SCD Type |
1 |
Amount |
Source Field Name |
AMOUNT |
Amount |
Source System |
DWStaging |
Amount |
Source Table |
GENERAL_LEDGER_MONTHLY_ACCUM |
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 |
GlRollOverSK |
MS_Description |
Primary Key for Uniqueness |
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 |
Annotations
| Object | Property | Value |
DDL
|
/****** Object: Table [dbo].[FactGlRollOverBalances] Script Date: 03/09/2017 17:14:58 ******/ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON CREATE TABLE [dbo].[FactGlRollOverBalances]( [GlRollOverSK] [int] IDENTITY(1,1) NOT NULL, [DateSK] [int] NOT NULL, [OrgUnitSK] [int] NOT NULL, [GlCodeSK] [int] NOT NULL, [GlAccountSK] [int] NOT NULL, [Amount] [numeric](11, 2) NOT NULL, CONSTRAINT [PK_FactGlRollOverBalances] PRIMARY KEY CLUSTERED ( [GlRollOverSK] 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].[FactGlRollOverBalances] WITH CHECK ADD CONSTRAINT [FK_dbo_FactGlRollOverBalances_DateSK] FOREIGN KEY([DateSK]) REFERENCES [DimDate] ([DateSK]) ALTER TABLE [dbo].[FactGlRollOverBalances] CHECK CONSTRAINT [FK_dbo_FactGlRollOverBalances_DateSK] ALTER TABLE [dbo].[FactGlRollOverBalances] WITH CHECK ADD CONSTRAINT [FK_FactGlRollOverBalances_DimChartOfAccount] FOREIGN KEY([GlAccountSK]) REFERENCES [DimChartOfAccount] ([GlAccountSK]) ALTER TABLE [dbo].[FactGlRollOverBalances] CHECK CONSTRAINT [FK_FactGlRollOverBalances_DimChartOfAccount] ALTER TABLE [dbo].[FactGlRollOverBalances] WITH CHECK ADD CONSTRAINT [FK_FactGlRollOverBalances_DimGlCode] FOREIGN KEY([GlCodeSK]) REFERENCES [DimGlCode] ([GlCodeSK]) ALTER TABLE [dbo].[FactGlRollOverBalances] CHECK CONSTRAINT [FK_FactGlRollOverBalances_DimGlCode] ALTER TABLE [dbo].[FactGlRollOverBalances] WITH CHECK ADD CONSTRAINT [FK_FactGlRollOverBalances_DimOrgUnit] FOREIGN KEY([OrgUnitSK]) REFERENCES [DimOrgUnit] ([OrgUnitSK]) ALTER TABLE [dbo].[FactGlRollOverBalances] CHECK CONSTRAINT [FK_FactGlRollOverBalances_DimOrgUnit] EXEC sys.sp_addextendedproperty @name=N'Display Name', @value=N'General Ledger Balances' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FactGlRollOverBalances' EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The record of a posted journal line, one record for each Journal Line. Will contain one row for each chart of account per year that had a roll-over balance.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FactGlRollOverBalances' EXEC sys.sp_addextendedproperty @name=N'Table Description', @value=N'The record of a posted journal line, one record for each Journal Line' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FactGlRollOverBalances' EXEC sys.sp_addextendedproperty @name=N'Table Type', @value=N'Fact' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FactGlRollOverBalances' EXEC sys.sp_addextendedproperty @name=N'Description', @value=N'Amount in Bucket 1 ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FactGlRollOverBalances', @level2type=N'COLUMN',@level2name=N'Amount' EXEC sys.sp_addextendedproperty @name=N'Display Name', @value=N'Amount' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FactGlRollOverBalances', @level2type=N'COLUMN',@level2name=N'Amount' EXEC sys.sp_addextendedproperty @name=N'ETL Rules', @value=N'Extract all data from GENERAL_LEDGER_FILE and GENERAL_LEDGER_MONTHLY_ACCUM with cnxarray=0' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FactGlRollOverBalances', @level2type=N'COLUMN',@level2name=N'Amount' EXEC sys.sp_addextendedproperty @name=N'Example Values', @value=N'100' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FactGlRollOverBalances', @level2type=N'COLUMN',@level2name=N'Amount' EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Amount in Bucket 1 ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FactGlRollOverBalances', @level2type=N'COLUMN',@level2name=N'Amount' EXEC sys.sp_addextendedproperty @name=N'SCD Type', @value=N'1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FactGlRollOverBalances', @level2type=N'COLUMN',@level2name=N'Amount' EXEC sys.sp_addextendedproperty @name=N'Source Field Name', @value=N'AMOUNT' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FactGlRollOverBalances', @level2type=N'COLUMN',@level2name=N'Amount' EXEC sys.sp_addextendedproperty @name=N'Source System', @value=N'DWStaging' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FactGlRollOverBalances', @level2type=N'COLUMN',@level2name=N'Amount' 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'FactGlRollOverBalances', @level2type=N'COLUMN',@level2name=N'Amount' 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'FactGlRollOverBalances', @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'FactGlRollOverBalances', @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'FactGlRollOverBalances', @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'FactGlRollOverBalances', @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'FactGlRollOverBalances', @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'FactGlRollOverBalances', @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'FactGlRollOverBalances', @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'FactGlRollOverBalances', @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'FactGlRollOverBalances', @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'FactGlRollOverBalances', @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'FactGlRollOverBalances', @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'FactGlRollOverBalances', @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'FactGlRollOverBalances', @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'FactGlRollOverBalances', @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'FactGlRollOverBalances', @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'FactGlRollOverBalances', @level2type=N'COLUMN',@level2name=N'GlCodeSK' EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Primary Key for Uniqueness' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FactGlRollOverBalances', @level2type=N'COLUMN',@level2name=N'GlRollOverSK' EXEC sys.sp_addextendedproperty @name=N'Description', @value=N'Key to OrgUnit Dimension' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FactGlRollOverBalances', @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'FactGlRollOverBalances', @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'FactGlRollOverBalances', @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'FactGlRollOverBalances', @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'FactGlRollOverBalances', @level2type=N'COLUMN',@level2name=N'OrgUnitSK' |
Sample Data
| Amount | DateSK | GlAccountSK | GlCodeSK | GlRollOverSK | OrgUnitSK |
| -2000.00 | 20120701 | 3059635 | 5148 | 1000 | 15531 |
| 6360.91 | 20020701 | 2774766 | 5148 | 8936 | 16914 |
| 500.00 | 20090701 | 2966943 | 5148 | 15599 | 15924 |
| 62770.79 | 20040701 | 2819392 | 5148 | 12459 | 15069 |
| -1584.81 | 20040701 | 2822543 | 5636 | 2258 | 16893 |
| 2706.72 | 19980701 | 2662691 | 5148 | 21278 | 14941 |
| -13742.92 | 20060701 | 2865611 | 5636 | 851 | 14170 |
| -1355675.33 | 20150701 | 3117137 | 5458 | 8329 | 12484 |
| -10196.13 | 20140701 | 3117017 | 5636 | 24182 | 17642 |
| -509.18 | 20030701 | 2796124 | 5636 | 23631 | 16838 |
| Powered by BI Documenter |
