Microsoft Dynamic CRM Dashboard

High Level Design

Overview

Microsoft Dynamics CRM is a customer relationship management software package developed by Microsoft. The product focuses mainly on sales, marketing, and service (help desk) sectors, but Microsoft has been marketing Dynamics CRM as a CRM platform and has been encouraging partners to use its proprietary Goals.

The integration between Sisense and MS Dynamics CRM will be created using CDATA JDBD connector:
https://www.cdata.com/drivers/dynamicscrm/jdbc/

Objectives

  • Integrate initiative widgets which provide a high-level performance overview compared to the Dynamics CRM existing reports for executive managers.
  • Identify revenue per campaign and trend over time
  • Monitor the campaign budget usage compared to plan
  • Product Sales overview
  • Monitor the number of employees globally

KPIs Architecture

Objectives

KPIs

Measures

Data Source

Understanding current state of the

Company through high level KPIs

$ Total Revenue

SUM(TotalRevenue)

Sales Dynamics CRM DB

Active Accounts

COUNT(AccountID)

Accounts Dynamics CRM DB

Leads

COUNT(LeadID)

Leads Dynamics CRM DB

Lead Type Segmentation

COUNT(LeadID)

Leads Dynamics CRM DB

Track the revenue over time

 

Company’s Revenue over the last quarters

 

SUM(TotalRevenue) based on Sale date

Sales Dynamics CRM DB

Campaign Analysis

Expected Revenue and AVG win probability (%) per campaign

SUM(TotalRevenue), AVG(WinProb) based on Sale date

Opportunities Dynamics CRM DB

Top 5 Campaigns by Budget Vs. Current Cost

SUM(TotalBudget), SUM(TotalCurrentCost)

Campaigns Dynamics CRM DB

Integration to Reports in MS Dynamics

iFrame Widget

Entities Relationship Diagram

MS-Dynamic-CRM-Entities-Relationship-Diagram

JDBC Connection Details

Connection String:

jdbc:dynamicscrm:[email protected]_dynamics_spaceaccount;Password=your_password;URL=your_full_dynamics_crm_url ;CRM Version=CRM Online;

JDBC JARs Folder:

C:\Program Files\CData\CData JDBC Driver for Dynamics CRM 2019\lib

Driver’s Class Name

cdata.jdbc.dynamicscrm.DynamicsCRMDriver

Plugins & Scripts

  1. Blox – https://www.sisense.com/marketplace/blox-widgets/
  2. Image Widget – https://support.sisense.com/hc/en-us/community/posts/221224568-Adding-Logo-Image-to-a-dashboard?flash_digest=2e5c4a60b0bb7eb303b8aa3a1cbf5b067f1787b1
  3. Distribute Widgets equally in a row –
  4. https://support.sisense.com/hc/en-us/community/posts/115005867428-Distribute-Equally-the-Widgets-in-a-Dashboard-Row

  5. iFrame Widget

https://support.sisense.com/hc/en-us/community/posts/221224848-iFrame-Widget-

 

 

Blox Scripts

Active Accounts, Total Revenue, Leads

{"style": "",
"script": "",
"title": "",
"showCarousel": false,
"horizontalAlignment": "center",
"body": [
{
"type": "Container",
"align - self": "center",
"items": [
{
"type": "ColumnSet",
"columns": [
{
"type": "Column",
"horizontalAlignment": "center",
"style": {
"justify - content": "center"
},
"items": [
{
"type": "Image",
"url": "https: //www.flaticon.com/premium-icon/icons/svg/1770/1770357.svg",
"spacing": "large",
"horizontalAlignment": "center",
"size": "medium"
},
{
"type": "TextBlock",
"text": "Active Accounts",
"size": "medium",
"color": "default",
"weight": "light",
"spacing": "small",
"horizontalAlignment": "center"
},
{
"type": "TextBlock",
"text": "{
panel: accounts
}",
"style": {
"align - self": "left",
"font - weight": "bold"
},
"size": "large",
"color": "default",
"spacing": "large",
"horizontalAlignment": "center",
"separator": true
},
{
"type": "ActionSet",
"actions": [
{
"type": "Action.OpenUrl",
"action": "lightbox",
"title": "Sisense Plugins Marketplace",
"url": "https: //www.sisense.com/marketplace/"
}
]
}
]
},
{
"type": "Column",
"horizontalAlignment": "center",
"style": {
"justify - content": "center"
},
"items": [
{
"type": "Image",
"url": "https: //www.flaticon.com/premium-icon/icons/svg/1700/1700258.svg",
"spacing": "large",
"horizontalAlignment": "center",
"size": "medium"
},
{
"type": "TextBlock",
"text": "Total Revenue",
"size": "medium",
"color": "default",
"weight": "light",
"spacing": "small",
"horizontalAlignment": "center"
},
{
"type": "TextBlock",
"text": "{
panel: data
}",
"style": {
"align - self": "left",
"font - weight": "bold"
},
"size": "large",
"color": "default",
"spacing": "large",
"horizontalAlignment": "center",
"separator": true
},
{
"type": "ActionSet",
"actions": [
{
"type": "Action.OpenUrl",
"action": "lightbox",
"title": "How to
import Microsoft Dynamic CRM Data ? ",
"url" : "https: //documentation.sisense.com/latest/managing-data/connectors/ms-dynamics-crm.htm"
}
]
}
]
},
{
"type": "Column",
"horizontalAlignment": "center",
"style": {
"justify - content": "center"
},
"items": [
{
"type": "Image",
"url": "https: //www.flaticon.com/premium-icon/icons/svg/1583/1583162.svg",
"spacing": "large",
"horizontalAlignment": "center",
"size": "medium"
},
{
"type": "TextBlock",
"text": "Leads",
"size": "medium",
"color": "default",
"weight": "light",
"spacing": "small",
"horizontalAlignment": "center"
},
{
"type": "TextBlock",
"text": "{
panel: leads
}",
"style": {
"align - self": "left",
"font - weight": "bold"
},
"size": "large",
"color": "default",
"spacing": "large",
"horizontalAlignment": "center",
"separator": true
},
{
"type": "ActionSet",
"actions": [
{
"type": "Action.OpenUrl",
"action": "lightbox",
"title": "Blox Documentation",
"url": "https: //documentation.sisense.com/latest/addons/sisenseblox.htm"
}
]
}
]
}
]
}
]
}
]
}

Implementation
Kit

The following resources will enable you to design your dashboard and data model with sample data and then apply it to your own data. Note that you will need to have a previously installed version of Sisense (you can use the free trial version if you’re not a customer).

Sample data and dashboard examples (direct download)

Documentation