Share This Post

Dynamics AX Updates

Dynamics AX Marketing and Sales Cube

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

Deployment

The Sales and marketing cube is added to the Dynamics AX project.

Configuration keys

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

  • General ledger (LedgerBasic)
  • Leads (smmLead)
  • Quotations (QuotationBasic)
  • Project I (ProjBasic)

Tables and views

The Dynamics AX Sales and Dynamics AX marketing cube use data from the following tables and views:

  • smmBusSectorGroup table
  • smmLeadPriorityTable table
  • smmLeadRatingTable table
  • 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 Microsoft Dynamics AX Sales and Microsoft Dynamics AX marketing include the following measure groups.

Project Sales Order

This measure group is based on the ProjTransPostingCube see and adds the following measures.

Measure

Measure field name Aggregation Description Associated dimensions
Project sales order count Not applicable Count  The number of sales orders of the type project.  Company

Activity

Worker

Projects

Date (date –  date)

Project sales revenue ProjTransPostingCube.ActualRevenue Sum  The total revenue for the sales orders of type project.

Sales Order

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

Measure

Measure field name Aggregation Description Associated dimensions
Sales order count Not applicable Count  The number of sales orders of the 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 the type sales.

Sales

This measure group is based on the smmProjectSalesView view and adds the following measures.

Measure Measure field name Aggregation Description Associated dimensions
Sales count Not applicable Count  The number of invoices for both sales and the project.  Company

Sales management  units

Worker

Date (sales –  invoice date)

Date (sales target –  target date)

Worker (sales –  salesperson)

Invoice revenue smmProjectSalesView.InvoiceRevenue Sum  The total invoiced amount for both sales and the project.

Sales target

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

Measure Measure field name Aggregation Description Associated dimensions
Sales target count Not applicable Count  The number of sales targets the transactions.  Company

Sales  management  units

Worker

Date (sales target  – target date)

Worker (sales –  salesperson)

Sales targets transactions amount smmSalesTargetTransView.AmountMST Sum  The amount of the sales target transactions.

Campaign

This measure group is based on the smmCampaignView view and adds the following measures.

Measure Measure field name Aggregation Description Associated dimensions
Campaign count Not applicable Count  The number of the 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 campaign.

Customer – prospect

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

Measure

Measure field name Aggregation Description Associated dimensions
Customer – the 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 adds the following measures.

Measure Measure field name Aggregation Description Associated dimensions
Lead count Not applicable Count  The number of the 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 the lead  record.
Days opened lead smmLeadView.DaysOpened Sum  The number of days the lead record has been open.

Opportunity

This measure group is basically based on the smmOpportunityView.

Sales quotation

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

Measure Measure field name Aggregation Description Associated dimensions
Sales quotation-count Not applicable Count  The amount of sales quotations.  Company

Released products

Campaign

Customer – prospect

Opportunity

Sales quotation

Sales management  units

Worker

Sales quotation amount smmQuotationView.LineAmount Sum  The total number of sales quotations.

Exchange rates by day

This measure group is based on the BIExchangeRateView view and adds 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 Dynamics AX Sales and Dynamics AX marketing cube contain the following calculated measures.

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

Key performance indicators

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

KPI calculations

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

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

Security

The Sales and marketing cube can be accessed by the users assigned to the following Microsoft SQL Server Analysis Services roles.

  • Chief executive officer
  • Marketing Coordinator
  • Marketing manager
  • Sales manager
  • Sales representative

Share This Post

Leave a Reply

avatar
  Subscribe  
Notify of
Skip to toolbar