Month: November 2018

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.