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
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
- Blox – https://www.sisense.com/marketplace/blox-widgets/
- Image Widget – https://support.sisense.com/hc/en-us/community/posts/221224568-Adding-Logo-Image-to-a-dashboard?flash_digest=2e5c4a60b0bb7eb303b8aa3a1cbf5b067f1787b1
- Distribute Widgets equally in a row –
- 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"
}
]
}
]
}
]
}
]
}
]
}
Data Connectors
