Dict-Physical

Table MF_FYAccountInfo

Card of table MF_FYAccountInfo

Name	MF_FYAccountInfo
Dimensional Type	Fact
Comment	Info on general-ledger accounts that changes on a per-fiscal-year basis, like the starting and ending balances. This fact table is more like an accumulating snapshot than a transacton fact table, in the sense that rows may be revisited and modified periodically (as, e.g., budgeted amounts get adjusted). The grain of this fact table is one row per fiscal year (instance) per account. Note that the 'instance' here follows business nomenclature, which uses the second year to designate a fiscal year (e.g., '2009-10' = instance or fiscal year 2010). This nomenclature is different from financial aid nomenclature, which uses the first year as the financial aid year (e.g., '2009-10' becomes aid year 2009). To reduce confusion, we encourage everyone to use the full fiscal/aid year name when reporting ('2009-10'). Views will be provided, as appropriate, to make this possible. Note that ending balances for active/current accounts, for the current fiscal year, are estimated. The estimates are based on historical trends—if enough history is available. If not enough history is available, the ending balance for the current fiscal year is estimated by multiplying the previous year's June 30th balance by 1.03. If no previous year is available, and we are eight months or more into the fiscal year, the end balance is estimated using the simple formula, (12 * current balance) / month of fiscal year. If nothing else is available, the budgeted amount is used, as long as the account isn't a control account, and as long as the budget value isn't NULL (if it is NULL, we go back to the above formula, but relax the eight month minimum; if the account is a control account we use the value zero as our default). The budgeted amount is also used as the estimated year-end balance for inactive accounts, for the current fiscal year. Starting balances, budgeted amounts, etc. are usually applied to control accounts, which are accounts whose GL number ends in 00. Put differently, the balances, budgets, etc. are typically defined at the subclass level (the account minus the trailing two digits. It's because of the way our GL is structured, this statement can be translated into the statement that these values are defined (primarily) for accounts ending in 00. And even though they are defined at the subclass level (or "control" level), in fact the amount ends up in practice being tied to a full 14-digit account number ending in "00." Sometimes a budget or other number will be defined at the "object" level, but in those cases business rules dictate that the numbers are not attached to the control account. If this seems ugly and complex, then seek therapy from inmates of the Business Office.

Check constraint name of the table MF_FYAccountInfo

CKT_MF_FYACCOUNTINFO

List of outgoing references of the table MF_FYAccountInfo

Relationship_34

MD_GLAccount (Shortcut)

MD_GLAcc_SurrogateKey

List of referencing views of the table MF_FYAccountInfo

VMF_FYAccountInfo

VMF_FYACCOUNTINFO

VW_ProjectedOverBudgetSubclasses

VW_PROJECTEDOVERBUDGETSUBCLASSES

List of diagrams containing the table MF_FYAccountInfo

StarsBusinessDiagram

List of columns of the table MF_FYAccountInfo

MD_GLAcc_SurrogateKey

<None>

numeric

MF_FYAcc_Instance

DW_YearAtCarleton

int

MF_FYAcc_14DigitCode

Used solely for internal bookeeping purposes. Do not expose to users. Contains GL account ID.

DW_GLCode

varchar(30)

MF_FYAcc_OpeningBalance

Opening account balance for a given account in a given fiscal year.

Contrary to most good data warehousing practice, this field may contain NULLs. There is no good default here, other than possibly zero (which we do not use).

<None>

money

MF_FYAcc_EstimatedOpeningBal

The DW team is not certain what this (rarely used) field is and how it's populated. It should therefore not (yet) be exposed to users.

Contrary to most good data warehousing practices, this field may be (and in fact is typically) NULL.

<None>

money

MF_FYAcc_BudgetedAmount

Budgeted amount for a given account. Note that a decimal place needs to be supplied here because the virtual field in Unidata/Colleague lacks one. This is done automatically in the VMF_FYAccountInfo fact-view.

This value is the workhorse of budget-vs-actual reporting.

Contrary to most good data warehousing practices, this field may be (and in fact is typically) NULL.

This field is generally defined, in Colleague, at the class level (11th and 12th digits of the GL account; first two digits of the object code). It may also be defined at the object level, although in that case we aggregate up to the class level.

Note that because BU transactions are being excluded (as of 2011 at least) from numbers in MF_GLFact, as they should be, this table becomes the source of budget numbers, at the per-year level. After conversations with the Business Office, it came out that they typically don't care about the vicissitudes of budget numbers through the year, the way they DO care about actuals. Our response was to store the currently known budgeted amount here in this table.

<None>

money

MF_FYAcc_YearEndActual

Year-end (i.e., June 30th) totals for a given account (actual as opposed to budget).

For the current fiscal year, the value here is an estimated amount, calculated using a data-mining query directed through the General Ledger MS Analysis Services database. For the current fiscal year, this number, in other words, is not an actual amount. It is a predicted amount.

The prediction algorithm used, primarily, is ARIMA. The particular parameters used for a particular prediction are available, but not exposed as such to users. And they may vary from one month to the next, and one account to the next, depending on which combinations give us the smallest variances. We do expose the variance. The square root of the variance (standard deviation) provides a reasonable measure of how confident we are in our predictions, although it is typically low. Cubes should only expose the standard deviation, since the variance could be more misleading to users.

Note that the predicted year-end total is really only useful at the account level. Once aggregated (due to missing data), the predictions fall farther and farther below what in most cases is a reasonable projection—and this without a commensurate increase in the aggregated variance. The usual reason for missing data is that the account is new and the ARIMA algorithm lacks sufficient historical depth to provide even a gross estimate. Bottom line: Use the predicted year-end total for individual accounts, and even then pay attention to the variances, and try, in general, to be discerning.

<None>

money

MF_FYAcc_YearEndActualVariance

This field is only for the statistically savvy. It is equal to zero where the year-end total (actual) is known. But for the current fiscal year, where the total is not known, it contains the variance supplied by the time-series prediction algorithm (ARIMA) that projects, for us, this fiscal year's ending total actual (as opposed to, say, the budget for the year). Basically what it tells us is how confident we can be in the year-end actual projections for the current fiscal year. The higher the number, the less confident we are. This is why past years' ending totals have a variance of zero (i.e., because we have confidence that they're right, because they're in the past).

Note that the variance should be converted to a standard deviation after aggregation, but before exposing the result to users. It's less confusing because its scale matches that of the predicted total.

Note also that the variances will typically be summed, which is perfectly OK for uncorrelated variables. In fact, most of the budgets aren't correlated. But realistically some are (we often shift spending around, and what one budget loses another gains). So take aggregated variances with something of a grain of salt.

<None>

money

MF_FYAcc_ApprovedBudgetDebit

Not currently used.

<None>

money

MF_FYAcc_ApprovedBudgetCredit

Not currently used.

<None>

money

MF_FYAcc_AllocatedBudgetDebit

Not currently used.

<None>

money

MF_FYAcc_AllocatedBudgetCredit

Not currently used.

<None>

money

MF_FYAcc_AuditKey

DW_AuditKey

bigint

MF_FYAcc_Timestamp

<None>

timestamp

List of indexes of the table MF_FYAccountInfo

MF_FYACCOUNTINFO_PK

TRUE

FALSE

MF_FYAccountInfo

ETL_OPTIMIZATION_INDEX

FALSE

MF_FYAccountInfo

List of keys of the table MF_FYAccountInfo

Identifier_1

Home