EnterpriseDW
 dbo.FactHoursReportedByDay (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.FactHoursReportedByDay
  Properties
Property Value
Name FactHoursReportedByDay
Schema dbo
Row Count 2573750
Data Size 98296 KB
Index Size 38384 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
DateReportedSK
int(10, 0)
False
False
False
 
Key identifier for the date the time was reported by the employee, used to reference the date dimension.
Foreign Key
EmployeeSK
int(10, 0)
False
False
False
 
Key identifier for the Employee, used to reference the employee dimension.
HoursReported
numeric(9, 2)
False
False
False
 
The number (in some cases the average hours not actual) of hours worked by an employee on an assignment for a week.
In Primary Key
HoursReportedByDaySK
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.
Foreign Key
TimeTypeSK
int(10, 0)
False
False
False
 
Key identifier for the type of hours worked by the employee, used to reference the time type dimension.
  Triggers
Name Insert Update Delete Instead
  Indexes
Name Clustered Columns
False
HoursReportedByDaySK
  Foreign Key Constraints
Name Columns Referenced Table Referenced Columns Checked
AssignmentSK
DimAssignment
AssignmentSK
True
DateReportedSK
DimDate
DateSK
True
EmployeeSK
DimEmployee
EmployeeSK
True
PositionSK
DimPosition
PositionSK
True
TimeTypeSK
DimTimeType
TimeTypeSK
True
  Check Constraints
Name Text Checked
  Parent Dependencies (objects that dbo.FactHoursReportedByDay depends on)
Name Type
Table
Table
Table
Table
Table
  Child Dependencies (objects that depend on dbo.FactHoursReportedByDay)
Name Type
TABLE
TABLE
TABLE
TABLE
TABLE
  Extended Properties
Object Property Value
FactHoursReportedByDay
Display Name
HoursByDay
FactHoursReportedByDay
Table Description
The fact will contain a row per employee per assignment per day hours of any type were reported by the employee. This will onl
FactHoursReportedByDay
Table Type
Fact
AssignmentSK
MS_Description
Key identifier for the employee's Assignment, used to reference the assignment dimension.
DateReportedSK
MS_Description
Key identifier for the date the time was reported by the employee, used to reference the date dimension.
EmployeeSK
MS_Description
Key identifier for the Employee, used to reference the employee dimension.
HoursReported
MS_Description
The number (in some cases the average hours not actual) of hours worked by an employee on an assignment for a week.
HoursReportedByDaySK
MS_Description
Primary Key to the Fact.
PositionSK
MS_Description
Key identifier for the employee's Position, used to reference the position dimension.
TimeTypeSK
MS_Description
Key identifier for the type of hours worked by the employee, used to reference the time type dimension.
   Annotations
Object Property Value
  DDL
/****** Object: Table [dbo].[FactHoursReportedByDay] Script Date: 03/09/2017 17:14:58 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[FactHoursReportedByDay](
    [HoursReportedByDaySK] [int] IDENTITY(1,1) NOT NULL,
    [EmployeeSK] [int] NOT NULL,
    [PositionSK] [int] NOT NULL,
    [AssignmentSK] [int] NOT NULL,
    [DateReportedSK] [int] NOT NULL,
    [TimeTypeSK] [int] NOT NULL,
    [HoursReported] [numeric](9, 2) NOT NULL,
 CONSTRAINT [PK_dbo.FactHoursReportedByDay] PRIMARY KEY NONCLUSTERED 
(
    [HoursReportedByDaySK] 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].[FactHoursReportedByDay]  WITH CHECK ADD  CONSTRAINT [FK_dbo_FactHoursReportedByDay_AssignmentSK] FOREIGN KEY([AssignmentSK])
REFERENCES [DimAssignment] ([AssignmentSK])
ALTER TABLE [dbo].[FactHoursReportedByDay] CHECK CONSTRAINT [FK_dbo_FactHoursReportedByDay_AssignmentSK]
ALTER TABLE [dbo].[FactHoursReportedByDay]  WITH CHECK ADD  CONSTRAINT [FK_dbo_FactHoursReportedByDay_DateReportedSK] FOREIGN KEY([DateReportedSK])
REFERENCES [DimDate] ([DateSK])
ALTER TABLE [dbo].[FactHoursReportedByDay] CHECK CONSTRAINT [FK_dbo_FactHoursReportedByDay_DateReportedSK]
ALTER TABLE [dbo].[FactHoursReportedByDay]  WITH CHECK ADD  CONSTRAINT [FK_dbo_FactHoursReportedByDay_EmployeeSK] FOREIGN KEY([EmployeeSK])
REFERENCES [DimEmployee] ([EmployeeSK])
ALTER TABLE [dbo].[FactHoursReportedByDay] CHECK CONSTRAINT [FK_dbo_FactHoursReportedByDay_EmployeeSK]
ALTER TABLE [dbo].[FactHoursReportedByDay]  WITH CHECK ADD  CONSTRAINT [FK_dbo_FactHoursReportedByDay_PositionSK] FOREIGN KEY([PositionSK])
REFERENCES [DimPosition] ([PositionSK])
ALTER TABLE [dbo].[FactHoursReportedByDay] CHECK CONSTRAINT [FK_dbo_FactHoursReportedByDay_PositionSK]
ALTER TABLE [dbo].[FactHoursReportedByDay]  WITH CHECK ADD  CONSTRAINT [FK_dbo_FactHoursReportedByDay_TimeTypeSK] FOREIGN KEY([TimeTypeSK])
REFERENCES [DimTimeType] ([TimeTypeSK])
ALTER TABLE [dbo].[FactHoursReportedByDay] CHECK CONSTRAINT [FK_dbo_FactHoursReportedByDay_TimeTypeSK]


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


EXEC sys.sp_addextendedproperty @name=N'Table Description', @value=N'The fact will contain a row per employee per assignment per day hours of any type were reported by the employee. This will onl' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FactHoursReportedByDay'


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


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'FactHoursReportedByDay', @level2type=N'COLUMN',@level2name=N'AssignmentSK'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Key identifier for the date the time was reported by the employee, used to reference the date dimension.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FactHoursReportedByDay', @level2type=N'COLUMN',@level2name=N'DateReportedSK'


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'FactHoursReportedByDay', @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'FactHoursReportedByDay', @level2type=N'COLUMN',@level2name=N'HoursReported'


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'FactHoursReportedByDay', @level2type=N'COLUMN',@level2name=N'HoursReportedByDaySK'


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'FactHoursReportedByDay', @level2type=N'COLUMN',@level2name=N'PositionSK'


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Key identifier for the type of hours worked by the employee, used to reference the time type dimension.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FactHoursReportedByDay', @level2type=N'COLUMN',@level2name=N'TimeTypeSK'
  Sample Data
AssignmentSK DateReportedSK EmployeeSK HoursReported HoursReportedByDaySK PositionSK TimeTypeSK
184284 20100422 10376 6.00 594790 3894 24
173976 20090224 14136 10.00 1461670 4815 24
92959 20050328 9526 6.50 774007 6406 24
105656 20070424 5708 5.00 2143027 7368 24
42104 20010129 1768 8.00 1128672 2658 33
98229 20030115 9699 4.00 494733 3976 24
18721 20021119 1466 2.00 2313376 4386 24
94590 20010612 9555 4.00 1072787 4703 24
111690 20030910 5847 5.00 765164 3626 24
110269 20070808 5202 4.00 408489 5173 24
Powered by BI Documenter