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: Sales Average days to close opportunity New leads Average days to close lead New opportunities Opportunities per segment KPI list: New opportunities Active opportunities New leads Active leads Sales per sales unit Sales per salesperson
Sales manager	Business overview: Sales Average days to close opportunity New leads Average days to close lead New opportunities Opportunities per segment KPI list: New opportunities Active opportunities New leads Active leads Sales per sales unit Sales per salesperson
Super sales representative	KPI list: New leads Active leads New opportunities Active opportunities Sales per sales unit
Dedicated sales representative	KPI list: New leads Active leads New opportunities Active opportunities Sales per sales unit
Marketing manager	Business overview: Sales New leads Average days to close lead Opportunities per segment Leads per segment KPI list: New leads Active leads Sales per sales unit
Marketing staffer	KPI list: New leads Active leads
Account manager	KPI list: New opportunities Active opportunities Sales per sales unit

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