Customer relationship management cube (smmCube) for Microsoft Dynamics AX 2012 and Microsoft Dynamics AX 2012 Feature Pack
Important
This content is archived and is not being updated. For the latest documentation, see Microsoft Dynamics 365 product documentation. For the latest release plans, see Dynamics 365 and Microsoft Power Platform release plans.
Applies To: Microsoft Dynamics AX 2012 Feature Pack, Microsoft Dynamics AX 2012
Use the Customer relationship management cube to report on sales transactions.
Configuration keys
The following configuration keys are required to use all features of the Customer relationship management cube:
General ledger (LedgerBasic)
Leads (SmmLead)
Quotations (QuotationBasic)
Project I (ProjBasic)
Tables and views
The Customer relationship management cube uses data from the following tables and views:
smmActivites table
smmActivityPhaseGroup table
smmActivityPlanGroup table
smmActivityTypeGroup table
smmBusSectorGroup table
smmCampaignGroup table
smmCampaignReasonGroup table
smmCampaignTargetTable table
smmCampaignTypeGroup table
smmLeadPriorityTable table
smmLeadRatingTable table
smmLeadTypeTable table
smmQuotationCompetitorGroup table
smmQuotationProbabilityGroup table
smmQuotationPrognosisGroup table
smmQuotationReasonGroup table
smmResponsibilityGroup table
smmSalesUnit table
smmSourceTypeTable table
smmCampaignView view
smmCustBusRelView view
smmLeadByCampaignView view
smmLeadView view
smmOpportunityByCampaignView view
smmOpportunityByCompetitorView view
smmOpportunityView view
smmProcessStageTemplateView view
smmProjectSalesView view
smmSalesTargetTransView view
Measures
The Customer relationship management cube includes the following measure groups.
Activity
This measure group is based on the SMMActivities table and includes the following measures.
Measure |
Measure field name |
Aggregation |
Description |
Associated dimensions |
|---|---|---|---|---|
Activity count |
Not applicable |
Count |
The number of activities. |
Company Phase for quotation Activity plan table Activity type Responsibility table Activities Date (activities – end date time) Date (activities – start date time) Worker (activities – done by worker Worker (activities – worker responsible) Date (date opened) Date (date closed) Date |
Competitor group
This measure group is based on the SMMQuotationCompetitorGroup table and includes the following measures.
Measure |
Measure field name |
Aggregation |
Description |
Associated dimensions |
|---|---|---|---|---|
Competitor group count |
Not applicable |
Count |
The number of competitors. |
Company Competitor |
Leads by campaigns
This measure group is based on the SMMLeadByCampaignView view and includes the following measures.
Measure |
Measure field name |
Aggregation |
Description |
Associated dimensions |
|---|---|---|---|---|
Leads by campaigns count |
Not applicable |
Count |
The number of leads by campaign. |
Company Campaign |
Opportunities by campaigns
This measure group is based on the SMMOpportunityByCampaignView view and includes the following measures.
| Measure |
Measure field name |
Aggregation |
Description |
Associated dimensions |
|---|---|---|---|---|
| Opportunities by campaigns count |
Not applicable |
Count |
The number of opportunities by campaign. |
Company Campaign Date (date opened) Date |
| Opportunities by campaigns estimated revenue |
SMMOpportunityByCampaignView.Estimated_Revenue |
Sum |
The estimated revenue on opportunities from campaigns. |
Opportunities by competitor group
This measure group is based on the SMMOpportunityByCompetitorView view and includes the following measures.
| Measure |
Measure field name |
Aggregation |
Description |
Associated dimensions |
|---|---|---|---|---|
| Opportunities by competitor group count |
Not applicable |
Count |
The number of opportunities by competitor group. |
Company Competitor Opportunities by competitor group Date (date opened) Date |
| Opportunities by competitor group estimated revenue |
SMMOpportunityByCompetitorView.EstimatedRevenue |
Sum |
The estimated revenue on opportunities by competitor group. |
Sales
This measure group is based on the SMMProjectSalesView view and includes the following measures.
| Measure |
Measure field name |
Aggregation |
Description |
Associated dimensions |
|---|---|---|---|---|
| Sales count |
Not applicable |
Count |
The number of sales. |
Company Sales management units Customers - prospects Date (sales – invoice date) Date (exchange rate date) Date Worker (sales – sales person) |
| Invoice revenue |
SMMProjectSalesView.InvoiceRevenue |
Sum |
The total invoiced revenue. |
Lead
This measure group is based on the SMMLeadView view and includes the following measures.
| Measure |
Measure field name |
Aggregation |
Description |
Associated dimensions |
|---|---|---|---|---|
| Lead count |
Not applicable |
Count |
The number of lead records. |
Company Lead type Lead priority Lead rating Source type Quotation reason Sales management units Process stage Customers - prospects Lead Worker (lead – closed by worker) Worker (lead – opened by worker) Worker (lead – owner) Date (date opened) Date (date closed) Date |
| Days to close lead |
SMMLeadView.DaysToClose |
Sum |
The number of days to close a lead record. |
|
| Days opened lead |
SMMLeadView.DaysOpened |
Sum |
The number of days a lead record has been open. |
Opportunity
This measure group is based on the SMMOpportunityView view and includes the following measures.
| Measure |
Measure field name |
Aggregation |
Description |
Associated dimensions |
|---|---|---|---|---|
| Opportunity count |
Not applicable |
Count |
The number of opportunity records. |
Company Source type Quotation probability Quotation prognosis Quotation reason Sales management units Process stage Customers - prospects Opportunity Worker (worker – responsible – owner) Worker (opportunity – owner) Worker (opportunity – opened by worker) Worker (opportunity – close by worker) Date (date opened) Date (date closed) Date |
| Days to close opportunity |
SMMOpportunityView.DaysToClose |
Sum |
The number of days to close an opportunity record. |
|
| Days opened opportunity |
SMMOpportunityView.DaysOpened |
Sum |
The number of days that an opportunity record has been open. |
|
| Actual revenue |
SMMOpportunityView.ActualRevenue |
Sum |
The total invoiced revenue for the opportunity. |
|
| Estimated revenue |
SMMOpportunityView.Estimated_Revenue |
Sum |
The total opportunity estimated revenue. |
|
| Opportunity won |
SMMOpportunityView.OpportunityWon |
Sum |
The number of opportunities won. |
|
| Opportunity lost |
SMMOpportunityView.OpportunityLost |
Sum |
The number of opportunities lost. |
Sales targets transactions
This measure group is based on the SMMSalesTargetTransView view and includes the following measures.
| Measure |
Measure field name |
Aggregation |
Description |
Associated dimensions |
|---|---|---|---|---|
| Sales targets transactions count |
Not applicable |
Count |
The number of sales target transactions. |
Company Sales management units Sales targets transactions Date (sales target transactions – target date) Date (exchange rate date) Worker (sales target transactions – sales person) Date Worker (sales – sales person) |
| Sales targets transactions amount |
SMMSalesTargetTransView.AmountMst |
Sum |
The amount of sales target transactions. |
Campaign
This measure group is based on the SMMCampaignView view and includes the following measures.
Measure |
Measure field name |
Aggregation |
Description |
Associated dimensions |
|---|---|---|---|---|
Campaign count |
Not applicable |
Count |
The number of campaigns. |
Company Process stage Campaign Date (campaign date) Date (campaign – end date) Date (campaign – follow-up date) Worker (campaign – worker responsible) Worker (campaign – worker) Date |
Customers - prospects
This measure group is based on the SMMCustBusRelView view and includes the following measures.
Measure |
Measure field name |
Aggregation |
Description |
Associated dimensions |
|---|---|---|---|---|
Customers – prospects count |
Not applicable |
Count |
The number of prospects. |
Currency Company Customers - prospects Date (created date) Worker (customer – prospect – worker responsible) |
Exchange rates by day
This measure group is based on the BIExchangeRateView view and includes the following measures.
Measure |
Measure field name |
Aggregation |
Description |
Associated dimensions |
|---|---|---|---|---|
Exchange rate |
BIExchangeRateView.CrossRate |
Max |
The exchange rate. |
Currency Date (exchange rate date) Analysis currency Date |
Calculated measures
The Customer relationship management cube contains the following calculated measures.
Calculated measure |
Aggregation |
Description |
|---|---|---|
Average opportunity days open |
Average |
The average number of days that opportunity records are open. |
Average days to close opportunity |
Average |
The average number of days that it takes to close an opportunity record. |
Opportunity win loss percentage |
Sum |
The percentage of won opportunities. |
Average lead days open |
Average |
The average number of days that a lead record is open. |
Average days to close lead |
Average |
The average number of days that it takes to close a lead record. |
Key performance indicators
The following sections describe the key performance indicators (KPIs) in the Customer relationship management cube.
KPI calculations
The following table lists the KPIs that are associated with the Customer relationship management cube. You can use the information in the following table to help verify the information in your KPIs.
KPI |
Calculation |
|---|---|
Active Leads |
Lead count where Lead Status = 0 (Open) |
Active Opportunities |
Opportunity count where Opportunity Status = 1 (None) |
New Leads |
Lead count in the past 7 days |
New Opportunities |
Opportunity count in the past 7 days |
Sales |
Project and customer invoice sales total |
Role Centers
The following table lists the Role Centers and web parts that display the KPIs associated with the Customer relationship management cube.
Role Center |
Web parts and KPIs |
|---|---|
Sales and marketing executive |
Business overview:
KPI list:
|
Sales manager |
Business overview:
KPI list:
|
Super sales representative |
KPI list:
|
Dedicated sales representative |
KPI list:
|
Marketing manager |
Business overview:
KPI list:
|
Marketing staffer |
KPI list:
|
Account manager |
KPI list:
|
Security
The Customer relationship management cube can be accessed by users assigned to the following Microsoft SQL Server Analysis Services roles.
Chief executive officer
Marketing coordinator
Marketing manager
Sales manager
Sales representative