|
MD_Pos_SurrogateKey
|
|
<None>
|
numeric
|
|
|
MD_GLAcc_SurrogateKey
|
|
<None>
|
numeric
|
|
|
MF_PosFund_Instance
|
Instance = fiscal year. This terminology ('instance') is used on Colleague financial aid and general-ledger files, for which new 'instances' are created every fiscal year. Each file is an 'instance' and carries an instance or fiscal-year key.
Put differently, all data on position funding is kept and updated on a per-fiscal-year basis. The fiscal year or 'instance' is therefore part of the primary key for many business-related fact tables. In this case, funding for a position is budgeted and recorded in lock step with other business processes, and therefore must be tagged with a fiscal year.
|
<None>
|
int
|
|
|
MF_PosFund_Percentage
|
The percentage of a given position's funding drawn annually from a given GL account.
Note that the grain of the table that holds this field is one row per position per GL account. A position may be funded through multiple GL accounts. For example, one GL account may fund 75% of a position, and another just 25%. This field holds the percentage, as a floating point number (where 1 = 100%, .5 = 50%, etc.).
The grain is NOT one row per position per person per GL account. In Colleague terms, this fact table links in at the position, not per-position, level. We are not interested in individual job holders per se, that is, but rather in their positions. Positions at Carleton may be occupied by more than one person, and we just don't care about that level of detail here.
|
<None>
|
float
|
|
|
MF_PosFund_ActualWagesOrSalary
|
Amount actually paid out, over the course of the current fiscal year, in salary and base wages + overtime, from a given GL account.
(Remember that the grain of the MF_PositionFunding fact table is one row per position per GL account (positions are sometimes funded out of multiple accounts.)
This field is not currently populated.
|
<None>
|
money
|
|
|
MF_PosFund_AnnualizedWagesOrSalary
|
Cost to a given GL account, annualized, of a given position (which may be occupied by more than one person). This is calculated by adding up all the yearly salaries/base wages for the people who hold a given position * (percent of position funded by this account expressed as a floating point number).
The cost here is annualized, i.e., it reflects what would be spent if the person worked for a full year. This is most useful for budget/planning purposes.
We do not include benefits in this calculation, basically because doing so would require that HR maintain a lot more data than it currently does, and because it would make calculations much more difficult. Carleton has standard ways of converting aggregate annualized salary figures to total cost estimates (including benefits), so this calculation method should be adequate for budgeting/planning purposes.
Note that this field does not reflect the theoretical cost to the college, if a position is fully staffed. (Positions often are either unfilled or partially filled.)
Note, finally, that this figure (i.e., the annualized salary at the per-GL-account level or grain) may differ, transiently, from the salary entered at the per-position level (MF_PerPosition), because we sometimes enter a person's salary at the per-position level before we've figured out exactly what GL account(s) the money for the salary is supposed to come out of.
|
<None>
|
money
|
|
|
MF_PosFund_ProjectedWagesOrSalary
|
This field holds the cost of a position (or if it's not fully staffed, the potential cost if it were fully staffed) in terms of salary and base wages.
Salary or wages for positions that are not filled is calculated using the midpoint of the salary range. So, e.g., if a position is authorized as 2.0 FTE (implying that we hire two people to fill it), and if neither person has been hired yet, then the projected wages or salary will be 2 x the midpoint of the salary range for the grade of that position. If, on the other hand, a position is defined as 2.0 FTE, and we have one person working full time in the position (1.0 workload), then the value for this attribute would be the actual salary of the one full-time person plus (midpoint of the salary range times (2.0 - 1.0)). In other words, we use the midpoint of the salary range to guess at what the total cost to Carleton would be if the position were fully filled.
The full calculation works out as follows:
If authorized FTE <= actual workload (i.e., the authorized FTE count - actual amount people are working in the position[s])
- Then projected cost to college = actual annualized cost to college * (% of position funded by a given account, expressed as a floating point num)
- Else projected cost to college =
(actual annualized cost to college + ((midpoint of salary range for position) * (authorized FTE - actual workload)))
* (percent of this position funded by a given account, expressed as a floating point num)
Technically, an error condition should exist if authorized FTE < actual workload, but life itself is full of error conditions. Yet it goes on, as do we - here.
|
<None>
|
money
|
|
|
MF_PosFund_ProjectedMaxWagesOrSalary
|
Calculated as MF_PosFund_ProjectedWagesOrSalary, except that we use the maximum base wage or salary for the salary range in question when calculating the total. This will give us the presumed maximum base wages/salary Carleton would be spending for a given position, if that position is/were fully staffed.
See MF_PosFund_ProjectedWagesOrSalary.
|
<None>
|
money
|
|
|
MF_PosFund_ProjectedMinWagesOrSalary
|
Calculated as MF_PosFund_ProjectedWagesOrSalary, except that we use the minimum base wage or salary for the salary range in question when calculating the total. This will give us the presumed minimum base wages/salary Carleton would be spending for a given position, if that position is/were fully staffed.
See MF_PosFund_ProjectedWagesOrSalary.
|
<None>
|
money
|
|
|
MF_PosFund_Workload
|
Actual workload summed across all employees holding a given position, allocated/divided up by GL account (to match the grain of this table).
Note that at Carleton we use the terms FTE and 'Workload' in confusing ways. At the position level, the FTE attribute indicates how many FTEs have been allocated, by HR, to a particular position (e.g., 10 1/2 time employees = 5 FTE). At the person-in-a-position (perpos) level, the FTE is used for something else, and Workload is the attribute that tells us what percentage of full time an individual person is working in a particular position.
This field here reflects the sum of workloads for all individuals currently working in a given position, allocated according to budgets (e.g., if two people are working in a particular position full time, and that position is paid out of two GL accounts, split 50-50, then each of those GL accounts will get a workload of 1).
Basically this is what most organizations would call the "actual FTE" working in a given position, allocated out to the appropriate accounts.
|
<None>
|
float
|
|
|
MF_PosFund_PositionFTE
|
Full workload value assoicated with a given position (the workload allocated/budgeted; not what is actually filled by existing employees).
Note that at Carleton we use FTE and 'Workload' in confusing ways. At the position level, the FTE indicates how many FTEs have been allocated, by HR, to a particular position (e.g., 10 1/2 time employees = 5 FTE). At the person-in-a-position (perpos) level, the FTE is used for something else, and Workload is the attribute that tells us what percentage of full time an individual person is working in a particular position.
|
<None>
|
float
|
|
|
MF_PosFund_Count
|
Count of people+position combinations (a person may fill more than one job, so the count of people in positions exceeds the number of actual employees at Carleton).
|
<None>
|
int
|
|
|
MF_PosFund_AuditKey
|
|
DW_AuditKey
|
bigint
|
|
|
MF_PosFund_Timestamp
|
|
<None>
|
timestamp
|
|