Sales and marketing cube (smmSalesMarketingCube) for Microsoft Dynamics AX 2012 R2 and R3

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 R3, Microsoft Dynamics AX 2012 R2

The Sales and marketing cube for Microsoft Dynamics AX is used to report on sales transactions. This article provides details about the cube.

Deployment Configuration keys Tables and views Measures Calculated measures Key performance indicators Security

Analytics in Microsoft Dynamics AX Cube and KPI reference for Microsoft Dynamics AX 2012 R2 and R3 Cube and KPI reference for Microsoft Dynamics AX 2012 and Microsoft Dynamics AX 2012 Feature Pack

Deployment

The Sales and marketing cube is included in the Dynamics AX project. For information about how deploy the Dynamics AX project—and the cubes that it contains—see Deploy the default cubes.

Configuration keys

The following configuration keys are required to use all features of the Sales and marketing cube:

• General ledger (LedgerBasic)

• Leads (smmLead)

• Quotations (QuotationBasic)

• Project I (ProjBasic)

Tables and views

The Sales and marketing cube uses data from the following tables and views:

• smmBusSectorGroup table

• smmLeadPriorityTable table

• smmLeadRatingTable tabe

• smmLeadTypeTable table

• smmQuotationProbabilityGroup table

• smmQuotationPrognosisGroup table

• smmSalesUnit table

• smmSourceTypeTable table

• ProjTransPostingCube view

• smmActivitiesCube view

• smmCampaignView view

• smmCustBusRelView view

• smmCustInvoiceJourSalesView view

• smmLeadView view

• smmOpportunityView view

• smmProcessStageTemplateView view

• smmProjectSalesView view

• smmQuotationView view

• smmSalesTargetTransView view

Measures

The Sales and marketing cube includes the following measure groups.

Project sales order

This measure group is based on the ProjTransPostingCube view and includes the following measures.

Measure	Measure field name	Aggregation	Description	Associated dimensions
Project sales order count	Not applicable	Count	The number of sales orders of type project.	Company Activity Worker Projects Date (date – date)
Project sales revenue	ProjTransPostingCube.ActualRevenue	Sum	The total revenue for sales orders of type project.

Sales order

This measure group is based on the smmCustInvoiceJourSalesView view and includes the following measures.

Measure	Measure field name	Aggregation	Description	Associated dimensions
Sales order count	Not applicable	Count	The number of sales orders of type sales.	Company Released products Campaign Customer - prospect Sales quotation Sales management units Worker Date (date – date) Date (exchange rate date)
Sales order revenue	smmCustInvoiceJourSalesView.InvoiceRevenue	Sum	The total invoiced revenue of type sales.

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 invoices for both sales and project.	Company Sales management units Worker Date (sales – invoice date) Date (sales target – target date) Worker (sales – sales person)
Invoice revenue	smmProjectSalesView.InvoiceRevenue	Sum	The total invoiced amount for both sales and project.

Sales target

This measure group is based on the smmSalesTargetTransView view and includes the following measures.

Measure	Measure field name	Aggregation	Description	Associated dimensions
Sales target count	Not applicable	Count	The number of sales target transactions.	Company Sales management units Worker Date (sales target – target 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 Campaign Process stage Activity Worker Projects Date (date – date) Projects (projects – parent project)
Actual campaign cost	smmCampaignView.ActualCostCommitted	Sum	The posted project expenses for the campaign.

Customer - prospect

This measure group is based on the smmCustBusRelView view and includes the following measures.

Measure	Measure field name	Aggregation	Description	Associated dimensions
Customer – prospect count	Not applicable	Count	The number of prospects.	Currency Company Customer - prospect Worker Date (date – date opened)

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 Source type Lead rating Campaign Customer - prospect Lead Process stage Sales management units Worker Date (date – date) Worker (worker – opened by) Worker (worker – closed by) Date (date – date opened) Date (date – date closed)
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 Campaign Customer - prospect Process stage Opportunity Quotation probability Quotation prognosis Sales management units Worker Date (date – date) Worker (worker – opened by) Worker (worker – salesperson – owner) Worker (worker – closed by) Date (date – date opened) Date (date – date closed)
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 gained from opportunity	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.

Activity

This measure group is based on the smmActivitiesCube view and includes the following measures.

Measure	Measure field name	Aggregation	Description	Associated dimensions
Activity count	Not applicable	Count	The number of activities.	Company Activity Worker Date (date – date opened) Date (date – date closed) Date (date – start date) Date (date – end date)

Sales quotation

This measure group is based on the smmQuotationView view and includes the following measures.

Measure	Measure field name	Aggregation	Description	Associated dimensions
Sales quotation count	Not applicable	Count	The number of sales quotations.	Company Released products Campaign Customer - prospect Opportunity Sales quotation Sales management units Worker
Sales quotation amount	smmQuotationView.LineAmount	Sum	The total amount of sales quotations.

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

Calculated measures

The Sales and marketing cube contains the following calculated measures.

Calculated measure	Aggregation	Associated measure group	Description
Average opportunity open days	Average	Opportunity	The average number of days that an opportunity record is open.
Average days to close opportunity	Average	Opportunity	The average number of days that it takes to close an opportunity record.
Opportunity win loss percentage	Sum	Opportunity	The percentage of won opportunities.
Average lead open days	Average	Lead	The average number of days that a lead record is open.
Average days to close lead	Average	Lead	The average number of days that it takes to close a lead record.
Average sales order amount	Average	Sales order	The average value of sales orders.

Key performance indicators

The following sections describe the key performance indicators (KPIs) in the Sales and marketing cube.

KPI calculations

The following table lists the KPIs that are associated with the Sales and marketing cube. You can use the information in the following table to help verify the information in your KPIs.

KPI	Associated measure group	Calculation
Active leads	Lead	Lead count where Lead Status = 0 (Open).
Active opportunities	Opportunity	Opportunity count where Opportunity Status = 1 (None).
New leads	Lead	Lead count in the past 7 days.
New opportunities	Opportunity	Opportunity count in the past 7 days.
Sales	Sales order	Project and customer invoice sales total.
Average sales order amount	Sales order	Total sales orders (including project) divided by the number of sales orders.

Role Centers

The following table lists the Role Centers and web parts that display the KPIs associated with the Sales and marketing 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 Sales and marketing 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