Understanding the CRM Warehouse Structure

Data marts are logical divisions within the CRM Warehouse and are comprised of subject-specific dimensional data models designed around a specific institutional process. The CRM Warehouse includes the Marketing Data Mart, Sales Data Mart, Service Data Mart, and Customer Segment Data Mart.

Image: CRM Warehouse data marts and business processes

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.

crm_warehouse_data_mart_and_business_process

This section discusses:

Marketing data mart
Service data mart
Sales data mart
Customer Segment data mart

Marketing Data Mart

Marketing data mart enables you to evaluate the effectiveness of marketing activities. Use it to analyze the number of leads generated, the number of click-throughs, respondent profiles, lead qualification mechanisms, and program return on investment. The Marketing data mart helps you to understand which activities work and why, identify whom you are attracting, and how to improve the next program.

Marketing data mart includes the ability to analyze the effectiveness of online marketing efforts, in addition to traditional marketing activities. The Marketing data mart delivers comprehensive analysis of Marketing activities from the target of prospects through the close of sales. The Marketing data mart enables you to evaluate the effectiveness of all marketing activities for your target audiences across multiple marketing channels. With the Marketing data mart, you can tie back to sales activities and orders to the marketing program that originated the opportunities.

Marketing Business Process

The Marketing data mart is related to PeopleSoft's Marketing business process, which is also known as Customer Lifecycle Marketing business process. This is the process followed when developing marketing programs and activities. Marketers begin the cycle by analyzing historical and predictive data to determine their marketing strategy. Marketers can then plan marketing programs and related activities by allocating budgets, assigning teams and tasks, and scheduling the timing. Next, you execute these plans by designing and taking the programs live across multiple channels, including direct, online, and telemarketing, and converting prospects into leads and sales. Finally, closed loop reporting enables marketers to assess program performance both in real-time to make any required changes to maximize revenue opportunities, as well as post-program performance to measure its overall success.

Multiple business sub-processes are related to the Marketing data mart:

Marketing data mart reports help marketers with the Marketing Resource Management business sub-process to better manage marketing operations, such tracking budgets and expenses. In addition, all costs associated with a roll-up program are consolidated for a single view of how much the roll-up program cost, broken out by individual activities.
Marketing data mart reports provide insight into Cross-Sell Opportunity Identification business sub-process; the data can help you determine which products tend to be correlated so you can determine potential cross-sell opportunities.
You can also measure Program Performance business sub-process by examining the reach and response of your programs and activities across all channels: direct, telemarketing, and online. This can help you to determine channel effectiveness and identify successful programs that can be cloned and re-executed in the future.

Marketing Data Mart Delivered Fact and Dimension Tables

This section discusses the delivered fact and dimension tables for the Marketing Data Mart.

Marketing 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. The following table describes the delivered Marketing Data Mart fact tables.

Fact Name	Fact Record Name	Description	Helps Answer
Marketing Activity	F_MKT_ACTIVITY	Maintains cost and time metrics for measuring the effectiveness and efficiency of marketing campaigns, programs, and activities (events). The fact data is a periodic snapshot captured at the marketing activity level.	The return on investment of a marketing activity. The amount of money budgeted for a marketing activity. Total cost of a marketing program. Revenue generated from a marketing program. Estimated revenue to be generated by a marketing program. Number of leads generated by a marketing activity. Number of customers generated by a marketing activity.
Online Marketing (OLM) All Response	F_OC_ALLRSP	Maintains aggregate email and web page response per action per dialog. The All Response fact table is an aggregated fact table.	Number of responses received from emails and web pages. Number of audiences in the online marketing list. Number of audiences contacted multiple times. Number of people who responded to an email.
OLM Bulk Email	F_OC_BMAIL	Maintains bulk email information. This base fact is the source to populate OLM aggregate facts.	—
OLM Daily Email	F_OC_EMLDAY	Maintains aggregate email response metrics per day, per action, per dialog. The Daily Email fact is an aggregated fact table.	Number of daily emails sent that generated a response. Number of emails sent that were not duplicates or 'Do Not Email' contacts. Number of emails sent that bounced back.
OLM Document Answer	F_OC_DOCANS	Maintains aggregate answer choice metrics for each web page document, per action, per dialog.	Number of responses to a documented question. Answer responses for a documented question that appeared online. Number of people responding to an online question.
OLM Document Choice	F_OC_DOCCHO	Maintains document choice information. This base fact is the source to populate the OLM aggregate fact F_OC_DOCANS.	—
OLM Dynamic Content	F_OC_DYNCNT	Maintains aggregate dynamic content information. This is a subset of the F_OC_EMLPFM fact.	—
OLM Dynamic Content Web Link	F_OC_DYNLNK	Maintains aggregate dynamic content web link information. This is a subset of the F_OC_EMLPFM fact.	—
OLM Dynamic Content Section	F_OC_DYNSEC	Maintains aggregate dynamic content section information. This is a subset of the F_OC_EMLPFM fact.	—
OLM Email Bounce	F_OC_EMLBNC	Maintains bounced email information. This base fact is the source to populate OLM aggregate facts.	—
OLM Email Impression	F_OC_EMLIMP	Maintains email impression information. This base fact is the source to populate OLM aggregate facts.	—
OLM Email Performance	F_OC_EMLPFM	Maintains aggregate email dynamic content appearance metrics per action, per dialog. This fact table is an aggregated fact table.	Number of times a document link was offered. Number of contacts who responded on a particular web page link. Number of times documents were sent to known audiences. Number of times documents were sent to unidentified audiences. Number of times document links were clicked by an unknown respondent.
OLM Email Repeat Response	F_OC_EMLREP	Maintains aggregate repeat email response metrics per action, per dialog. The Repeat Email fact is an aggregated fact table.	Number of repeating emails sent that generated responses. Number of audiences in a specific online marketing list. Number of audiences contacted multiple times. Number of emails offered that were not duplicates or 'Do Not Email'. Number of emails bounced back. Number of people responding to an email.
OLM Email Response	F_OC_EMLRSP	Maintains aggregate email response metrics per action (marketing activity), per dialog (marketing program). The Email Response fact is an aggregated fact table.	Number of emails sent for each dialog action. Number of audiences in a specific online marketing list. Number of audiences contacted multiple times. Number of times a specified link was clicked. Number of people responding to an email.
OLM Profile Answer	F_OC_PROANS	Maintains profile choice answer information. This base fact is the source to populate the OLM aggregate fact F_OC_PROCHO.	—
OLM Profile Answer Choice	F_OC_PROCHO	Maintains aggregate online marketing profile choice metrics for each web page profile question, per action, per dialog.	Number of contacts who responded to a specific web page profile question. Number of visitors for a web page. Number of submitters for a web page. Number of audiences contacted through email. Number of clicks for a specified link by a respondent. Number of responses for a web profile choice.
OLM Single Email	F_OC_SMAIL	Maintains single email information. This base fact is the source to populate OLM aggregate facts.	—
OLM Web Click	F_OC_WEBCLK	Maintains web click information. This base fact is the source to populate OLM aggregate facts.	—
OLM Daily Web Response	F_OC_WEBDAY	Maintains aggregate web page responses per day, per action, per dialog. This fact table is an aggregated fact table.	Number of daily web page submits and visits. Number of identified web page visits. Number of identified web page submits.
OLM Web Individual Response	F_OC_WEBIND	Maintains web individual response information. This base fact is the source to populate OLM aggregate facts.	—
OLM Web Repeat	F_OC_WEBREP	Maintains aggregate repeat web page response metrics per action, per dialog. This fact table is an aggregated fact table.	—
OLM Web Response	F_OC_WEBRSP	Maintains aggregate web page response metrics per action, per dialog. This fact table is an aggregated fact table.	Responses for an online marketing web page. Number of visits to the web page can be identified. Number of identified web page visits. Number of unidentified web page visits. Number of identified web page submits. Number of web page submitters.
OLM Web Submit	F_OC_WEBSUB	Maintains web submit information. This base fact is the source to populate OLM aggregate facts.	—
OLM Web Visit	F_OC_WEBVST	Maintains web visit information. This base fact is the source to populate OLM aggregate facts.	—
Product Correlation	F_PROD_CORR	Maintains product and correlated product relationships within sales orders (market basket metrics) and provides insight into cross-selling opportunities. The fact data is derived from the sales order capture transaction at each product combination level.	Based on history, the likelihood that a customer who purchases one product will purchase another specific product. Products typically sold along with a specific product.

The following table describes the delivered Marketing Data Mart dimension tables:

Marketing Data Mart Dimension Tables

Dimension Name	Dimension Record Name	Description
Marketing Audience	D_AUDIENCE	Maintains marketing audience information.
Marketing Status	D_MKT_STATUS	Stores campaign life cycle stage information. Campaign and activity statuses are identical objects, although they are used separately. The PeopleSoft CRM application delivers a set of predefined marketing statuses; customers may also define additional values.
OLM Marketing Action	D_OC_ACTION	Maintains online marketing action and action type information. Online marketing actions include actions such as sending broadcast emails, displaying a web page, sending follow-up emails, and so on. Actions roll up to Action Types. A dialog consists of one or more actions.
OLM Basic Individual	D_OC_BASIND	Maintains individual information. The data is basic CDM data for individuals from the E-CRM BORM source. Some of the attributes are associated with the CRM Customer Person dimension.
OLM Basic Organization	D_OC_BASORG	Maintains organization information. The data is basic CDM Data for organizations from the E-CRM BORM source. Some of the attributes are associated with the CRM Customer Organization dimension.
OLM Dialog	D_OC_DIALOG	Defines a specific marketing program for the execution of email or web channels. A dialog consists of one or more marketing actions.
OLM Doc Choice	D_OC_DOCCHO	Maintains document choice, element, document and element type information. The dimension has the following hierarchies: Document Choices, Document Elements, Document; Document Choices, Document Elements, Element Type.
OLM Document	D_OC_DOCMNT	Maintains email messages and web pages that are created for dialogs.
OLM Audience List	D_OC_LIST	Maintains audience list and list type information. Lists of audiences are used in all dialogs to specify for whom the dialog is targeted. The audience list is sourced from the CRM Customer record. Audience Lists roll up to List Types.
OLM Profile Choice	D_OC_PROCHO	Maintains profile choice, profile element, profile, and profile type information about the target audience. The dimension has the following hierarchy: Profile Choice, Profile Element, Profile, Profile Type.
OLM Repeat Action	D_OC_REPACT	Maintains online marketing repeat action information. Online marketing actions include actions such as sending broadcast emails, displaying a web page, sending follow-up emails, and so on. This dimension also contains the number of times that one action has been repeated.
OLM VC Format	D_OC_VCFMT	Maintains information about the format of a web link or email, such as HTML or TEXT.
OLM VC Object	D_OC_VCOBJ	Maintains virtual content, virtual content type, and virtual content link type information displayed in a web page or in an email. The dimension has the following hierarchies: VC Object, VC Link Type; VC Object, VC Type.
OLM VC Parameter	D_OC_VCPARM	Maintains parameter and parameter set information. Parameter sets are groups of content sections (for example, VCType main set); parameters are the content sections embedded in Dynamic Contents (for example, Biking, Surfing, Kayaking, and Skiing).

Sales Data Mart

The Sales data mart helps you analyze sales processes and evaluate the effectiveness of sales-related activities. Use it to examine pipeline status, forecasting accuracy, discount analysis, sales cycle time, and fallout analysis. Using historical measures of your sales processes helps you to provide opportunities for reducing cycle times within your sales activities.

Sales Business Process

The Sales data mart is related to the Sales business process, which is also called Collaborative Multichannel Selling business process. The Sales business fulfills an organization's requirements for discovering, managing, negotiating, and closing opportunities. This business process includes assigning leads and qualifying opportunities; proposing solutions and providing quotes; negotiating and closing the sales opportunities resulting in contracts, orders, or both; and managing pipelines, forecasts, and territories.

Sales managers monitor key performance metrics to gauge the overall effectiveness and efficiency of the sales force and to allocate resources across the territory structure to ensure adequate distribution of leads, opportunities and account coverage. Quote and proposal data provides additional insight into the details of future revenue streams and is used by sales managers to manage opportunity progression towards closure. Sales managers are responsible for monitoring individual and team performance and manage pipeline coverage and forecast data against quota levels at the individual sales representative, product, and summary levels.

There are two business sub-process related to the Sales data mart: Qualify to Close and Order to Cash. The Sales data mart addresses metrics related to both business sub-processes. The primary business sub-process that is most relevant to the sales manager role is Qualify to Close, which deals with the distribution of qualified sales leads, manages the conversion of leads into viable opportunities, and provides coaching and mentoring to the sales resources to maximize revenue generation for the organization.

A secondary business sub-process relevant for this role is Order to Cash, which is more directly related to the order capture and order management process. Sales managers that also manage this aspect of the overall sales process must monitor the ratio of opportunities to sales orders and the fulfillment of those orders through to collection and recognition of revenue. Order data provides additional information on source, product mix, discounting, and profitability metrics that provide insight into the overall effectiveness of the sales force. Sales managers use this information to adjust their strategy to maximize profitability and ensure that territories, and the resources that are allocated to those territories, are aligned properly.

Sales Data Mart Delivered Fact and Dimension Tables

This section discusses the delivered fact and dimension tables for the Sales Data Mart.

Sales Data Mart Fact Tables

Fact Name	Fact Record Name	Description	Helps Answer
Sales Lead	F_LEAD	Maintains accumulated snapshot sales lead duration and lead effectiveness data. It contains metrics at the Lead ID level. These metrics are used for lead qualification and lead generation analysis and can be rolled up by dimensions such as Customer, Program, Sales Territory, and Marketing Channel. This table is an accumulated snapshot; each time a change is made to a lead, the existing row describing that lead is changed - but the Lead Date remains the same.	Historical lead conversion rate. Number of opportunities generated from a lead. Number of leads generated. Number of customers generated from a sales lead. Number of orders generated from a lead, Average number of quotes from a sales lead.
Sales Opportunity	F_OPPORTUNITY	Contains snapshot information for expected, forecasted, and closed revenue for each sales opportunity from open to close and provides pipeline analysis for measuring the effectiveness and efficiency of the sales force. This fact data maintains the basis of measurement on the sales opportunity pipeline and fallout analysis. The fact data is a snapshot captured at the Opportunity and Sales Rep grain level.	Revenue forecasted for an opportunity. Order amount for an opportunity. Number of days estimated for an opportunity to generate an order. Total budget amount for all of the opportunities generated in a specific quarter. Revenue allocation percentage for a sales person. Qualification goal of an opportunity based on its selected qualification. Qualification score of an opportunity compared to the opportunity qualification goal, answering whether the opportunity is qualified. Difference between actual opportunity sales days and estimated opportunity sales days.
Product Opportunity	F_OPPY_PRODUCT	Contains snapshot information for product price and discount for each sales opportunity, which enables pipeline analysis by product or item. The fact data is a snapshot captured at the Opportunity and Product grain level.	Length of time an opportunity takes to close for a particular product. Difference between the estimated and actual revenue recognition for a particular product. Average confidence of the opportunity to generate an order. Average score of all opportunities that the sales team has won for a given year. Qualification goal of an opportunity based on its selected qualification. The weighted product item price in the opportunity. The confidence percent for an opportunity. Whether a product listed in the opportunity is the primary product.
Order Association	F_ORD_ASSOC	Maintains relationship data between an order capture and its associated objects, such as sales lead, opportunity, service order, RMA, and so on. This fact table describes the association between an order and its source and the data is a transaction at each order association. That is, each order can be tied to each of the preceding business processes to analyze the effectiveness of strategic marketing and selling activities.	Number of orders generated from a lead. Number of orders generated from an opportunity. " What is the number orders originated through a particular lead" Number of transactions associated with an order. Number of service orders associated with a particular case. Number of RMAs associated with a particular case.
Order Capture	F_ORD_CAPTURE	Captures sales order line transaction metrics, enabling you to generate reports that tell you whether, for example, a sale is generated by a sales rep or partner, through which marketing activity the event occurred, or for which product and segment, and the respective business unit. The fact data is an order line for each transaction.	Number of orders and corresponding revenue averaged per month, for a particular sales rep. List price for a product. Average quantity ordered for a product. Price of the periodic recurring charge for a product. Frequency of the recurring charge for that instance of the product sale. Actual price used for an order.
Product Quote	F_PROD_QUOTE	Maintains product sales quote price and discount metrics at the product, sales quote grain, which are used in sales quote analysis. These metrics are analyzed by dimensions such as Product and Time. Granularity for this fact table is per Product per Quote.	Average discount and selling price for a product. Average quote price for a product. Average discount for a product. List price for a product. Quantity of product used for a set of quotes for that product.
Sales Quota	F_SALES_QUOTA	Maintains metrics used to analyze forecast, sales representative sales quotas and actual revenue. These metrics are analyzed by dimensions such as Sales Territory, Forecast ID, and Time Frame. The fact granularity is per sales representative, per forecast, per timeframe.	Average quota amount for a sales person, Total forecasted revenue amount in a sales timeframe. Actual revenue for a sales person in a sales timeframe.
Sales Quote	F_SALES_QUOTE	Contains sales quote and discount data by sales representative, by sales opportunity (from open to close) and contains metrics such as actual price and actual discount, which are used in sales quote analysis. These metrics are analyzed by dimensions such as Sales Rep and Time. Granularity is per sales representative, per Quote.	Average discount and selling price for a sales representative. Discount amount offered to a customer by a sales representative. Number of quotes for a particular product opportunity.

The following table describes the delivered Sales Data Mart dimension tables:

Sales Data Mart Dimension Tables

Dimension Name	Dimension Record Name	Description
Competitor	D_COMPETITOR	Maintains competitor information. A competitor is a company that you are competing against for a sales opportunity.
Confidence	D_CONFIDENCE	Maintains information about sales confidence. The data is EPM defined data through Dimension Mapper.
Fallout Reason	D_FALLOUT_RSN	Maintains fallout reason information. It is the principal reason for losing an opportunity to a competitor.
Forecasts	D_FORECAST	Stores information about forecasts, which are forward looking estimations of income or performance.
Sales Lead Rating	D_LEAD_RATING	Stores information about the sales lead rating. The rating describes the quality of the lead.
Opportunity Flag	D_OPPY_FLAG	Maintains information for the following opportunity flags: forecasted, open, closed, qualified, won, lost.
Order Status	D_ORD_STATUS	Maintains the order status of the order capture.
Partner Program	D_PRTR_PGM	Maintains partner program information. This dimension has the following hierarchy: Partner Program, Partner Subtype, Partner Type Partner Program, Partner Level.
Qualification	D_QUALIFICATION	Maintains qualification information for sales leads and opportunities.
Sales Quote Dimension	D_QUOTE	Maintains sales quote information.
Sales Representative	D_SALES_REP	Maintains information about sales representatives. Some agent attributes are associated with HCM person data.
Sales Stage	D_SALES_STAGE	Maintains sales stage (also known as Sales Cycle) information that indicates the stage of the opportunity during the sales cycle.
Sales Status	D_SALES_STATUS	Maintains sales lead or opportunity status information.
Order Ship Type	D_SHIP_TO_TYPE	Maintains order ship to type information for orders.
SIC Code	D_SIC	Maintains Standard Industrial Classification (SIC) code information.
Timefame	D_TIMEFRAME	Maintains information about timeframes. Timeframes are dynamic time periods used for calculation purposes.

Service Data Mart

The Service data mart gives organizations the ability to analyze the effectiveness of their contact center by understanding call and case load trends, agent utilization, cost to support products and customers, up-sell and cross-sell success rates, product quality, and performance of service-level agreements. With this knowledge, organizations can lower the cost to serve, as well as increase service revenue, by maximizing each customer contact to provide the most effective service and generate service and sales revenue through targeted offers.

Service data mart helps you analyze all contact center activities, such as support activities, calls, cases, and resolutions, and test their effectiveness through various communication channels. Analysis of contact center communication channels in the context of CRM transactions provides insight into your organization's ability to service your customer base. Use it to better understand product quality, call and case load trends, agent utilization, the cost to support products and customers, and performance to service level agreements.

Support Business Process

Service data mart provides insight into PeopleSoft's Support business process, which is also known as Integrated Service Management business process. This process follows the life span of a service incident, including revenue generation by way of offers. This business process also includes the process of reporting and resolving product defects and the analysis of contact center performance.

Service data mart provides insight into various stages of the Support business process, enabling an organization to improve business process efficiency through this knowledge.

Access, Resolve, and Recommend are the business sub-processes related to the Service data mart. The Access sub-process involves capture, entitle, and assign functions. The Resolve sub-process involves the search and resolve functions. The Recommend sub-process enables management to analyze offer success and loss rates and understand which offers are most effective.

Service Data Mart Delivered Fact and Dimension Tables

This section discusses the delivered fact and dimension tables for the Service Data Mart.

Service Data Mart Data Mart Fact Tables

Fact Name	Fact Record Name	Description	Helps Answer
Case	F_CASE	Maintains accumulated snapshot cost and time metrics for measuring the effectiveness and efficiency of support cases.	How long a case has been open. Number of cases with an open status. Number of opened cases. Number of cases with a closed status. Number of reopened cases. Number of cases closed on the same day.
Case Association	F_CASE_ASSOC	Maintains relationship data between a contact center case and its associated objects, such as sales lead, sales order, service order, defect, and so on.	Number of times a case is associated with other business processes, such as a case generating a sales lead or an order. Number of cases generated form a sales lead. Number of cases generated for an order. Number of cases generated with an RMA. Number of cases followed with a customer survey. Number of cases related to a product defect.
Case History	F_CASE_HIST	Maintains change history for each case life cycle from open to close. The fact data maintains the basis of the measurement on case reassignment and case escalation. The fact is captured whenever a case is updated.	Length of time to solve cases for a specific product.
Case Resolution	F_CASE_RESOLVE	Maintains the transaction data to analyze case resolution and agent to solution performance. The fact grain is for each resolution, for each case.	Number of cases resolved using solutions from the solution library. Average number of resolutions for a support case. Number of cases closed without resolution.
Case Survey	F_CASE_SURVEY	Maintains the survey rating data for customer support cases.	Number of customer surveys. Weight value for a survey script. Score for a survey script. Customer satisfaction with the results of their cases.
Case Trend	F_CASE_TR	Contains daily (periodic) snapshots of case status data from the F_CASE table. This table enables you to analyze cases over a period of time using measures such as new cases, closed cases, pending cases, or average time to close. This table is sourced from F_CASE_HIST.	Number of open, closed, or pending cases with respect to the snapshot date. Age of a case with respect to the snapshot date. Average number of days taken to close support cases. Rates of first call resolution.
Call Detail	F_CC_CALL	Maintains aggregate contact center call metrics. The fact is sourced from third-party CTI suppliers. It can be integrated into CRM interactions through the Connection ID.	Average wait time for customer calls. Average call volume for a specific month. Average duration for customer calls. Number of outbound or inbound calls for a specific day. Average number of agents handling a customer call. Total call time in seconds. Total call conference time in seconds.
Chat	F_CC_CHAT	Maintains metrics to determine agent load, volume of chat activity.	Number of chats for a specific day. Number of chats related to cases. Number of chats related to an order. Chat length.
Email	F_CC_EMAIL	Maintains accumulated snapshot email volume, category assignment activity, response time effectiveness, and template accuracy, plus and utilization metrics.	Total number of inbound emails to support customers. Number of SPAM emails to support customers. Number of outbound emails. Number of closed emails when supporting customers. Number of times an email was assigned.
Email Assignment	F_EMAIL_ASSIGN	Maintains assignment duration metrics for worklists and agents.	Average agent assignment for handling emails from customer who requested support. Average time duration before an email is handled by an agent.
Entitlement	F_ENTITLEMENT	Maintains periodic snapshot entitlement metrics for customers and products at the entitlement grain.	Number of entitlements for a specific customer and product. Which agreements and entitlements exist for my customers. Number of days does a particular service agreement expire. Number of times a particular service agreement renewed.
Customer Interaction	F_INTERACT	Maintains customer interaction metrics.	Total number of customer interactions associated with a sales lead. Total number of customer interactions associated with a support case. Total number of customer interactions associated with a service order. Total number of customer interactions associated with an order capture. Number of calls/emails/chats received for a particular day.
Solution	F_SOLUTION	Maintains accumulated snapshot usage and solved count for each solution in the contact center. The fact data also reflects the solutions submitted by agents.	Number of times a particular resolution was used to solve a problem. Number of cases were solved with a particular solution. Number of times the solution actually solve the problem.

The following table describes the delivered Service Data Mart dimension tables:

Service Data Mart Dimension Tables

Dimension Name	Dimension Record Name	Description
Contact Center Agent	D_AGENT	Defines the contact center agent. Some agent attributes are associated with HCM person data. The dimension has the following hierarchy: Agent, Provider Group, Provider Group Type.
Customer Support Agreement	D_AGREEMENT	Maintains customer support agreement information. The dimension has the following hierarchy: Agreement Line, Renewal Num, Agreement.
Agreement Line Status	D_AGRMNT_STAT	Maintains customer support agreement and agreement line status information. The source, PSXLATIEM, is accessed twice to complete the dimension.
Call Type	D_CALL_TYPE	Maintains call type information. The call type is defined in the EPM translate for contact center third-party supplier use.
Support Case	D_CASE	Maintains customer support case information. The dimension has the following hierarchies: Case, Case Type, Case, Case Source, Case, Case Detail, Case Detail Type, Case Category, Case, Case Impact, Case, Reopen Reason, Case, Closed Reason, Case, Resolve, Case, Restore.
Case Priority	D_CASE_PRIORITY	Maintains support case priority information. The dimension has the following hierarchy: Priority, Priority Category.
Case Severity	D_CASE_SEVERITY	Maintains support case severity information.
Case Status	D_CASE_STATUS	Maintains support case status information. The dimension has the following hierarchy: Case Status, Case Status Category.
Email Mailbox	D_EML_BOX	Maintains information about mailboxes associated with emails. The dimension has the following hierarchy: Mailbox, Mailbox Type.
Email Template Category	D_EML_CATEGORY	Defines the template categories assigned to an email.
Employee	D_EMPLOYEE	Tracks employee data for agents and general employees. Attributes for an employee can be employee job code, department, or location.
Entitlement	D_ENTITLEMENT	Maintains customer support service entitlement information. The dimension has the following hierarchy: Entitlement, Entitlement Plan.
Install Product Component	D_INST_PROD	Used by the Installed Product component to determine the type of product that is being registered for the Customer, Site, Department, or Employee.
Contact Center Interaction	D_INTERACTION	Defines contact center interaction transactions.
Interact Method	D_INTERACT_MTHD	Defines the method of an interaction.
Interaction Status	D_INTERACT_STAT	Defines the status (completed, in progress) and detail type (email, chat, phone) of an interaction. The hierarchy is: Status, Detail Type.
Case Links	D_LINKS	Stores information about the case links an agent can associate with a case; for example, service order and sales order links.
Provider Group	D_PROVIDER_GRP	Organizes customer support agents into provider groups. Cases and service orders are assigned first to a provider group, then to an agent in the provider group. Agents roll up to Provider Groups.
Resolution Source	D_RESOLVE_SRC	Maintains customer support case resolution source information.
Case Solution	D_SOLUTION	Maintains customer support case solution information. The dimension has the following hierarchies: 1. Solution, Solution Library 2. Solution, Solution Type.
Solution Status	D_SOLUTION_STAT	Maintains support solution status and resolution status information. The source for both fields is PSXLATITEM. There are two ETL jobs to populate this dimension.
Survey Instance	D_SURVEY_INST	Maintains customer support survey instance information. Survey instances are associated with a support cases.
Survey Rating	D_SURVEY_RATING	Maintains customer support survey rating information.
Support Survey Script	D_SURVEY_SCRIPT	Maintains customer support survey script information. The dimension has the following hierarchies: 1. Script, Script Category 2. Script, Script Type.
Email Workgroup	D_WORK_GRP	Maintains information relating to email work groups.

Customer Segment Data Mart

Customer Segment data mart provides the analysis of customer behavior as a result of the activities of the CRM business processes. One of an organization's goals is to manage a strategic segment of customers that are identified as having the potential for high value. There are many activities performed throughout the CRM business processes that influence the behavior of these desired segments.

Marketing Business Process

Customer Segment data mart is part of PeopleSoft's Marketing business process, which is also known as Customer Lifecycle Marketing business process. A marketing organization can segment its customer and prospect base using multiple hierarchical structures. In the List Management business sub-process, PeopleSoft provides functionality through the Audience Builder component to define, manage and update segments and hierarchies. PeopleSoft Marketing uses segment groups to define these hierarchies within a set of segments. Within the segment groups, you can segment primary audiences into smaller groups, enabling effective analysis of various campaign strategies. Audience membership can be static or dynamic in nature. You can re-execute criteria used to define audiences to establish an updated snapshot of each segment. This closed loop reporting enables marketers to assess the performance of programs targeting their strategic customer segments.

The List Management business sub-process is related to the Customer Segment data mart. This business sub-process examines the measures by audiences, segments, or both, to help determine "hot lists." This process also helps to identify list quantity issues that marketers can potentially address. Customer segment analysis using customer acquisition and customer segment reports identify the most effective campaigns used to generate leads and sales. You can reuse the attributes of these campaigns to create similar marketing programs that are designed to illicit similar customer behavior in the strategic segment that you have selected. Using the Segment Migration fact, you can perform segment response analysis by tracking the effectiveness of CRM activities in influencing your customer base over time.

Customer Segment Data Mart Delivered Fact and Dimension Tables

This section discusses the delivered fact and dimension tables for the Customer Segment Data Mart.

Customer Segment Data Mart Data Mart Fact Tables

Fact Name	Fact Record Name	Description	Helps Answer
Customer Acquisition	F_CUST_ACQUIRE	Maintains measures of new customers by date of first sale. The grain of the fact table is captured at the customer level.	Total amount of a customer's first order. Number of sales interactions, such as meetings or phone calls, that occurred before a customer made the first purchase. Number of direct mailings before a customer made the first purchase. Number of inbound calls made before a customer made the first purchase. Number of interactions that occurred before a customer made a first purchase.
Customer Count	F_CUST_COUNT	Maintains periodic snapshot counts of customers.
Customer Segment	F_CUST_SEGMENT	Maintains a periodic snapshot of customer segment composite metrics. The grain of the fact table is the customer level.	Number of interactions, by channel, servicing a specific strategic segment. Average order amount for a specific segment's customers. Number of direct mail interactions for a specific segment's customers. Number of telemarketing interactions for a specific segment's customers. Number of cases requested from a specific segment's customers. Number of cases resolved on the first call from a specific segment's customers. Number of sales orders are generated for a specific segment's customers.
Customer Segment Migrate	F_SEG_MIGRATE	Maintains periodic snapshot customer segment membership information. Each organization tracks strategic segments of customers determined by predefined criteria that are tracked by sales marketing organizations. You can validate activities created to influence a desired migration of customers from one segment to another. The grain of the fact is the customer level.	Number of customers moving from a low-value segment to a high-value segment. Number of customers in a specific segment migrating to a different segment.
Segment Count	F_SEGMENT_COUNT	Maintains periodic snapshot customer counts for customer segments.	Number of new customers in a specific segment. Number of existing customers in a specific segment. Number of customers in a specific segment that have not placed an order in the past 18 months.

The following table describes the delivered Customer Segment Data Mart dimension tables:

Customer Segment Data Mart Dimension Tables

Dimension Name	Dimension Record Name	Description
Role Type	D_ROLE_TYPE	Maintains information about the role played by a contact, partner, company or an individual. Data is sourced from E-CRM BORM.

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