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.

Reference

Deployment

Configuration keys

Tables and views

Measures

Calculated measures

Key performance indicators

Security

Resources

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