Dict-Physical

Table MF_GLFact

Card of table MF_GLFact

Name	MF_GLFact
Dimensional Type	Fact
Comment	GL fact table, which includes fiscal year, account, debit, credit, and transaction source. The GL is the fundamental financial pipeline through which all transactions get funneled at Carleton, as elsewhere. This fact table may potentially be confusing, because of the single MF_GLFact_Amount field, which is negative in the case of credits and positive in the case of debits. Views based on this table should therefore project the information contained here in alternate, more normal-person-friendly ways. Not all transactions that go into Carleton's general ledger are placed in this table. Specific exclusions include the following transaction codes: EP YE AB BU AE The nature and extent of these exclusions is, honestly, not going to matter to virtually anyone on campus other than the Comptroller. They are listed here for completeness' sake. Note that although BUs are excluded, budget figures are available in MF_FYAccountInfo, specifically in MF_FYacc_BudgetedAmount. In at least one year (2006), budget figures were input using BU transactions, instead of in the customary way. As a result, budget figures will appear to be missing from the data warehouse for that year.

Check constraint name of the table MF_GLFact

CKT_MF_GLFACT

List of outgoing references of the table MF_GLFact

Relationship_29

MD_GLAccount (Shortcut)

MD_GLAcc_SurrogateKey

Relationship_30

MD_GLTransactionSource

MD_GLTransSrc_SurrogateKey

Relationship_31

MD_Date (Shortcut)

MD_Date_DateInteger

Relationship_32

MD_GLAccount (Shortcut)

MD_GLAcc_ControlAccountKey

Relationship_38

MD_GLFactAnnotation

MD_GLFactNote_SurrogateKey

Relationship_126

MD_Vendor (Shortcut)

MD_Vend_SurrogateKey

List of referencing views of the table MF_GLFact

VW_GLInfoWithDynamicSecurity

VW_GLINFOWITHDYNAMICSECURITY

VW_ProjectedOverBudgetSubclasses

VW_PROJECTEDOVERBUDGETSUBCLASSES

VW_ProjectedUnderBudgetSubclasses

VW_PROJECTEDUNDERBUDGETSUBCLASSES

List of diagrams containing the table MF_GLFact

StarsBusinessDiagram

List of columns of the table MF_GLFact

MD_GLAcc_SurrogateKey

Foreign key link to MD_GLAccount dimension. Do not use, as is, in reports. Join with GL account dimension.

<None>

numeric

MF_GLFact_Instance

Degenerate dimension containing the "instance" or fiscal year associated with a given transaction. Instances are in YYYY format (not YYYY-YY format). So, e.g., the instance for fiscal year 2009-10 is 2009.

DW_YearAtCarleton

int

MF_GLFact_Counter

Each GL transaction is uniquely identified by a14-digit account number, an instance (fiscal year), and a counter. The counter is incremented for each transaction for a given fiscal year and account, thereby ensuring uniqueness for any given row.

This how the GL transaction table is implemented, not only here in the data warehouse, but also in Colleague itself.

<None>

int

MD_GLFactNote_SurrogateKey

<None>

numeric

MD_GLTransSrc_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.

This field ties a GL transaction to a date. Note that the value can be back-dated. It's not the actual 'last touched' date, which isn't used for reporting anyway.

DW_DateInteger

int

MD_Vend_SurrogateKey

<None>

numeric

MD_GLAcc_ControlAccountKey

Foreign key linking a GL entry to a GL control account.

Use this key (instead of MD_GLAcc_SurrogateKey) to aggregate GL summaries to the level of the control account.

Note that if a GL account has no control account, we treat it as its own control account.

<None>

numeric

MF_GLFact_ReferenceNumber

Degenerate dimension on GL fact table containing a reference number. Reference numbers are attached to every transaction that gets added to the general ledger fact table. These numbers provide additional information about where the transaction comes from that can be useful for audits, etc. Think of them as an expansion (or more detailed key) of the transaction source.

This field provides a link, in the case of vouchers, to vendors. The reference number in that case is the voucher number. In the case of checks (including ACH e-checks) the reference number is the check number.

The reference number should generally not be used for reporting purposes, but if it is used, it is typically best used in conjunction with the transaction source (q.v.).

DW_GLCode

varchar(30)

MF_GLFact_Amount

Dollar amount of transaction. If a credit, will be negative. If a debit will be positive. Think of a credit as a negative debit.

Only an accountant would do such a thing.

<None>

money

MF_GLFact_EntryDate

This is a timestamp field, essentially, that tells us when a transaction was added to the general ledger. In Colleague such fields currently only go down to the 'day' granularity level, but this may change. As result, this field has a datetime data type.

This field does NOT hold the transaction date, i.e., the date that we, as an institution, place on a transaction (which may be something we back-date).

This field is included in the GL account fact table for completeness' sake, and to facilitate incremental updates of the data warehouse. It should generally not be used for reporting.

<None>

datetime

MF_GLFact_AuditKey

DW_AuditKey

bigint

MF_GLFact_Timestamp

<None>

timestamp