Author: Ravi Mukkelli

Free Accelerators & Apps for Dynamics 365 CE

Ravi MukkelliLeave a comment

As Dynamics 365 CE has become more app friendly, there are many free apps available on the Microsoft AppSource. In this blog post I have listed out most commonly used free aps and accelerators available.

DataExport  Microsoft Dynamics 365 – Data Export Service

Pros:

  1. Ease of use and configuration.
  2. The Azure DB can be part of a different tenant. Only Azure key vault should be part of the same tenant as Dynamics 365 CRM.
  3. Best app to export data from Online Dynamics 365 CRM to Azure DB which can be used to build a Data Warehouse for reporting or data transformations.
  4. Dynamics 365 users can control the schedules from within their CRM. Even an a Admin dashboard is provided.
  5. API’s are available for automation.

Cons:

  1. Occasional issues with data synchronization which can be fixed.
  2. Only supports Azure SQL DB.
  3. Need Azure subscription, so additional resource cost.

Alternative Approach : Can use CDS for reporting or use Flow to push the data.

Supported Version : Online Dynamics 365 Only

attachmentmanagement Attachment Management

This app can be used to push Email and Notes attachment to an Azure blob storage. The best use of this app will be for customers who use email marketing or store most of their documents within Notes (annotations) of Dynamics 365.

Pros:

  1. This app is a good alternative if customers doesn’t have document management products like SharePoint.
  2. Dynamics instance space can be effectively managed.
  3. Ease of use and configuration.

Cons:

  1. Larger attachment sizes could be a issue as most of the logic is part of a custom plugin and plugin execution is restricted to 2 mins max. If upload takes longer, then we will have timeout issues.
  2. Only supports Azure SQL DB.
  3. Need Azure subscription, so additional resource cost.

Alternative Approach : Can use CDS for reporting or use Flow to push the data.

Supported Version : Online Dynamics 365 Only

attachmentmanagement Customer Today!

This app can be used to get top news articles related to the customer. It uses Bing News Search API to search for the related content.

Pros:

  1. Most relevant news is pulled from the search which is good.
  2. The news information can be tagged to a Note or Task on click of a button for quick follow-ups.
  3. This feature is configurable even on custom entities.
  4. Configuration is easy as it uses a HTML web resource to pull up the information.

Cons:

  1. There were few issues while accessing it using Unified Interface i.e., adding to Notes failed.
  2. Need Azure subscription, so additional resource cost.

Alternative Approach : “Company News Timeline” app is a closer alternative. But its restricted to only Account, Contact, Lead & Opportunity and only works with Unified Interface. With the April 2019 release, this feature is by default available for Marketing.

Supported Version : Works on all versions.
attachmentmanagement Dynamics 365 Data Archival and Retention

This app is used to archive Dynamics 365 data to a Azure COSMOS DB. The best use of this app will be for customers who wants to play around with Big Data.

Pros:

  1. As this App uses Azure services its highly scalable.
  2. GDPR compliant and uses AD authentication, so security is not a concern.
  3. Its highly configurable i.e., data retention periods can be set, supports all entities, filtering of data is supported as it uses FetchXML to archive the data, scheduling of the archival jobs is easy.
  4. Jobs can be monitored easily.

Cons:

  1. Azure resources are extensively used, so the overall cost is expensive.
  2. Only supports Azure COSMOS DB.

Alternative Approach : Can use Data Export Service.

Supported Version : Online Dynamics 365/2016 Only
attachmentmanagement Email Machine Learning

This app is used for Sentiment analysis based on email responses. Based on the score, we can perform various operations and even extend it to the related entities. Need to make sure that the Azure Text Analytic services should be hosted/created as part of the same tenancy as Dynamics CRM 365.

Pros:

  1. As this App uses Azure services its highly scalable.
  2. Using the sentiment score, various rules like auto creation of Cases or routing the Cases etc., can be configured easily which will add more value to the business process.
  3. This solution can also is used to show a cumulative score on the Contact or Account entities based on the email communication.
  4. This app is configured to analyze multiple languages.
  5. Easy to configure.

Cons:

  1. Need Azure subscription, so additional resource cost.

Alternative Approach : Amazon or Google sentiment analysis services.

Supported Version : Dynamics 365/2016 Online & On-Premise.
attachmentmanagement Change Tracking Solution

This app is used to track the customization changes made to Entities, JavaScript, Plugins & Workflows.

Pros:

  1. Provides a high level audit log for every Publish made which includes customizations, workflows, business rules, plugins. A custom entity “Change Tracker” is used to log these audit changes.
  2. All the JavaScript web resource changes are well tracked.
  3. Can be used as part of the DevOps process for CI/CD, i.e., once the audit record is created either on Publish or Publish All event, we can trigger a Flow which can start a Azure DevOps process.

Cons:

  1. Audit information is not elaborate for few events like Publish All, Workflow changes etc.

Alternative Approach : Need to come up with custom plugins.

Supported Version : Dynamics 365/2016 Online & On-Premise.
HCL_PDF HCL Dynamics – PDF Generator

This app is used to generate PDF documents from the word templates on click of a button.

Pros:

  1. Configuration is easy. It can be configured per entity.
  2. Can save the generated PDF to notes.
  3. The PDF generation logic is part of a plugin and action. So this can be used as part of any workflow process and can be automated.

Cons:

  1. I had issues with the PDF formatting. Few template outputs were completely out of focus with irregular margins.

Alternative Approach : Flow can be used with some custom development.

Supported Version : Dynamics 365/2016 Online & On-Premise.

attachmentmanagement Actionable Audit

This app is used to track entity changes for Create, Update and Delete actions. It is more of like OOTB Audit but the only difference is its captured as part of an custom entity, so users can export or report on this data which is handy.

Pros:

  1. As the audit is pushed to a custom entity users can perform all kinds of reporting and even export to CSV.
  2. Supports custom entities too.

Cons:

  1. This app uses a custom approach of storing the Audit which involves plugins. These plugins fire on every configured event of the Entity which could degrade the performance.
  2. There are many manual configuration steps like the plugin registration, IFrame setup, Security Roles etc., only an experienced Dynamics administrator can perform.

Alternative Approach : None.

Supported Version : Dynamics 365/2016 Online & On-Premise.
attachmentmanagement Dynamics 365 Self Hosting Bots

“Dynamics 365 Bot” as the name suggest is a simple bot app. The bot uses Azure LUIS for the Q&A setup. This app can be really helpful for customer care or sales representatives to get quick info from Dyanmics 365 CRM instance.

Pros:

  1. Users who want to explore the bot framework this app will provide a good platform experience.
  2. Easily customizable as the source is available on Github.
  3. The bot provides a extendable framework which combines Dynamics 365 CDS entity model.

Cons:

  1. Need Azure subscription, so additional resource cost.
  2. Provides only basic information related to Opportunities, Cases, Accounts. To enhance the features we need more resources and time.

Alternative Approach : LiveAssist.

Supported Version : Dynamics 365/2016 Online.
attachmentmanagement Export To Excel Tracking

This app is used to track the data export via excel. There were many customers who requested this feature as they want to track who are when the business critical data has been exported.

Pros:

  1. Audit information is elaborate and also captures the query used i.e., FetchXML.
  2. Easy to configure and data is stored in a custom entity, so can be used for reporting.
  3. As the logic is part of a Plugin, it even works if export to excel is requested through a API call.

Cons:

  1. Its a plugin configured on the “RetrieveMultiple” event, so executes on every retrieve.

Alternative Approach : Custom plugin.

Supported Version : Dynamics 365/2016 Online and OnPremise.

There are few more available on the app source worth exploring like Azure Cosmos DB for DocumentDB API Data Provider, Smart Email Monitoring, Dynamics 365 Channel Integration Framework, Dynamics 365 Higher Education Accelerator

Alternate ways to find duplicates in Dynamics 365 CRM

Ravi MukkelliLeave a comment

In my previous blog post Finding duplicates using PowerBI I have explained how PowerBI can be used to find duplicates. It kept me thinking what are other effective feasible ways to find duplicates other than PowerBI. One of my customer was not using PowerBI and they had a huge dataset of contacts approximately 20 million, which needs to be cleansed. So, I started exploring

Solution 1 – Using Excel PowerQuery and Fuzzy matching

Using Excel’s fuzzy matching add-in we can easily find duplicates. As Microsoft has made the CDS connection available using Power Query (check this link), it has become a breeze to work on Dynamics 365 data.  Excel is great with smaller datasets but with the above described scenario it may not be a best fit, and more over there will be lot of manual effort involved. Even automation is really hard.

Solution 2 – Using SSIS Fuzzy Matching

The Dynamics 365 Data Export service is a free-add which exports the Dynamics 365 CRM data to any customer owned Azure SQL DB. This is a one of the best add-on’s Microsoft has come up, which can be used in building up a Data warehouse to perform all kinds of reporting and data validations. So, once the Dynamics 365 CRM data is synchronized to an Azure SQL DB, we can create SSIS packages to find duplicates. SQL Server SSIS supports “Fuzzy Grouping” which is one of the best options to filter duplicate data. Check the Microsoft Docs link for detailed configuration steps. The only catch is “Fuzzy Grouping” needs  access to SQL temp DB. So we cannot use Azure SQL DB in cloud, we need access to a local SQL Server or SQL Server on a cloud VM . The high level design is

image

Solution 3 – Using SQL SOUNDEX

“SOUNDEX” a predefined function part of Microsoft SQL, returns a four character  code to evaluate the similarity of two strings. For example consider the below contacts which have a similar sound. They seem to be duplicates with typo’s. The SOUNDEX(fullname) of full name results with a same sound code. Using this feature we can definitely show duplicates within Dynamics.

image

In the example above, you can see three duplicate contacts with typo’s. The SOUNDEX results show the same code being generated. The design I have used is to use the soundcode feature to identify duplicates.

image

I have created a new attribute within Contacts as “SoundCode” which will be used to capture the psuedo code from SOUNDEX. Once the data is synchronized using Data Export Service to a Azure SQL DB, I have created a insert trigger which updates the record with a sound code. This information is captured within the newly created attribute.

image

The idea is to show these probable duplicates back in Dynamics 365 within the Contacts form. So, for that I have created a simple Azure function which retrieves the probable duplicates by querying the Contact table from the Azure SQL DB.  I have created a simple HTML web resource which sends in a request to execute the Azure function and show the results within a table. This web resource is called on click of a button with in the Contact menu.

Considering the data volume of the customer, this solution proved out to give better results than the other solutions.

Find Duplicate Data in Dynamics using PowerBI

Ravi MukkelliLeave a comment

One of the critical tasks of today’s organizations is data quality and handling. The key to data quality is making sure that data is unique and filtered so that it will be easy to integrate with different systems with in the organization. To avoid duplicate within the data is one of he major tasks every organization is trying to hardwire within their critical systems. In this series of blog posts, I going to discuss few scenarios of handling duplicate data in Microsoft Dynamics 365.

We all know the traditional OOTB De-Duplication feature of Microsoft Dynamics 365 CRM, which is majorly used to detect duplicates. In few scenarios we even use “Unique Keys” to restrict duplicates. But these features either use “Exact” or ”Partial” match precisions. But this feature cannot handle typo’s or misspellings. Recently Microsoft has released the Fuzzy Matching feature as part of Power BI. For more details read FuzzyMatching PowerBI. Lets look at the steps to create a PowerBI report which shows up probable duplicates with in Dynamics 365 CRM system using Fuzzy Matching.

Open Power BI Desktop, create a new BI report. We are going to use the new CDS connector to connect to the Dynamics 365 CRM system. Select the Data Source as CDS connector.

image

Set the URL to the Dynamics 365 CRM instance.

image

Select the entity which requires to be filtered. Lets assume that we have duplicate data as part of Contact entity. As you can see the FirstName is misspelled.

image

Select the Contact Entity as shown below.

image

For our scenario, as the duplicate data is part of the contact names, lets select the fullname field along with contactid for filtering.

image

Once selected, the data will be shown in the view. The next step is to apply fuzzy matching on the data to filter out the duplicate data. Click the “Merge Queries” or “Merge Queries as New option”.image

From the Merge Wizard select the parent and the reference entity as Contact. We are going to perform an left outer join on the results to find the duplicates. Select the fullname column to create the merged table.

image

As we are going to use the Fuzzy match, check the box. Set the threshold to a value between 0 to 1.0. This is going be the driving factor to identify probable matches. I set at .90, so the understanding is to consider 90% probable matches. Rest are optional and if required can be checked.

image

Once the filter is applied the output clearly identifies the “Vincent Lauriant” as a probable duplicate contact. As part of this example I am going to filter out original records and show only the duplicate data. We can perform various operations at this stage to get the desired output. The simplest option I went for is to create index column to filter this data.

image

I have added a conditional column with a simple validation to filter out the non duplicates. I have used the “contactid” for that.

imageimage

Apply the filter to only show the duplicate data and our result set is ready. Publish the report to the Power BI online and set a schedule to refresh the dataset on a regular basis.

imageimage

This report can be published as a Dashboard report with in your Dynamics 365 CRM instance and users can constantly can validate the data. 

image


Address Validation in PowerApps using Azure Maps Service(Part 2)

Ravi Mukkelli3 Comments

Welcome back readers. As part of our previous post we have created the Azure Maps service which we are going to use in a PowerApps for quick search/validation of address. As part of this blog we are going to create a simple PowerApp which uses the Azure Maps Search API to quickly search for an address. Lets look at the steps to create a Canvas PowerApp.

Login into https://web.powerapps.com using valid credentials

clip_image010_thumb[1]

Once logged-in click to create a Canvas app. On successfully creation, drag three controls a

1. Textbox – used to enter the address for search.

2. Button – used to initiate the address search using azure maps search API.

3. ListBox – used to display the search results.

clip_image012_thumb[1]

Lets add a Microsoft Flow workflow on the button click. This flow can be used to send an request to the Azure Maps service . Lets go through the steps to create the Flow for PowerApp. Click the button and navigate to Action menu to add a Flow

clip_image014_thumb[1]

Click to create a new Flow

clip_image016_thumb[1]

Once the Flow Editor is opened, the PowerApps step is already added as a first step. As part of our example we need to pass the address search string as a parameter to the flow. For that we need use the “Ask in PowerApps” variable which will act as a required parameter which holds the address search string. I have initiated a variable and passed the “Ask in PowerApps” as the default value.

clip_image018_thumb[1]

In the next step I have initialized a second variable called “AddressResults” of type Array to store the search results.

clip_image020_thumb[1]

The next step is to send in a request to the Azure Maps service to search for the address. Using a HTTP step add the required information as shown below. Pass the “AddressText” variable value as a Query parameter value.

clip_image022_thumb[1]

As the results are in JSON format, I am trying to parse the JSON so that it will be easy to extract the data. So I have added the “Parse Json” step to parse the response body. You can build the schema by using “Use sample payload to generate schema” option and Perform a quick query in your browser and past the results to generate the schema. The details are provided in our previous post. Once the schema is generated move onto the next step.

clip_image024_thumb[1]

The next step is to extract the results. As response is an array of address matches, we have to use a loop step. Add the “Apply to Each” step and parse the results. As defined in my previous post, the response has results—>address—>freeformAddress within the JSON. So instead of sending the entire results response, I am extracting only the address information and formatting it into a JSON object to pass it on to the PowerApps. The weird part is PowerApps only supports responses in JSON format. Which is strange. I will try to dig deeper into this, but for now lets stick to the logic to send the required response as JSON. As shown below I am storing the result addresses to the “AddressResults” array variable.

clip_image026_thumb[1]

The next step is format the output as a JSON object and send in the Response as shown below

clip_image028_thumb[1]

Now our Flow is ready to perform a search using Azure Maps search API

clip_image030_thumb[1]

Once done add the newly created flow to the “OnSelect” event of the button.

clip_image032_thumb[1]

Call the flow on click of the button and capture the response within a Global Variable as shown below

clip_image034_thumb[1]

Set the ListBox source to the global variable as shown below

clip_image036_thumb[1]

and we are all set to do a final test.

FinalTest

Address Validation in PowerApps using Azure Maps Service(Part 1)

Ravi Mukkelli1 Comment

Microsoft cloud computing platform “Azure” has become one of the core offerings of Microsoft. In this blog post, I am going to elaborate how to perform address validation using Azure Maps. Azure Maps has many offerings like Search, Maps, Geocoding, Traffic, Routing & Time Zones. We are going to use the Azure Maps Search to validate an address entered within an PowerApp.

The initial step is to create the Azure Map service from Azure portal. Login into Azure Portal I.e., https://portal.azure.com either using trail account or an azure account. Search for “Azure Maps Accounts”

clip_image002

Select the valid Subscription & Resource Group. By default, the “S0” pricing tier is selected. For more details please refer to the azure pricing details Azure-Maps Pricing. Click “Create” to create the mapping services. It may take few minutes for the Azure services to be hosted.

clip_image004

Once the Azure Maps service is successfully provisioned, lets perform a quick check of the address search using the Location API’s. We need the subscription key to perform this search. Click on the Keys and copy the Primary Key

clip_image006

We are going to use Fuzzy Search as part of this example so as defined in the Microsoft Docs Site (https://docs.microsoft.com/en-us/azure/azure-maps/how-to-search-for-address) construct the URL as

https://atlas.microsoft.com/search/address/json?api-version=1.0&subscription-key={subscriptionkey}&countrySet=AUS&typeahead=TRUE&query=4 Freshwater Pl,southbank

Param Description Value
api-version Version of the location services 1.0
subscription-key The primary key of the service from azure <<key>>
countrySet Filter to target specific country. AUS
typeahead If the typeahead flag is set, the query will be interpreted as a partial input and the search will enter predictive mode TRUE
Query A comma separated string of address or latitude followed by longitude

For more details please refer Get Search Fuzzy link.

If successful, it should return list of probable address matches in JSON format as

clip_image008

We have the Azure Maps Search API ready to validate/search addresses. As part of the Part 2 blog post we are going to leverage this service with in an PowerApp to perform a quick address validation.