Employee schedule fact table

TableName

Field

Field Type

Description

PrimaryKey

MappedDimensionTable

DimensionTableField

Bi_FactEmployeeSchedule_s3

BreakType

DECIMAL(19,0)

Indicates what type of break is taken by the employee

Bi_DimEmployeeBreakCategory_s3

EmployeeBreakCategoryPK

Bi_FactEmployeeSchedule_s3

CenterID

VARCHAR(36)

A unique identifier for the center, generated in Centre dimension(Bi_DimCentre).

Bi_DimCenter_s3

centerID

Bi_FactEmployeeSchedule_s3

EmployeeId

VARCHAR(36)

A unique identifier for an employee(Bi_Dimuser).

Bi_DimUser_S3

userid

Bi_FactEmployeeSchedule_s3

EndTime

TIMESTAMP

The end date and time corresponding to the 'record name'.

For example, if the record name is 'Schedule', this field populates the end date and time of the schedule.

Bi_FactEmployeeSchedule_s3

etlCreatedBy

DECIMAL(19,0)

ETL system admin user who inserted or updated the corresponding record in this table.

Bi_FactEmployeeSchedule_s3

etlCreatedDate

TIMESTAMP

Latest timestamp at which the corresponding record was either inserted or updated in this table.

Bi_FactEmployeeSchedule_s3

ExpectedCheckinTime

FLOAT

The time at which the guest is expected to check-in.

Bi_FactEmployeeSchedule_s3

ExpectedCheckoutTime

FLOAT

The time at which the guest is expected to check-out.

Bi_FactEmployeeSchedule_s3

FactEmployeeScheduleId

DECIMAL(19,0)

A unique identifier for each record generated in the fact table.

Yes

Bi_FactEmployeeSchedule_s3

OrganizationId

VARCHAR(36)

A unique identifier for the organition for which this data belongs

Bi_DimCenter_s3

OrganizationId

Bi_FactEmployeeSchedule_s3

OutsideScheduleInMinutes

FLOAT

Break minutes outside schedule

Bi_FactEmployeeSchedule_s3

PodName

VARCHAR(20)

Internal to the ETL, says from which POD data is being synced the table

Bi_FactEmployeeSchedule_s3

RecordPk

DECIMAL(19,0)

The primary key constraint column for the current record in core system.

Bi_FactEmployeeSchedule_s3

RecordType

SMALLINT

type of task for which time is assigned like attendance, break, schedule, etc..

Bi_FactEmployeeSchedule_s3

RecoveryTime

INT

The total recovery time (in minutes) taken by the employee within the scheduled time. Only appointments that are in (Open, Closed, Check-in, Confirmed) statuses are considered. This is populated only when the 'record name' is 'Schedule' or 'StagedSchedule'.

Bi_FactEmployeeSchedule_s3

RoomID

VARCHAR(36)

Indicates the room assigned to service provider in the given schedule

Bi_DimResource_s3

ResourceId

Bi_FactEmployeeSchedule_s3

ScheduleDate

TIMESTAMP

The date associated with the corresponding record.

Bi_FactEmployeeSchedule_s3

ServiceTime

FLOAT

The total time (in minutes) for which the employee provided a service within the scheduled time.

Only appointments that are in (Open, Closed, Check-in, Confirmed) statuses are considered. This includes the recovery time and is populated only when the 'record name' is 'Schedule' or 'StagedSchedule'.

Bi_FactEmployeeSchedule_s3

StartTime

TIMESTAMP

The start date and time corresponding to the 'record name'.

For example, if the record name is 'Schedule', this field populates the start date and time of the schedule.

Bi_FactEmployeeSchedule_s3

StatusType

DECIMAL(19,0)

Status of the schedule

Bi_FactEmployeeSchedule_s3

void

BOOLEAN

Indicates if the row should be inserted or deleted. 1- delete, 0-insert

Bi_FactEmployeeSchedule_s3

WithInScheduleInMinutes

FLOAT

Break minutes with in schedule

Bi_FactEmployeeSchedule_s3

WorkTaskID

NVARCHAR(MAX)

The unique identifier of the work task. 

These are tasks that employees can undertake beyond their principal job

bi_factemployeeschedule_s3

IsMandatoryBreakComplete

BOOLEAN

Indicates if employee completed mandatory break

bi_factemployeeschedule_s3

TimeOffInMinutes

INTEGER

Timeoff employee has taken in minutes in the day

bi_factemployeeschedule_s3

VacationTimeOffInMins

FLOAT

Indicate number of minutes employee has logged as vacation hours

bi_factemployeeschedule_s3

HolidayTimeOffInMins

FLOAT

Indicates number of minutes employee has logged as holiday hours

bi_factemployeeschedule_s3

groupid

BIGINT

Internal to Zenoti. Used for data processing.