Understanding the FMS Warehouse Structure

Data marts are logical divisions within the FMS Warehouse and are comprised of subject-specific dimensional data models designed around a specific institutional process. The FMS Warehouse includes the Enterprise Service Automation (ESA) data mart, General Ledger and Profitability data mart, Payables data mart, and Receivables data mart.

Image: FMS Warehouse Data Marts and Business Processes

This example illustrates the fields and controls on the FMS Warehouse Data Marts and Business Processes. You can find definitions for the fields and controls later on this page.

Each data mart is associated with a business process that helps you answer the questions you need to keep your organization robust and ahead of its competition. With each data mart, PeopleSoft presents the associated business process and the fact tables that will help you answer your critical business questions.

This section discusses:

Enterprise Service Automation (ESA) data mart
General Ledger and Profitability data mart
Payables data mart
Receivables data mart

Enterprise Service Automation (ESA) Data Mart

Analyzing projects and contracts is an increasingly important, yet difficult task in the growing services economy. Whether you are part of a professional services organization, an internal IT department, a research organization, or a capital project accounting department, the Enterprise Service Automation data mart helps you analyze project performance; contract profitability; resource utilization; and time and expense compliance. The ESA data mart includes data that you need to calculate standard measures such as Proposal Pipeline, Project Profitability, Contract Revenue Recognized, Estimate to Complete, Resource Utilization, and Earned Value. With this information, you can measure your organization against external benchmarks. You can create a baseline for your own results, and then work towards improving project performance over time, as specified by your overall corporate strategy.

Project Management Business Process

The ESA data mart is tied to PeopleSoft's Project Management business process. The Project Management business process fulfills an organization's requirements for the delivery of professional or internal services, such as planning, prioritizing, and managing resources and projects. If customer focused, this business process includes managing contracts, as well as contract changes, billing, and revenue information. Both the customer and internally focused process includes capturing project costs, adjusting projects, and settling expenses, and so on.

The information captured within the Project Management business process helps you determine answers to questions such as "How is my project performing?", "Is this contract profitable?", "What is the organization's resource utilization?" and "How much of the corporate expenses are compliant with policy?"

Create Project, Execute Project, and Assign Resources to Project are business sub-processes related to the ESA data mart. These business sub-processes aid you in areas such as creating projects and tasks, defining budget and resource requirements, tracking and adjusting projects.

Grant Analytics Business Process

The ESA data mart is tied to PeopleSoft's Grants Management business process. The ESA data mart supports the data necessary for analyzing Grants activities, including pre-award analytics, to answer questions about outstanding proposals, funding, and budgets defined for proposals, and post-award analytics, to answer questions about post-award activities such as receipt of awards, project budgets, billing, receivables and expenditures activity related to the grant.

The information captured within the Grant Analytics business process helps you determine answers to questions such as:

What proposals are outstanding and how much funding do they represent?
What are the budgets defined for my research proposals?
What F&A amounts are associated with my proposal budgets?
What is the breakdown of funding requests by department and/or principal investigator?
What is the total amount of proposals that were rejected versus funded?
What amounts were budgeted, awarded, and proposed?

Enterprise Service Automation Data Mart Delivered Fact and Dimension Tables

This section discusses the delivered fact and dimension tables for the ESA data mart.

Enterprise Service Automation Data Mart Fact Tables

The following table describes the delivered ESA data mart fact tables.

Note: In the table, the Helps Answer column includes examples of the type of answers a fact table can provide; it does not contain the complete list of answers.

Fact Name	Fact Record Name	Description	Helps Answer
Assignment	F_ASGN	Represents information about assignments. (Assignments pair a resource with a unit of work.)	Average length of an assignment. Average billing rate for an assignment. Number of hours scheduled against a particular assignment. Bill rate for an employee associated with an assignment. Bill rate for the project role associated with an assignment. Cost rate for the project role associated with an assignment.
Contract Amendment	F_CA_AMD	Captures the change in the obligations and entitlements of a contract. The negotiated and discount amount changes are at the contract level.	Total amount attributed to contract amendments in a period. Average percent increase due to contract amendments. Average gross amendment change for a given contract or customer. Total gross/negotiated change for a given contract or customer. Total discount and surcharge for a given contract or customer.
Contract Distribution Transaction Detail	F_CA_DTL_DIST	Stores information relating to accounting distributions of a contract line.	Average amount allocated to a given combination of General Ledger ChartFields. Total amount allocated to a given combination of General Ledger ChartFields.
Contract Forecast Current	F_CA_CUR_FRCST	Captures the current revenue backlog for both fixed-fee and rate-based contract lines and the probability percentage of the revenue forecast.	Percentage change over time for a contract revenue forecast. Current total contract revenue forecast for a period. Total forecast revenue for a given contract, customer, product, or period. Probability associated with our forecasted contract revenue.
Contract Transaction Detail	F_CA_DTL_TRANS	Stores information relating to a contract line by capturing the contract terms and revenue measures. The measures are at the contract line level of detail.	Average discount given on a contract line. Largest or smallest discount given on a contract. Total discount given on all contract lines. Minimum contract line gross amount for a particular contract, customer, or product. Maximum contract line net amount for a particular contract, customer, or product. Average contract line list amount for a particular contract, customer, or product.
Contract Forecast Periodic	F_CA_PRDC_FRCST	Captures the trend of revenue backlog for both fixed-fee and rate-based contract lines and the probability percentage of the revenue forecast.	See notes for Contract Forecast Current (F_CA_CUR_FRCST) table.
Contract Renewal	F_CA_RNW	Captures the contract renew amount and percentage increase over the different contracts and different renewal cycles.	Average percentage increase in contract renewals. Total amount attributed to contract renewals for a period. Total contract renewal amount for a given contract, customer, product. Renewal percentage increase or decrease for a given contract, customer, product.
Contract Revenue Recognition	F_CA_REV_RECOGN	Contains the revenue recognized and reversed accounting entries for a contract line.	Total revenue amount recognized during a period. Revenue earned from a given customer. Average deferred revenue age for a given customer.
Expense Distribution Transaction Detail	F_EXP_DTL_DIST	Stores measures related to expense transactions and captures the related accounting information and associated amounts.	Average cost of an expense type (such as a hotel stay). Maximum amount spent on an expense type. Average number of nights spent in a hotel. Number of nights associated with an expense distribution level. Number of passengers associated with an expense distribution level.
Expense Report Approval	F_EXP_RPT_APPRV	Stores measures related to an expense report's approval and captures information relating to approvers, types, and approval dates.	Average length of time it takes for an expense report to be approved. Approvers taking the longest to approve expense reports. Number of expense approvals in a given period. Number of expense reports associated with a particular employee, by status.
Employee Forecast	F_FCST	Stores information about employee forecasts.	Revenue forecast for my organization for a given period. Forecast utilization for my organization for a given period. Total forecast amount. Number of hours an employee is forecasted to work on a given project.
Grants Award	F_GM_AWARD	Contains award activity information, including the detail funding or budget information associated with an award.	Awards this year verses last year. Sponsor awards funded.
Grants Project Transactions	F_GM_PRJ_TRAN	Contains grant-specific project transaction activity information to enable you to analyze grants project budgets and actual transactions, such as expenses and revenues.	Average number of research versus non research professionals per proposal/activity. Total cost of a project. Total revenue of a project. Total cost of an activity. Total revenue for an activity for a specific accounting period.
Proposal Details	F_GM_PROPOSAL	Contains details of the pre-award (proposal) business process, which enables analysis of proposed budget amounts at the proposal, version and proposed project level. The granularity is at business unit, proposal, version and proposed project. Proposal version activity is stored.	Budgets defined for a research proposal. Direct costs of a proposed budget verses indirect cost. F&A amounts associated with a proposal budget. Proposals with cost-sharing budgets with other institutions. Breakdown of proposal requests by department and/or PI (principal investigator).
Proposal Award Summary	F_GM_PRP_AWD	Contains summary information of the proposal and award business processes at a very high level, for the purpose of success rate analysis. The granularity is at business unit and proposal. Proposal version activity is not stored. The Fact is of the type accumulating snap shot; existing rows in the fact table may need to be updated as proposal activity takes place in the source system.	Total amount of proposals rejected verses funded. Proposal processing time from submission to funding. Breakdown of funding requests by department and/or PI (principal investigator). Success rate percentages calculated for both count and amount.
Project Activity Current	F_PRJ_AC_CUR	Stores current project activities (tasks) measures.	Actual cost of work performed (ACWP) for an activity. Budgeted cost of worked scheduled (BCWS) for an activity. Budgeted cost of work performed (BCWP) for an activity. Percent complete for an activity. Comparison of baseline to actual hours for an activity. Number of work hours required to complete an activity.
Project Activity Periodic	F_PRJ_AC_PRDC	Stores trend project activities (tasks) measures.	See notes for Project Activity Current (F_PRJ_AC_CUR) table.
Project Change	F_PRJ_CHNG	Stores project change control information.	Average number of changes per project. Change count by project, project activity, change type, change reason, user, or employee.
Project Current	F_PRJ_CUR	Stores current project measures.	Budgeted cost of worked scheduled for a project. Budgeted cost of work performed for a project. Actual cost of work performed for a project. Percent complete for a particular project. Comparison of budget to actual work hours. Comparison of actual to budget amounts for an activity. Number of billed hours associated with a project. Costs which have not been billed in the billing or accounts receivable system.
Project Deliverable	F_PRJ_DELV	Stores project deliverable information.	Average number of deliverables per project. Deliverables count by project, project activity, deliverable status, or employee.
Project Issue	F_PRJ_ISS	Stores information about project issues. Project issues may be optionally related to a project activity.	Average number of issues per project. Issue count by project, project activity, issue type, issue status, issue priority, or employee.
Project Periodic	F_PRJ_PRDC	Stores trend project measure information.	See notes for Project Current (F_PRJ_CUR) table.
Project Transaction	F_PRJ_TRAN	Stores project financial transactions data. With the exception of programs (summary projects), each project at the most basic level is composed of transactions. These transactions contain the quantity and amounts associated with each transaction.	Number of hours billed against a project. Budget versus actual for a project. Total cost of a project. Total revenue of a project. Total cost of an activity. Total revenue for an activity.
Proposal Grant Transaction Detail	F_PRP_VER_TRANS	Captures the information relating to proposals and grants. For proposals, it captures the different versions of project proposals created for business opportunities. For grants, it captures information relating to proposals, budgets, and so on.	Average estimated margin percentage for all proposals. Percentage of proposals won. Probability that a proposal will be approved. Number of work days required for a given project role. Anticipated labor costs for a given project role.
Resource Rate	F_RSRC_RT	Stores resource actuals, employee, and job code rates. It allows for analysis across different rates.	Cost for a resource. Actual resource rate versus bill rate. Number of hours to complete an activity. Employee standard bill rate compared to the actual rate, job code rate, and project role rate for a given project activity. Actual bill rate compared to the job code bill rate for a particular project. Actual bill rate compare to the project role bill rate for a particular project. Project role cost rate compare to the job code cost rate for a particular project activity.
Service Order	F_SO	Stores information about service orders (SO). SOs submit, track, and fulfill requests for services from external or internal customers. A SO defines the required services for a project and specifies the criteria that the system uses.	Average number of days requested on a service order. Total number of resource requests in days. Number of estimated days for all service orders associated with a contract. Average project role bill rate for all service orders related to a project. Average project role cost for service orders associated with a particular contract.
Time Report Daily Detail	F_TIME_RPT	Stores information relating to a person's time report entry. It captures the related accounting information and associated hours. The details are captured at a day-level.	Average number of billable hours entered in a period. Resource utilization of my organization in a period. Ratio of policy to billable hours for an employee during a given period.

Enterprise Service Automation Data Mart Dimension Tables

The following table describes the delivered ESA data mart dimension tables.

Dimension Name	Dimension Record Name	Description
Project Analysis Group	D_ANLYS_GRP	Stores analysis group information. You can use analysis groups for analyzing projects and mapping analysis types.
Project Analysis Type	D_ANLYS_TYPE	Stores analysis type information. Analysis types are assigned to individual transactions to identify different types of transactions, such as estimated costs, budgeted amounts, actual costs, and billed costs.
Assignment	D_ASGN	Stores information about assignments. Assignments pair a resource with a unit of work.
Assignment Status	D_ASGN_STAT	The assignment status dimension represents information about the status of an assignment.
Billing Action	D_BLN_ACTN	The billing action dimension represents information (for example, Billable, Internal, or Nonbillable) about billing actions.
Contract Amend Reason	D_CA_AMD_RSN	Provides an explanation why an amendment was initiated. The amendment reason augments the amendment type and provides additional insight for the reason behind the amendment.
Contract Amendment Type	D_CA_AMD_TYPE	Stores information about the high-level classification of an amendment. Amendment types are dictated by internal amendment policies.
Contract Detail	D_CA_DTL	Stores contract detail information.
Contract Junk	D_CA_JNK	Stores a collection of low cardinality contract information.
Dist Account Type	D_DIST_ACCT	Stores information relating to the expense report's distribution account type.
Expense Location	D_EX_LOCATION	Stores the details of the expense location, such as location group, state, or country.
Expense Line Detail	D_EXP_LN	Stores information relating to an expense report line detail.
Expense Report	D_EXP_RPT	Stores information relating to an expense report.
Exp Approval Detail	D_EXP_RPT_APPRV	Stores information regarding the expense report approval process.
Employee Forecast	D_FCST	Stores information about employee forecasts.
Employee Forecast Approved	D_FCST_APPRV	Stores information, such as Approved, Denied, Sent Back for Revision, On Hold, Initial, Resubmitted, In Process, Rerouted, Submitted, about forecast approvals.
Award Attribute	D_GM_ATTR_CODE	Defines attributes that are associated with proposals and awards, which are used to classify them.
Grants Management Award	D_GM_AWARD	Defines a grant award. The award dimension is an extension of the contract dimension with grants award specific attributes.
Award Funding by Period	D_GM_BUD_PER	Stores the Budget Periods details for an award.
Grants Management Institution	D_GM_INST	Defines the Grants Management entity, Institution.
Award Keyword	D_GM_KEYWORD	Defines keywords that can be associated with awards so they can be categorized. The keyword provides the ability to view grant award amounts across multiple grants based on a select keyword value.
Grants Management Principal Investigator	D_GM_PI	Defines attributes that are associated with principal investigators, which are used to classify grants. It is an extension of the Employee dimension. Includes ethnicity, disabilities, fringe rates, conflict of interest, and so on.
Grants Management Project	D_GM_PRJ	Defines grant projects.
Grants Management Project Attributes	D_GM_PROP_PROJ	Defines attributes for proposed grants projects.
Grants Management Proposals	D_GM_PRP	Defines grant proposals.
Sponsor	D_GM_SPONSOR	Defines sponsors. This is an extension of the Customer dimension with grants-specific attributes.
Sponsor Type	D_SPNSR_TYPE	Defines attributes that classify sponsors.
Merchant	D_MRCHNT	Stores merchants available for selection in an expense report.
Project Activity	D_PRJ_AC	Stores information about a project's activities (tasks). This dimension rolls up to the project dimension.
Project AC Status	D_PRJ_AC_STAT	Stores information about user-defined activity statuses.
Project Change	D_PRJ_CHNG	Stores project change information.
Project Deliverable Status	D_PRJ_DELV_STAT	Stores the project deliverable status dimension.
Project Health	D_PRJ_HLTH	Stores information about project health. This information is maintained in a separate dimension to avoid forcing type-2 changes on the project dimension table or placing degenerate dimensions in the fact table.
Project Issue	D_PRJ_ISS	Contains unique combinations of issue priorities, statuses, and types.
Project Role	D_PRJ_ROLE	Stores information about project roles.
Project Resource	D_PRJ_RSRC	Stores information that classifies project financial transactions.
Project Status	D_PRJ_STAT	Stores information about user-defined project statuses.
Project Transaction	D_PRJ_TRAN_STAT	Stores combinations of resource transaction, business intelligence and GL distribution statuses in the data. Storing this data in one small dimension table reduces the number of surrogate keys in the fact table by two.
Proposal	D_PRP	Stores proposal information.
Proposal Detail	D_PRP_DTL	Stores proposal detail information.
Service Order	D_SO	Stores information about service orders (SOs). SOs are used to submit, track, and fulfill requests for services from external or internal customers.
Service Order Stat	D_SO_STAT	Stores information about service order statuses.
Task Type	D_TASK_TYPE	Stores information used to categorize tasks, such as Billable External, Billable Internal, Company Holiday, Corporate Event, Corporate Training, Education-College/Univ, Meeting, Nonbillable External, and Nonbillable Internal.
Time Reporting Code	D_TRC	Stores information about time reporting codes, such as Contract Holiday, Floating Holiday, Illness - Paid, Jury Duty, Personal - Paid, and Vacation.
User	D_USER	Stores information about the application's users.
Award Attributes (Relationship Table)	R_GM_AWD_ATTR	Links the one to many relationships between awards and custom attributes
Proposal Attributes (Relationship Table)	R_GM_PRP_ATTR	Links the one to many relationships between proposals and custom attributes
Award Keyword (Relationship Table)	R_GM_AWD_KEYWRD	Links the one to many relationships between awards and keywords.
Proposal Keyword (Relationship Table)	R_GM_PRP_KEYWRD	Links the one to many relationships between proposals and keywords.

General Ledger and Profitability Data Mart

The General Ledger and Profitability data mart helps you analyze things such as the condition of assets, liabilities, and equity of an organization, as well as the profit and loss position of an organization. This data mart also enables your organization to compare results for a period across fiscal years. Additionally, it helps you determine which customers, products, and channels are most profitable and why. With it, you are able to rank customers based on revenue, gross margin, net income, or total expenses. You can identify changes in product mix, analyze profitability trends over time, and learn how each factor impacts costs, profit, and ultimately, the value of your organization. If you have purchased the Global Consolidations application, you can access this information through the Profitability data mart.

Global Consolidations introduces consolidation by using flows that enable you to track and reconcile the gross variation of an account. Gross variation is the difference between the opening and closing balances of an account. For example, the gross variation of a fixed asset account can be distinguished by additions, disposals, deprecation, asset impairment, current translation, and reclasses. Data flow of specific accounts is required as part of regulatory reporting, such as Security and Exchange Commission, International Accounting Standards, and so on. This feature enables you to identify and document the change in account activity, meet certain regulatory requirements, and provide financial statement footnotes.

Note: The Global Consolidation subject area is primarily sourced from the Operational Warehouse Enriched (OWE) tables GC_CLED_MGT_F00 and GC_FLOW_MGT_F00, which are the final output tables of the Global Consolidation application. The Profitability subject area is sourced from the OWE table PF_LEDGER_F00.

Financial Control and Reporting Business Process

The Financial Control and Reporting business process fulfills an organization's requirements for recording, settling, consolidating, and reporting enterprise financial transactions. Using the Financial Control and Reporting business process, you capture and record financial transactions, transform financial information, allocate costs and adjust currencies, and close your books. You can use this information to report your results and to analyze your financial information, as well as to adjust your budgets, business events, and strategic plans. The Financial Control and Reporting business process helps you answer questions such as "How is my company performing against budget?", "How is my company performing against previous years?", What is my company's cash flow position?" and "How strong is my company's liquidity and solvency?"

Analyze Financial Information is the business sub-process tied to the General Ledger and Profitability data mart, which aids you in analysis of areas such as creating allocations, revaluing balances, performing eliminations, recording period end adjustments, and consolidating data.

Commitment Control Business Process

The GL and the Profitability data mart enables you to view the current status of budgets, identify transactions associated with budgets, generate internal budget reports and reports to external parties. In addition to internal reporting and analytics, you can also perform regulatory or statutory reporting.

The Commitment Control business process helps you answer questions such as "What is my budget balance with and without encumbrances?" "What portion of my budgets were still encumbered at year-end and why?" "What remaining balances were rolled from the prior year?" "Which of my budgets typically ends the year with balances that are rolled?" "What is the budget to actuals variance average across a five year period?" and "Which budgets are being supplemented by revenues and by how much?"

General Ledger and Profitability Data Mart Delivered Fact and Dimension Tables

This section discusses the delivered fact and dimension tables for the General Ledger and Profitability data mart.

General Ledger and Profitability Data Mart Fact Tables

The following table describes the delivered General Ledger and Profitability data mart fact tables.

Note: In the table, the Helps Answer column includes examples of the type of answers a fact table can provide; it does not contain the complete list of answers.

Fact Name	Fact Record Name	Description	Helps Answer
GL Aggregated Balance	F_BEGIN_BAL	Contains aggregated period balances found in the F_LEDGER table and provides details such as requested budget amount, approved budget amount, and so on, which provides financial facts for the budget preparation process. You can use this information to provide financial facts for the budget preparation process.	Balance of an account at the end of last year. Amount an account grew over the past year. Requested budget amount. Final budget amount after all adjustments have been applied.
Global Consolidations - Ledger	F_CLEDGER	This fact is an MDW version of GC_CLED_MGT_F00, which is the output of Global Consolidations process. By storing it in MDW, users can leverage the functionality available in MDW hierarchies.	Aggregate assets for all source ledgers. Actual transactional amount posted to Global Consolidations from a source. Actual amount in base currency posted to Global Consolidations from a source.
Global Consolidations - Flows	F_FLOWS	Based on output of Global Consolidations process, tracks and reconciles the gross variation of an account which can be due to additions, disposals, depreciation, asset impairment, and so on.	Breakdown of a fixed asset account by additions. The impact fluctuations in currency conversion rates has on cash flow.
General Ledger Journal	F_JOURNAL	Stores all journal entries that are posted in GL and it provides an audit trail for activity affecting the ledger. Use this fact to determine journal entries that drove the change in ledger balance, for example.	Journal entries that drove the change for a particular ledger balance.
Commitment Control Activity Log	F_KK_ACTIVITY	Provides detailed transaction activity recorded for each budget ledger.	View transaction details for one or more budgets. View the original transaction keys in the source systems. Reconcile Audit Log activity to budgets.
Commitment Control Activity Fund Source Log	F_KK_ACT_FS	Captures detailed transaction activity recorded for a specific fund source.	Number of allocated funding sources spent or unspent. Expenditures from a funding source across the organization and across multiple years with a breakdown by expenditure or revenue class. Sum of allocated, unallocated, and available funds, and a breakdown of how the funds were spent.
Commitment Control Ledger Balances	F_KK_BALANCES	Contains details of Commitment Control ledger balances.	Budget that typically ends the year with rolled balances. Budget balance with and without encumbrances. Budget still encumbered at year-end.
Commitment Control Budget Association	F_KK_BUD_ASSOC	Captures information on revenue allocations, such as the method of allocation and revenue percent.	Budgets supplemented by revenues and by amount. Number of budget accounts supplemented by a specific revenue budget.
Commitment Control Encumbrance and Liquidation	F_KK_ENCUMBRAN	Contains details on open encumbrances and relieving documents.	Identify vouchers that relieved encumbrances on a purchase order or budget account. Identify any discrepancies or remaining reserved amounts that must be fully relieved.
Commitment Control Exception Log	F_KK_EXCEPTION	Contains budget checking errors activity. Errors are entered into this table each time a transaction line that is budget checked results in an error or warning	Number of budget checking exceptions for a particular budget account. Number of budget check failures in the last week. Top reasons for budget check failure.
Commitment Control Fund Source Allocation	F_KK_FS_ALLOC	Contains the monetary amounts of allocation as well as expenditure/revenue amounts recorded against the fund source.	Allocated fund sources for capital improvement projects. Allocation methods, revenue percent, and caps. Balance remaining for a fund source. Number of unallocated funding sources.
Commitment Control Fund Source Received	F_KK_FS_RCVD	Captures the receipt of funds for a given fund source.	Funds spent for a specific period at the request of a foundation or grant sponsor.
Budget Journal Fact Table	F_KK_JOURNAL	Contains detailed information on budget journals. It enables you to: Review budget adjustments for a business event, such as allocation of budgets for a special program or project. Analyze the rate of adjustments made to original budgets by fiscal year and/or budget period. Analyze budget transfer activity for a group of programs or projects over a multi-fiscal year period.	Journal entries that drove the change for a particular ledger balance.
Commitment Control Ledger Detail	F_KK_LEDGER	Contains detailed information about Commitment Control ledgers.	Budgets that are consistently overspent. Areas where revenues are not keeping up with spending. Spending pattern for my budgets. Revenue trend from utility fees over the last five years. Number of budgets that are strictly controlled.
Commitment Control Override Log	F_KK_OVERRIDES	Contains information on budget overrides.	Individual responsible for authorizing the most number of exception overrides. Number of overrides for particular budget account.
Commitment Control Audit Log	F_KK_TRANS_LOG	Contains all events related to a transaction including changes made to distributions. If you are doing summary level budgeting you can use this data as a link between Commitment Control budget activity and General Ledger.	Transactions expensed against a budget account. Percent of expenses incurred through procurement card transactions. Purchase order types remaining open at year-end.
General Ledger Period Balances	F_LEDGER	Contains posted journals aggregated to the period level with a three-month rolling average value and helps in reporting on beginning and ending balances of accounts. It also provides the ability to compare results for a period across fiscal years.	Revenue recognized for a specific period. Balance at the beginning of an accounting period. Inventory value at the end of a period. Amount the balance grew during a given accounting period. Average amount for an account over the past six months.
Profitability Analysis	F_PROFITABILITY	Contains ledger level measures sourced from the PF_LEDGER_F00 table, which is populated by PeopleSoft Allocation Manager and Activity Based Management processes. This table helps you analyze profitability using dimensions such as Customer, Product, Channel, and so on.	Activity level for specific business activities, business units or entities. Relative activity for specific business activities, business units or entities across time, within or across fiscal years. Spending trend for specific expense line items across specific organizational units. Revenue trend for specific products or services within a region or across organizational units. Balance growth during the accounting period.

General Ledger and Profitability Data Mart Dimension Tables

The following table describes the delivered General Ledger and Profitability data mart dimension tables.

Dimension Name	Dimension Record Name	Description
Activity Based Manager Data	D_ABM_OBJECT	Stores activity based manager data used in profitability analysis.
Alternate Account	D_ALTACCT	Stores alternates to original accounts. Corporations use these mainly for accounting purposes.
Billing Status	D_BI_STATUS	Stores billing status. Possible values are Cancelled, New Bill, Hold Bill, and so on.
Billing Type	D_BI_TYPE	Stores billing types. Possible values are Asset Bills, Service Bills, and so on.
Commitment Control Ledger Group	D_BU_LED_GRP	Identifies the ledger groups associated with a business unit.
Expansion 1	D_DIMENSION1	This is an user defined expansion dimension.
Expansion 2	D_DIMENSION2	This is an user defined expansion dimension.
Expansion 3	D_DIMENSION3	This is an user defined expansion dimension.
Consol Ledger Source	D_GC_CLED_SRC	Identifies the source of Global Consolidations entry as Equalization, Elimination, and so on.
Flow Codes	D_GC_FLOW_CD	Describes the system and manual flow codes used to indicate the Global Consolidations flow process.
Flow Rates	D_GC_FLOW_RATE	Stores the rate at which the flow transaction was made, either Cash, Closing, or Both.
Flow Sources	D_GC_FLOW_SRC	Stores the source that generated the flow transaction, either Batch, Journal, or Source Input.
Global Consolidations Company Location	D_GC_LOCATION	Stores details about the location of the business unit associated with Global Consolidations.
Commitment Control Budget Attributes	D_KK_BUDG_JNK	Identifies all of the relevant translate values for Commitment Control attributes.
Commitment Control Budget Ledger Definition	D_KK_BUDG_TYPE	Identifies the attributes of a Commitment Control budget ledger group.
Commitment Control Fund Source	D_KK_FUND_SRCE	Identifies details about the fund source and includes information such as fund source type, reimbursement agreement, agency, and total fund source amount.
Commitment Control Source Transaction Definition	D_KK_SRC_TRAN	Identifies the type of transaction that was processed by Commitment Control, such as Purchase Order, Payables Voucher, and GL Journal.
Commitment Control Transaction Type	D_KK_TRANS_TYPE	Identifies the type of budgetary amount as original or adjusted or transfer of either type.
Line of Business	D_LINE_OF_BUS	Stores details about line of business of the business unit associated with Global Consolidations.
Scenario and Calendar	D_PFBU_SCEN_DFN	Sourced from PF_BU_SCEN_DFN and is created in the MDW for metadata purposes only.
Commitment Control Project Resource Type	D_PRJ_RSRC_TYPE	Identifies resource types owned by a project.
Subsidiary	D_SUBSIDIARY	Stores information on subsidiary.

Payables Data Mart

You use the Payables data mart to report and analyze the performance of your suppliers and your payables department. The Payables data mart includes information about suppliers, vouchers, match exceptions, and payments. This data mart helps you to assess your supplier relationships by answering questions such as "Which suppliers have billing errors?" and "How long does it take us to pay suppliers?" With this information, you can ask suppliers for better service or negotiate with them for better terms. To measure performance of your payables department, this mart helps answer questions such as "What are my accounts payable aging balances?", "Does my organization take advantage of supplier discounts?" and "How many vouchers are entered per day?" You can analyze results across many dimensions, enabling you to compare performance of multiple payables departments or even individual employees. After you understand current performance levels, you can set targets and monitor results.

The Payables data mart includes data needed to calculate standard measures such as Days Payable Outstanding, Accounts Payable Turnover, and Sales to Accounts Payable Ratio. With this information, you can measure your organization against external benchmarks. You can baseline your own results and then work towards improving payables performance over time, as specified by your overall corporate strategy.

Procurement Business Process

The Payables data mart is tied to PeopleSoft's Procurement business process, which is also known as the Source to Settle business process. The Procurement business process fulfills an organization's requirements for sourcing, engaging, procuring, and settling payment for goods, services, or both. The Procurement business process enables you to determine profitability sourcing strategies, collaborate with suppliers, and drive efficient procurement and settlement for all goods and services.

Pay Supplier is the business sub-process related to Payables data mart, which aides you in areas such as creating and matching vouchers, resolving disputes with suppliers, and creating payment.

Payables Data Mart Delivered Fact and Dimension Tables

This section discusses the delivered fact and dimension tables for the Payables data mart.

Payables Data Mart Fact Tables

The following table describes the delivered Payables data mart fact tables.

Note: In the table, the Helps Answer column includes examples of the type of answers a fact table can provide; it does not contain the complete list of answers.

Fact Name	Fact Record Name	Description	Helps Answer
AP Account Line Entries	F_AP_ACCOUNT_LN	Stores all of the AP accounting entries posted by the AP module.	Posting details of the AP transactions to GL for a specified time period. Journal amount posted to the GL for a selected AP account by AP transactions.
AP Aging Process	F_AP_AGING	Contains the aging information by aging category and supplier and can be used in analysis.	Money owed for a specific period. Highest balance for a supplier. Highest past due for a supplier. Aging trends of accounts payable. Total discount amount available in AP Vouchers. Total prepaid amount to a specified supplier. Total AP outstanding amount to be paid to a specified supplier.
AP Transactions	F_AP_TRAN	Contains the summary of AP transactional information at a header level and can be used to analyze vouchers and payments made by a specific supplier. Various amounts such as Paid, Open, Discount, Late Charge, and so on are available.	Total number of AP vouchers and AP payments. Total AP voucher amount and total AP payment amount by supplier. Current status of the AP transactions. Voucher, adjustment and payment history for a supplier. Discount eligible to be deducted if AP vouchers are to be paid as per due date. Total late charge amount to a particular supplier over time. Late charge denied amount to a particular supplier.
AP Voucher Match Exceptions	F_VCHR_MTCH_EXP	Contains information about all failed transactions of the AP matching process and can be used to report the number of exceptions generated by the process, by supplier and AP operator ID.	Number of exceptions generated by the AP voucher matching process, by supplier and AP operator ID.

Payables Data Mart Dimension Tables

The following table describes the delivered Payables data mart dimension tables.

Dimension Name	Dimension Record Name	Description
Aging Category	D_AGING_CTGRY	Stores aging categories used in AP and AR aging analysis. The AP_AR_IND field classifies the aging category as AP or AR.
AP Payment Transaction Status	D_AP_PTR_STAT	Stores accounts payable (AP) transaction status from a payment perspective. Possible values are Paid, Stopped, Void, and so on.
AP Payment Transaction Type	D_AP_PTR_TYPE	Stores AP transaction types from a payment perspective. Possible values are Express Payment, Manual Payment, Regular Payment, and so on.
AP Voucher Transaction Status	D_AP_VTR_STAT	Stores the AP voucher transaction status. Possible values are Approved, Denied, Pending, and so on.
AP Voucher Transaction Type	D_AP_VTR_TYPE	Stores AP voucher transaction types. Possible values are Adjustment Voucher, Prepaid Voucher, and do on.
AP Post Status	D_APPOST_STATUS	Stores AP posting status. Possible values are Posted, Unposted, Payment Not Applied, and so on.
AP Operator	D_PERSON_APOPID	Stores details such as name and contact details about the AP operator who handles the payments in the AP module.

Receivables Data Mart

Accounts receivable are often the largest asset on a company's balance sheet and are critical to its financial health. While extending credit to the customers helps boost sales, it also increases the cost of working capital and financial risk, especially in challenging economic times.

You use the Receivables data mart to analyze your company's receivables. With it you can perform multidimensional analysis against your receivables activities and receivables information to see how much is due from customers, view accounts receivable transactional activities, analyze customer credit worthiness and risk, and conduct account receivable account level analysis.

The Receivables data mart helps you identify areas within your organization contributing to higher days sales outstanding, increased credit risk, poor collection performance, or more bad debt write-offs. In essence, it helps you better understand your receivables asset and identify areas for improvement within your organization that will enable you to improve the quality of your accounts receivable and reduce the cost of working capital.

Order Fulfillment Business Process

The Receivables data mart is related to PeopleSoft's Order Fulfillment business process, which is also known as Order to Cash. The Order Fulfillment business process fulfills an organization's requirements for capturing, fulfilling, and settling goods sold. With the Order Fulfillment business process, you capture, confirm, and manage sales orders and contracts, deliver goods or services, and then invoice, collect, and resolve payment. The Order Fulfillment business process also helps you to manage returns and inventory, process customer payments, and maintain profitable customer relationships. The information captured within the Order Fulfillment business process helps you determine answers to questions such as "What are my cost per sales?", "What is my ending receivables balance?" and "What is my bad debt compared to sales?"

Process Customer Payments is the business sub-process related to the Receivables data mart, where you receive and apply customer payments, maintain customer balances, manage past due accounts, and resolve payment discrepancies.

Receivables Data Mart Delivered Fact and Dimension Tables

This section discusses the delivered fact and dimension tables for the Receivables data mart.

Receivables Data Mart Fact Tables

The following table describes the delivered Receivables data mart fact tables.

Note: In the table, the Helps Answer column includes examples of the type of answers a fact table can provide; it does not contain the complete list of answers.

Fact Name	Fact Record Name	Description	Helps Answer
AP-AR Netting	F_APAR_NETTING	Contains information about the AR amount outstanding from a customer and the AP amount outstanding from the company to that customer (if that customer is also a supplier to the company). This provides a net total for the customer or supplier.	Net balance for a customer or supplier. Highest balance for a customer or supplier. Highest past due for a customer or supplier. Outstanding balance for a customer, with respect to the customer's credit limit. AR-AP Netting Balance Amount for customers who are also suppliers. Aging trends of accounts payable.
AR Account Line Entries	F_AR_ACCOUNT_LN	Contains all AR accounting entries and can be used to answer questions related to the journal amount posted to GL for the selected AR accounts by AR transactions.	Journal amount posted to the general ledger for the selected AR account by AR Transactions.
AR Aging process	F_AR_AGING	Contains the aging information by aging category and customer.	Money owed for a specific period, and percentage past due. Highest past due amount for a customer. Aging trends of my accounts receivable.
AR Credit Limit	F_CREDIT_LIMIT	Contains information about credit allocated to a customer based on the customer's credit rating.	Available credit and credit limit by customer. Customer credit limit percentage over limit and how much has the customer actually exceeded this credit limit range. Outstanding AR amount and by what percentage the AR outstanding above or below the credit limit.
AR Days Sales Outstanding	F_AR_DSO	Contains Days Sales Outstanding (DSO) information for accounting periods and helps to analyze the average time taken to turn the receivables into cash.	Days sales outstanding, by customer.
AR Transactions	F_AR_TRAN	Stores summary of AR transactions and helps in analysis of total AR invoices and payments, AR invoice amount, AR receivables amount by a customer, and so on. This table helps you determine factors such as the AR Transactions Performance metrics, for example, transaction amounts, discount amounts, and so on.	Total AR invoice amount and total AR payment amount, by customer. Current status of AR transactions. Invoice, adjustment and payment history for a customer. Earned discount amount for a specified customer in a given time period. Dispute amount for a specified customer in a given time period. Transaction activity for a customer for a specific period of time.

Receivables Data Mart Dimension Tables

The following table describes the delivered Receivables data mart dimension tables.

Dimension Name	Dimension Record Name	Description
AR Document Type	D_AR_DOC_TYPE	Stores details about various AR document types such as Receivables Adjustments, Receivables Revaluation, and so on.
AR Item Transaction Status	D_AR_ITR_STAT	Indicates the transaction status of an item as Closed or Open.
AR Item Transaction Type	D_AR_ITR_TYPE	Stores AR Item transaction types. There is no source for this table, a single row is created with hardcoded values.
AR Payment Transaction Status	D_AR_PTR_STAT	Stores AR payment status. Possible values are Applied, Complete, Unidentified, and so on.
AR Payment Transaction Type	D_AR_PTR_TYPE	Stores AR Payment transaction types. There is no source for this table, a single row is created with hardcoded values.
Collection Status	D_COLLECT_STAT	Stores collection status codes and descriptions.
Deduction Status	D_DEDUCT_STAT	Stores AR deduction status information.
Dispute Status	D_DISPUTE_STAT	Stores details about disputes raised in AR collections.
Entry Reason Type	D_ENTRY_RSTYP	Stores reasons used when an entry in GL is created from AR. Possible values are Adjustment Overpayment, Credit Memo, and so on.

Shared Dimensions

Certain dimensions, such as Account or Department are used across all EPM warehouses. These dimensions are identical in structure and content across all EPM warehouses. The following table describes the delivered shared dimension tables.

Dimension Name	Dimension Record Name	Description
Account	D_ACCOUNT	Stores details of an account that represents a ChartField.
AP Document Type	D_AP_DOC_TYPE	Stores details about AP document types, such as Payables Payments, Payables Adjustments, Payables Accruals, and so on.
Association Type	D_ASSOC_TYPE	Defines the association type for Case, Interaction and Order association.
Bank Account	D_BANK_ACCT	Store details about banks and bank accounts.
Book Code	D_BOOK_CODE	Stores details about book codes, which represent an account attribute and a balancing ChartField.
Budget Reference	D_BUDGET_REF	Stores budget descriptions.
Buyer	D_BUYER	Stores information on buyers, including information related to a buyer's employee ID and address.
Contract	D_CA	Stores the details of the contract information entered with customers. A contract contains the agreement information and obligations for the products and services licensed in the contract and is grouped by contract type.
Carrier	D_CARRIER	Stores information on carriers.
Certification Source	D_CERTSRC	Stores information on certification sources for suppliers.
Channel	D_CHANNEL	Stores channel information related to sales and procurement.
Chartfield1	D_CHARTFIELD1	Stores user defined ChartField details.
Chartfield2	D_CHARTFIELD	Stores user defined ChartField details.
Chartfield3	D_CHARTFIELD3	Stores user defined ChartField details.
Channel Partners	D_CHNL_PARTNER	Stores information about channel partners involved in the sales process.
Expenses Classifications	D_CLASS_FIELD	Stores expenses classification codes and descriptions, such as wages, benefits, health, and office supplies.
Company	D_CMPNY	Stores company-related information.
Credit Risk	D_CREDIT_RISK	Classifies credit risk values as High, Low, and Medium.
Customer Contact Person	D_CUST_CNTCT	Stores information about the customer contact person, which includes contacts and partners.
Customer Organization	D_CUST_ORG	Stores information related to customer organizations (companies). A customer organization is a company that purchases, leases, or contracts for products or services. The customer organization (company) is a subset of the Customer dimension.
Customer Person	D_CUST_PERSON	Stores information about individuals that purchase, lease, and contract for products or services. The Customer Person is a subset of the Customer dimension.
Customer Site	D_CUST_SITE	Stores information about organizations that purchase, lease, and contract for product or services located at a particular site or location. Sites can be an organization site or an individual site. Site is also a subset of the Customer dimension.
Customer Master	D_CUSTOMER	Stores information for entities that can participate in business relationships.
Department	D_DEPT	Stores information about the entities in an organization. This dimension includes attributes about a department, such as description, company code, location, and budget fields.
Employee Job Code	D_EMPL_JOB	Stores employee job history data, such as actions taken, department, job code, location, and salary history. Multiple records can be created for an employee.
Establishment	D_ESTAB	Stores distinct physical places of business (establishments) within a company and its address, and is used for regulatory reporting purposes.
Frequency	D_FREQ	Stores the payment and hours reporting frequency for time and payroll data. You can use a frequency to indicate how many times per year an event occurs.
Fund	D_FUND	Stores details about fund codes and their description.
GL Adjustment types	D_GL_ADJ_TYPE	Stores types of general ledger (GL) adjustments.
GL Offset	D_GL_OFFSET	Stores information on GL offset. This dimension groups billing information, such as office rent and retail rent.
Industry Group	D_INDUSTRY_GRP	Stores customer industry group information.
Inventory Item	D_INV_ITEM	Stores information about Inventory Item, which includes all attributes of item, including simple hierarchy information, such as category or group, as well as Make or Buy flag.
Inventory Location	D_INV_LOCATION	Stores information about the storage location from which goods will be moved.
Jobcode	D_JOBCODE	Stores information about the job assignments in an organization. This dimension represents the categorization of jobs into types, such as executive, technical, and administrative services.
Journal Line Source	D_JRNL_SOURCE	Stores the details about source of journal entries created in GL.
Sales Lead	D_LEAD	Stores sales leads generated by marketing campaign waves.
Ledger	D_LEDGER	Stores the ID and description of ledgers that are defined based on templates.
Line Type	D_LN_TYP	Stores information on line types.
Location	D_LOCATION	Stores a list of work sites for an organization. Location is used to establish physical locations in an organization, such as corporate headquarters, branch offices, and remote sales offices.
Lot	D_LOT	Stores information on lot (a group of items with similar characteristics).
Operating Unit	D_OPER_UNIT	Stores details about operating units, such as a plant, office, physical location, branch, and building.
Sales Opportunity	D_OPPORTUNITY	Stores information about a sales opportunity.
Order Capture	D_ORD_CAPTURE	Stores order capture information for the sales order process.
Sales Order Status	D_ORD_STAT	Stores information on order status.
Partner	D_PARTNER	Stores partner information. The dimension has the following hierarchy: Partner, Partner Status.
Pay Group	D_PAYGRP	Groups employees by how they are paid.
Person	D_PERSON	Stores the most current personal information of both employees and non-employees of an organization.
AR Specialist	D_PERSON_ARSPL	Stores details, such name and contact, about the accounts receivable (AR) specialist involved in handling the disputes and deductions in the AR module.
AR Collector	D_PERSON_COLTR	Stores details, such name and contact, about the AR collector involved in collecting the receivables amount in the AR module.
AR Credit Analyst	D_PERSON_CRNYST	Stores details, such name and details, about the AR credit analyst involved in handling the credits given to customers.
AR Deduction Manager	D_PERSON_DEDMGR	Stores AR deduction manager name and contact information.
Position	D_POS	Stores information on all job positions available, whether an employee fills the position or no, and helps with data analysis based on salary or standard hours.
Product Group	D_PROD_GROUP	Stores information on product groups.
Product	D_PRODUCT	Stores information on products.
Program	D_PROGRAM_FDM	Keeps track of programs, such as public works, social services, fire, and public safety, that are tracked in General Ledger.
Project	D_PROJECT	Stores information about projects. A project is a vehicle for identifying an initiative that has a specified start and end date.
Partner Contact	D_PRTR_CNTCT	Stores partner contact data.
Payment Method	D_PYMNT_MTHD	Stores methods of payment, such as check, cash, and credit card.
Receive Line Status	D_RECLN_STATUS	Stores information on all receive line statuses.
Regulatory Region	D_REG_RGN	Stores the codes for regulatory and regional edit purposes. A regulatory region is any region where there are specific laws and regulations that are used for transactional purposes.
Geographic Region	D_REGION	Contains geography information for customers.
Salary Plan	D_SALPLN	Stores unique salary categories that are defined in an organization. These categories are set up according to an employee's compensation structure.
Scenario	D_SCENARIO	Stores details of historical, budgeting, and forecast scenarios.
Customer Segment	D_SEGMENT	Stores customer segment information.
Statistics Code	D_STAT_CODE	Stores details about statistical information, such as floor space, full-time equivalent workdays, and shipment size.
Subledger	D_SUBLEDGER	Stores information on subledger, which groups the accounting information.
Supplier	D_SUPPLIER	Stores information on suppliers, such as remit to supplier and corporate supplier.
Sales Territory	D_TERRITORY	Stores sales territory information. Sales territories are user defined sales regions independent of geography or proximity.
Unit	D_UNIT	Stores detail information on real estate properties.
Unit of Measure	D_UOM	Indicates the quantity in which an inventory item is expressed, such as case (CS) or box (BX).