EnterpriseDW
 dbo.FactPositionExpense (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.FactPositionExpense
  Properties
Property Value
Name FactPositionExpense
Schema dbo
Row Count 4905067
Data Size 307224 KB
Index Size 73096 KB
Create Date 3/9/2017 10:43 AM
Description
  Columns
 
 
Column Name
Data Type
Allow Nulls
Row Guid
Fulltext Indexed
Identity
Default
Description / Computed Formula
Foreign Key
AssignmentSK
int(10, 0)
False
False
False
 
Key identifier for the employee's Assignment, used to reference the assignment dimension.
Foreign Key
BudgetedSK
int(10, 0)
False
False
False
 
Key identifier for the budgeted information, used to reference the budgeted dimension.
Foreign Key
CheckDateSK
int(10, 0)
False
False
False
 
Key identifier for the date the employee was paid or the check date, used to reference the date dimension.
Foreign Key
CheckTypeSK
int(10, 0)
False
False
False
 
Key identifier for the type of check with which the employee was paid, used to reference the Check Type dimension.
Foreign Key
CompensationTypeSK
int(10, 0)
False
False
False
 
Key identifier for the type of budget component, used to reference the budget component dimension.
Foreign Key
EmployeeSK
int(10, 0)
False
False
False
 
Key identifier for the Employee, used to reference the employee dimension.
ExpenseAmount
numeric(11, 2)
True
False
False
 
The number (in some cases the average hours not actual) of hours worked by an employee on an assignment for a week.
Foreign Key
GLAccountSK
int(10, 0)
False
False
False
 
Key identifier for the full account number, used to reference the Chart of Accounts dimension.
Foreign Key
GlCodeSK
int(10, 0)
False
False
False
 
Key identifier for the General Ledger Code of the account (last 9 digits of the account), used to reference the GLCode dimensio
Foreign Key
OrgUnitSK
int(10, 0)
False
False
False
 
Key identifier for the Organizational Unit of the account (first 16 digits of the account), used to reference the OrgUnit Dimens
In Primary Key
PositionExpenseSK
int(10, 0)
False
False
False
(1 / 1)
Primary Key to the Fact.
Foreign Key
PositionSK
int(10, 0)
False
False
False
 
Key identifier for the employee's Position, used to reference the position dimension.
  Triggers
Name Insert Update Delete Instead
  Indexes
Name Clustered Columns
False
PositionExpenseSK
  Foreign Key Constraints
Name Columns Referenced Table Referenced Columns Checked
AssignmentSK
DimAssignment
AssignmentSK
True
BudgetedSK
DimBudgeted
BudgetedSK
True
CheckDateSK
DimDate
DateSK
True
CheckTypeSK
DimCheckType
CheckTypeSK
True
CompensationTypeSK
DimCompensationType
CompensationTypeSK
True
EmployeeSK
DimEmployee
EmployeeSK
True
GLAccountSK
DimChartOfAccount
GlAccountSK
True
GlCodeSK
DimGlCode
GlCodeSK
True
OrgUnitSK
DimOrgUnit
OrgUnitSK
True
PositionSK
DimPosition
PositionSK
True
  Check Constraints
Name Text Checked
  Parent Dependencies (objects that dbo.FactPositionExpense depends on)
Name Type
Table
Table
Table
Table
Table
Table
Table
Table
Table
Table
  Child Dependencies (objects that depend on dbo.FactPositionExpense)
Name Type
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
  Extended Properties
Object Property Value
FactPositionExpense
Display Name
PositionExpense
FactPositionExpense
Table Description
The fact will contain a row per Employee per Position per Assignment per pay check per budget component per account number for p
FactPositionExpense
Table Type
Fact
AssignmentSK
MS_Description
Key identifier for the employee's Assignment, used to reference the assignment dimension.
BudgetedSK
MS_Description
Key identifier for the budgeted information, used to reference the budgeted dimension.
CheckDateSK
MS_Description
Key identifier for the date the employee was paid or the check date, used to reference the date dimension.
CheckTypeSK
MS_Description
Key identifier for the type of check with which the employee was paid, used to reference the Check Type dimension.
CompensationTypeSK
MS_Description
Key identifier for the type of budget component, used to reference the budget component dimension.
EmployeeSK
MS_Description
Key identifier for the Employee, used to reference the employee dimension.
ExpenseAmount
MS_Description
The number (in some cases the average hours not actual) of hours worked by an employee on an assignment for a week.
GLAccountSK
MS_Description
Key identifier for the full account number, used to reference the Chart of Accounts dimension.
GlCodeSK
MS_Description
Key identifier for the General Ledger Code of the account (last 9 digits of the account), used to reference the GLCode dimensio
OrgUnitSK
MS_Description
Key identifier for the Organizational Unit of the account (first 16 digits of the account), used to reference the OrgUnit Dimens
PositionExpenseSK
MS_Description
Primary Key to the Fact.
PositionSK
MS_Description
Key identifier for the employee's Position, used to reference the position dimension.
   Annotations
Object Property Value
  DDL
/****** Object: Table [dbo].[FactPositionExpense] Script Date: 03/09/2017 17:14:58 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[FactPositionExpense](
    [PositionExpenseSK] [int] IDENTITY(1,1) NOT NULL,
    [CompensationTypeSK] [int] NOT NULL,
    [CheckDateSK] [int] NOT NULL,
    [CheckTypeSK] [int] NOT NULL,
    [EmployeeSK] [int] NOT NULL,
    [PositionSK] [int] NOT NULL,
    [AssignmentSK] [int] NOT NULL,
    [BudgetedSK] [int] NOT NULL,
    [OrgUnitSK] [int] NOT NULL,
    [GlCodeSK] [int] NOT NULL,
    [GLAccountSK] [int] NOT NULL,
    [ExpenseAmount] [numeric](11, 2) NULL,
 CONSTRAINT [PK_dbo.FactPositionExpense] PRIMARY KEY NONCLUSTERED 
(
    [PositionExpenseSK] 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].[FactPositionExpense]  WITH CHECK ADD  CONSTRAINT [FK_dbo_FactPositionExpense_AssignmentSK] FOREIGN KEY([AssignmentSK])
REFERENCES [DimAssignment] ([AssignmentSK])
ALTER TABLE [dbo].[FactPositionExpense] CHECK CONSTRAINT [FK_dbo_FactPositionExpense_AssignmentSK]
ALTER TABLE [dbo].[FactPositionExpense]  WITH CHECK ADD  CONSTRAINT [FK_dbo_FactPositionExpense_BudgetedSK] FOREIGN KEY([BudgetedSK])
REFERENCES [DimBudgeted] ([BudgetedSK])
ALTER TABLE [dbo].[FactPositionExpense] CHECK CONSTRAINT [FK_dbo_FactPositionExpense_BudgetedSK]
ALTER TABLE [dbo].[FactPositionExpense]  WITH CHECK ADD  CONSTRAINT [FK_dbo_FactPositionExpense_CheckDateSK] FOREIGN KEY([CheckDateSK])
REFERENCES [DimDate] ([DateSK])
ALTER TABLE [dbo].[FactPositionExpense] CHECK CONSTRAINT [FK_dbo_FactPositionExpense_CheckDateSK]
ALTER TABLE [dbo].[FactPositionExpense]  WITH CHECK ADD  CONSTRAINT [FK_dbo_FactPositionExpense_CheckTypeSK] FOREIGN KEY([CheckTypeSK])
REFERENCES [DimCheckType] ([CheckTypeSK])
ALTER TABLE [dbo].[FactPositionExpense] CHECK CONSTRAINT [FK_dbo_FactPositionExpense_CheckTypeSK]
ALTER TABLE [dbo].[FactPositionExpense]  WITH CHECK ADD  CONSTRAINT [FK_dbo_FactPositionExpense_CompensationTypeSK] FOREIGN KEY([CompensationTypeSK])
REFERENCES [DimCompensationType] ([CompensationTypeSK])
ALTER TABLE [dbo].[FactPositionExpense] CHECK CONSTRAINT [FK_dbo_FactPositionExpense_CompensationTypeSK]
ALTER TABLE [dbo].[FactPositionExpense]  WITH CHECK ADD  CONSTRAINT [FK_dbo_FactPositionExpense_EmployeeSK] FOREIGN KEY([EmployeeSK])
REFERENCES [DimEmployee] ([EmployeeSK])
ALTER TABLE [dbo].[FactPositionExpense] CHECK CONSTRAINT [FK_dbo_FactPositionExpense_EmployeeSK]
ALTER TABLE [dbo].[FactPositionExpense]  WITH CHECK ADD  CONSTRAINT [FK_dbo_FactPositionExpense_GLAccountSK] FOREIGN KEY([GLAccountSK])
REFERENCES [DimChartOfAccount] ([GlAccountSK])
ALTER TABLE [dbo].[FactPositionExpense] CHECK CONSTRAINT [FK_dbo_FactPositionExpense_GLAccountSK]
ALTER TABLE [dbo].[FactPositionExpense]  WITH CHECK ADD  CONSTRAINT [FK_dbo_FactPositionExpense_GlCodeSK] FOREIGN KEY([GlCodeSK])
REFERENCES [DimGlCode] ([GlCodeSK])
ALTER TABLE [dbo].[FactPositionExpense] CHECK CONSTRAINT [FK_dbo_FactPositionExpense_GlCodeSK]
ALTER TABLE [dbo].[FactPositionExpense]  WITH CHECK ADD  CONSTRAINT [FK_dbo_FactPositionExpense_OrgUnitSK] FOREIGN KEY([OrgUnitSK])
REFERENCES [DimOrgUnit] ([OrgUnitSK])
ALTER TABLE [dbo].[FactPositionExpense] CHECK CONSTRAINT [FK_dbo_FactPositionExpense_OrgUnitSK]
ALTER TABLE [dbo].[FactPositionExpense]  WITH CHECK ADD  CONSTRAINT [FK_dbo_FactPositionExpense_PositionSK] FOREIGN KEY([PositionSK])
REFERENCES [DimPosition] ([PositionSK])
ALTER TABLE [dbo].[FactPositionExpense] CHECK CONSTRAINT [FK_dbo_FactPositionExpense_PositionSK]


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


EXEC sys.sp_addextendedproperty @name=N'Table Description', @value=N'The fact will contain a row per Employee per Position per Assignment per pay check per budget component per account number for p' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FactPositionExpense'


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


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Key identifier for the employee''s Assignment, used to reference the assignment dimension.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FactPositionExpense', @level2type=N'COLUMN',@level2name=N'AssignmentSK'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Key identifier for the budgeted information, used to reference the budgeted dimension.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FactPositionExpense', @level2type=N'COLUMN',@level2name=N'BudgetedSK'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Key identifier for the date the employee was paid or the check date, used to reference the date dimension.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FactPositionExpense', @level2type=N'COLUMN',@level2name=N'CheckDateSK'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Key identifier for the type of check with which the employee was paid, used to reference the Check Type dimension.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FactPositionExpense', @level2type=N'COLUMN',@level2name=N'CheckTypeSK'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Key identifier for the type of budget component, used to reference the budget component dimension.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FactPositionExpense', @level2type=N'COLUMN',@level2name=N'CompensationTypeSK'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Key identifier for the Employee, used to reference the employee dimension.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FactPositionExpense', @level2type=N'COLUMN',@level2name=N'EmployeeSK'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The number (in some cases the average hours not actual) of hours worked by an employee on an assignment for a week.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FactPositionExpense', @level2type=N'COLUMN',@level2name=N'ExpenseAmount'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Key identifier for the full account number, used to reference the Chart of Accounts dimension.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FactPositionExpense', @level2type=N'COLUMN',@level2name=N'GLAccountSK'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Key identifier for the General Ledger Code of the account (last 9 digits of the account), used to reference the GLCode dimensio' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FactPositionExpense', @level2type=N'COLUMN',@level2name=N'GlCodeSK'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Key identifier for the Organizational Unit of the account (first 16 digits of the account), used to reference the OrgUnit Dimens' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FactPositionExpense', @level2type=N'COLUMN',@level2name=N'OrgUnitSK'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Primary Key to the Fact.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FactPositionExpense', @level2type=N'COLUMN',@level2name=N'PositionExpenseSK'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Key identifier for the employee''s Position, used to reference the position dimension.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FactPositionExpense', @level2type=N'COLUMN',@level2name=N'PositionSK'
  Sample Data
AssignmentSK BudgetedSK CheckDateSK CheckTypeSK CompensationTypeSK EmployeeSK ExpenseAmount GLAccountSK GlCodeSK OrgUnitSK PositionExpenseSK PositionSK
161720 2 20141114 11 100 7726 268.29 3091190 6088 12504 2587817 2464
101224 -1 20020815 -1 74 9502 10.50 2751417 6415 12939 931829 2258
78839 -1 20070914 11 30 3819 370.68 2887198 6361 13680 701718 2136
224325 2 20160729 11 102 15567 129.46 3198259 6352 12598 876318 1481
117690 -1 20081114 -1 44 5427 722.00 2915546 6432 13593 3971123 1510
31196 -1 20050415 -1 74 847 12.00 2808802 6415 13136 1605857 1224
74596 2 20101215 -1 44 3712 846.00 2984242 6432 14472 1319199 2647
116572 -1 20150529 11 30 5280 92.86 3103406 6361 14436 4887712 1702
73421 -1 20160615 11 77 3061 10.48 3121285 6360 13054 681511 4025
123006 -1 20050831 11 30 6236 8.21 2837654 6361 13596 534026 4791
Powered by BI Documenter