Dict-Physical

Table MF_LeaveAccrual

Card of table MF_LeaveAccrual

Name	MF_LeaveAccrual
Dimensional Type	Fact
Comment	Leave amount changes (accruals or reductions), by person, date, and leave type. Please be sure to break these numbers up by fiscal year when reporting, since we handle leave accruals on a per-fiscal-year basis. If carryover is allowed for a particular leave category, we add the carryover in July in a block. As a result, numbers here cannot be aggregated across fiscal years. The delivered Datatel ODS has views that can be used to populate this table: ODS_PERLVDTL and ODS_LEAVE_ACCRUAL. The former is more useful than the latter. NOTE WELL: Employee information has only been accumulating, historically, in the data warehouse for a relative short time (started in 2009). And as of 2011 we have not done any historical load, i.e., we have not attempted to reconstruct history father back than that. What this means is that leave accrued, used, etc. before we started tracking employee history in the data warehouse will be linked to HR information on that person as of 2009 (when we started accumulating data). TO BE SAFE, THEREFORE, only report on leave for years >= 2009.

Check constraint name of the table MF_LeaveAccrual

CKT_MF_LEAVEACCRUAL

List of outgoing references of the table MF_LeaveAccrual

Relationship_150

MD_LeaveInfo (Shortcut)

MD_LeaveInfo_SurrogateKey

Relationship_151

MD_Employee (Shortcut)

MD_CollPers_SurrogateKey

Relationship_154

MD_Date (Shortcut)

MD_Date_DateInteger

List of referencing views of the table MF_LeaveAccrual

VMF_LeaveAccrual

VMF_LEAVEACCRUAL

List of diagrams containing the table MF_LeaveAccrual

StarsEmployeeDiagram

List of columns of the table MF_LeaveAccrual

MD_LeaveInfo_SurrogateKey

<None>

numeric

MD_CollPers_SurrogateKey

<None>

numeric

MD_Date_DateInteger

Integer representation of the date (e.g., 20070101 for 1 Jan 2007). Defaults to 99991231, for situations where a date is required but none is available.

DW_DateInteger

int

MF_Leave_AccrualID

ID associated with accrual in Colleague. Implemented as a degenerate dimension here. Do not expose to users!

Could be used as the sole primary key for this fact table, but we don't want to count on that. Hence we use it in conjunction with the date and the employee foreign keys.

DW_CodeDesc

varchar(32)

MF_Leave_ColleagueID

Generally should not be exposed to users. It is used by the ETL loads instead of the employee's surrogate key, which might be revised if we load more history into MD_Employee.

DW_ColleagueID

char(7)

MF_Leave_IsEnded

'Yes' if a given leave action (accrual, decrease, etc.) applies to a plan that, for a given employee, has ended as of the last data warehouse load. 'No' otherwise.

Use this value as a filter, to remove non-current accrual information.

DW_YesOrNoNOTNULL

char(3)

MF_Leave_Amount

<None>

float

MF_Leave_MaxAccrual

Not currently populated.

Some leave plans have no max accrual (i.e., accrual limit), so this value may be null. The value here is the max as of the date of the accrual.

When aggregated, the values in this field should be aggregated by taking the rightmost or max() value, not the sum.

<None>

float

MF_Leave_Balance

Balance available for a given leave category and a given person at a given time.

Generally this should be calculated by aggregating on MF_Leave_Amount, not by using this field.

When aggregated, it should be aggregated by taking the rightmost or max() value, not the sum.

<None>

float

MF_Leave_ForwardingBalance

In general, don't use this field unless you're sure you know what it is and what you're doing.

When aggregated, it should be aggregated by taking the rightmost or max() value, not the sum.

<None>

float

MF_Leave_Action

Single-letter code designating whether the MF_Leave_Amount is and accrual (A), usage (U), etc.

This attribute is generally not not needed because the value of MF_Leave_Amount will be negative in the case of a U, and positive in the case of an A. In some cases, one may want to look only at usage/reductions and not accruals or vice versa. In such cases, this attribute would be useful, although it is also available on the MD_LeaveInfo dimension.

<None>

char(1)

MF_Leave_Count

Simply counts leave accrual/usage actions. Generally not useful.

<None>

int

MF_Leave_AuditKey

DW_AuditKey

bigint

MF_Leave_Timestamp

<None>

timestamp