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