How to setup – Azure Synapse Link – Microsoft Dataverse


Azure Synapse Link (earlier known as Export to Data Lake Service) provides seamless integration of DataVerse with Azure Synapse Analytics, thus making it easy for users to do ad-hoc analysis using the familiar T-SQL with Synapse Studio, build Power BI Reports using Azure Synapse Analytics Connector or use Azure Spark in Azure Synapse for analytics.

As a first step, we need to create the Azure Synapse Workspace.

Login to Azure Portal (https://portal.azure.com/ ) with appropriate roles and create a Synapse workspace.

https://docs.microsoft.com/en-us/azure/synapse-analytics/get-started-create-workspace

Wait for the deployment to be complete. Below are the resources created as part of the deployment.

Login to the maker portal (https://make.powerapps.com/ ) and select the appropriate environment

Click on Azure Synapse Link and check the Connect to your Azure Synapse Analytics workspace (preview) and specify the storage account created in the previous step

Here we have selected the Contact and System User table for export.

On clicking Save, we got this error couple of times, even though the account being used was Owner across the Azure resources. Trying save 3-4 times more, allowed the link to be created.

Click on Go to Azure Synapse Analytics workspace, opens the Azure Synapse Studio

Within Synapse Studio we can see our Dataverse Database and run query.

Similarly, we create a Power BI report with Azure Synapse Analytics (SQL DW) connector.

Copy the Serverless SQL endpoint of the Synapse workspace.

Select the Direct Query option.

https://azure.microsoft.com/en-gb/blog/power-your-business-applications-data-with-analytical-and-predictive-insights/

Hope it helps..

Advertisements

How to – improve data migration performance – SSIS & Azure Data Factory (Dataverse / Dynamics 365)


In one of our projects, we were executing SSIS Packages (KingswaySoft’s Dynamics 365 SSIS Integration Toolkit) under Azure-SSIS Integration Runtime in Azure Data Factory.

Check out –

Deploy and run SSIS Package in Azure Data Factory

Deploy and run SSIS Packages that use KingswaySoft’s SSIS Integration Toolkit on Azure Data Factory.

After trying out different combinations, we eventually settled with batch size as 10 and thread as 15.

https://nishantrana.me/2021/06/08/data-migration-optimum-batch-size-and-threads-for-maximum-throughput-microsoft-dataverse-dynamics-365/

Also, we used multiplexing – running the CRM Destination Component under different application users.

To be precise, 4 in our case and we can increase it get further  improvement in the throughput.

And also based on the recommendation of our Microsoft’s Fast Track Architect we raised a Microsoft ticket to increase the number of web servers allocated from 2 to 3.

Below were our findings,

the earlier run was using batch size as 100 and thread as 20 with the number of servers as 2.

On updating the batch size to 10 and thread as 15 and with the number of servers allocated increased to 3, there was a huge performance gain.

Check the table below – 

The above table is sample run in the sandbox environment, during the final run in production we got the number of servers allocated, increased to 6, gaining further improvement.

Also, check out the below blog post to understand about the affinity cookie and its affect on performance, in case if we doing migration using custom code –

https://markcarrington.dev/2021/05/26/improving-bulk-dataverse-performance-with-enableaffinitycookie/

Hope it helps..

Advertisements

Modern Model App designer (preview) – Dataverse (Dynamics 365)


A new Model App designer (preview) was added to the platform recently.

It follows a page-based authoring approach and resembles the new UCI.

Within the Maker Portal, while create an new Model-Driven App, we can select Modern App designer (preview)

Provide the name and description to the app and click Create.

Click on Play to see how the default app looks like

Now let us add the lead and contact entity and some dashboards to it.

Click on New Page

Select Table based view and form. The other option is Dashboard.

Search and select Lead and Contact and leave it checked – Show in navigation option

This adds the form(s) and view(s) for the table selected.


We can click on form and view and can either let all the forms and views selected by default or can specify specific views and forms.

“No forms have been explicitly selected. All forms will be available by default.”

Similarly, we can add a Dashboard

Here we have selected 2 dashboards and kept the option – Show in navigation checked.

We can update the title of the Dashboards added.

From the navigation menu, we can arrange the components added -i.e. move them up and down and as well remove them.

We can also add new Group and also the Subarea of type either Table or Dashboard.


Save and Publish the change and click on Play.

Our App is ready.

Similarly, we can edit an existing App in the new preview designer.

We can also Switch to classic 

Our Classic App Designer à

Lastly, the Data section in the new designer allows us to edit our entity/ table.

DataSection

Hope it helps..

Advertisements

Data Migration – Optimum batch size and threads for maximum throughput – Microsoft Dataverse (Dynamics 365)


For one of our projects, we were trying to figure out the optimum batch size and threads while using the CDS/CRM Destination component of KingswaySoft.

https://www.kingswaysoft.com/products/ssis-integration-toolkit-for-microsoft-dynamics-365/help-manual/crm/destination

Now with Service Protection API limits in place, to get the maximum throughput the first point to consider is User Multiplexing.

Nicely explained here à

https://powerplatform.se/fast-data-management-in-a-limited-cds-world/

We are using the Conditional Split component to divide the load among the different CDS Destination Components which are running under different application users (using different CDS Connection Manager)

In the below data flow, we have 5 different CDS Destination components, each using a different Connection Manager configured using different users.

Here we took 50000 records as a sample for ContactCreate operation with 3 and 5
CDS Destination Component.

Below were our findings à

As suggested in the article, batch size as 10 and thread as 16 seem to give the best performance.

The trick is here to use as many threads as possible running under different users.

The CDS Destination component of KingswaySoft handles the server-side throttling automatically, it will follow the instruction and wait before trying again the same request.

On trying with 500 as the batch size, we got the error.

[Dynamics CRM Destination [18]] Error: An error occurred with the following error message: KingswaySoft.IntegrationToolkit.DynamicsCrm.CrmServiceException: CRM service call returned an error: The operation has timed out (Error Type / Reason: Timeout, Detailed Message: The operation has timed out) (SSIS Integration Toolkit for Microsoft Dynamics 365, v20.2.0.3083 – DtsDebugHost, 15.0.2000.128)KingswaySoft.IntegrationToolkit.DynamicsCrm.WebAPI.WebApiServiceException : The operation has timed out (Error Type / Reason: Timeout, Detailed Message: The operation has timed out)System.Net.WebException (Status Reason: Timeout): The operation has timed out”.

So basically try out the different combinations to get the maximum throughput.

Also check à

https://www.kingswaysoft.com/products/ssis-integration-toolkit-for-microsoft-dynamics-365/help-manual/crm/destination

I think below will still be relevant but for CRM On-Premise

https://nishantrana.me/2018/12/19/optimum-batch-size-and-thread-while-deleting-records-using-ssis-integration-toolkit-for-microsoft-dynamics-365/

Batch Size and Copy of Paralleism for Azure Data Factory and Microsoft Dataverse

https://nishantrana.me/2021/05/25/write-batch-size-data-integration-unit-and-degree-of-copy-parallelism-in-azure-data-factory-for-dynamics-crm-365-dataset/

More articles on KingswaySoft and Dynamics 365 / Dataverse

https://nishantrana.me/2018/11/26/ssis-and-microsoft-dynamics-365/

Hope it helps..

Advertisements

Synchronize (Bi-Directional) Dynamics 365 CRM Cloud data with SQL Azure using Skyvia’s Synchronization Package


Continuing our previous post where we used the replication package

https://nishantrana.me/2019/11/13/easily-set-up-dynamics-365-ce-crm-replication-incremental-to-azure-sql-sql-on-premise-using-skyvias-data-integration-services/

here we will see how we can use the synchronization service for bi-directional sync.

Below of the prerequisites of synchronization package to perform bi-directional sync –

  • We need to use the created on and modified on fields of the Entity
  • The synchronized tables to have an auto-generated primary key.

So here we need to alter the SQL Azure’s Contact table that was auto-created earlier by the Replication package to have the auto-generated primary key.

Connect to the SQL Azure database using Skyvia’s Query service and perform the alter operation

Or use the Query Editor (preview) of Azure

With necessary changes made, let us create a new Synchronization package

Specify the connection and click on Add Task to create the synchronization task.

We have specified contact entity from Dynamics CRM Source and contact table in SQL Azure which was earlier created for us by the replication package.

It automatically maps all the columns here based on the column name.

We can also manually specify mapping here for both Source To Target and Target to Source fields to be used for sync.

Click on Finish once done with the mapping.

Similarly to replication packages, the synchronization packages can be scheduled.

Click on Save to create the Task.

Click on Run

The run history shows the 220 records synced from Dynamics CRM to SQL Azure.

We can see the 220 records created in our SQL Azure DB

Now let us update a record in SQL Azure and run the package again.

We can see the record updated in Dynamics CRM

Now let us delete a few records from Azure SQL and run the package

We can see the records delete from Dynamics CRM

Similarly, let us delete a couple of records from Dynamics CRM and run the package

We can see the 8 records deleted in the destination SQL Azure

Thus, we saw how easy it is to do bi-directional sync using Skyvia’s Synchronization package.

Hope it helps..

Single Instance / Organization Considerations in Dynamics 365 Customer Engagement / Dynamics CRM


Let us start from the scenario that the enterprise has decided to use Microsoft Dynamics CRM /365 CE as a preferred customer relationship solution for their organization.

For a large enterprise and/or having multiple different departments, the first question that comes up is, do they need to have a single instance or organization or multiple instance or organization. Well, that decision is not that easy but one thing is for sure the decision should be made as early as possible in the project.

Well you could refer to these posts that could help us to make the decision

https://medium.com/capgemini-dynamics-365-team/does-enterprise-scale-dynamics-require-multiple-instances-f1931a443930

https://nishantrana.me/2019/07/23/single-organization-vs-multiple-organization-single-instance-vs-multiple-instance-key-points-to-consider-in-dynamics-365-ce-on-premise/

Now suppose we have made the decision to go with the single instance \ organization, so what are some of the key points we need to consider.

There could be multiple ways of implementing and defining the segregation layer as well as what needs to be shared from data and customization perspective among the different business units, the most important being the security model and the business unit structure being one of the key components of it.

Check out the below Webinar –

http://www.xrmcoaches.com/2017/07/free-webinar-friday-creating-your-dynamics-365-security-model/

also listen to the below podcast to get some guidance around the setting up the security model

http://crmmvppodcast.com/episode-19-setting-up-your-security-architecture-like-a-crm-mvp

In one of our projects, the way we segregated different departments, after defining the appropriate business unit structure, was by having the Business Unit lookup (custom field) in all the common entities that were being shared by the departments and populating that field accordingly, every time record is created either through User Interface or through code and to refer the same field in the customizations (plugin, workflow etc.) as and when applicable.

In the other project, we were referring to the Business Unit lookup field in the System User / Team Entity.

Well there could be multiple ways of doing the same with its own pros and cons.

But what if you have already implemented for one of the units and didn’t consider that the same instance would need to be extended to accommodate the different business unit(s).

Well, then it is time to modify a couple of things to make the Business Unit structure scalable (if it is not).

Let us go through the each of the main components one by one and how they can be segregated among different business units.

Wrong root business unit name?

Set Parent Business Unit lookup field as optional, open the Business Unit record and rename it.

Need to rename the organization?

For Online à

Navigate to Admin Portal, select the instance and click on edit and update the organization name and if required the URL as well as shown below.


For On-Premise à

  • Follow the below steps to update the Display Name

https://blogs.msdn.microsoft.com/darrenliu/2013/09/21/change-crm-organization-display-name/

  • Updating Organization Database Name

https://blogs.technet.microsoft.com/crmbipl/2008/01/14/how-to-change-crm-4-0-organizations-database-name/

Forms

Create separate new Main forms for different business units.

Enable appropriate security roles for the Form.

Views

Fields

Reuse the fields that are common instead of ending up creating a large number of fields.

Field security profiles can also be used if required when certain entity’s records are shared across the business unit and we want to hide the sensitive information.

Business Rules

Business rule can be configured to run on the scope of Form instead of the Entity.

Dashboards

For Dashboards, we can enable the security roles.

Workflows

  • Use scope fields to make sure the workflows fire appropriately.
  • Or put the first step to check for which business unit it is firing (depends on the segregation logic implemented)

Plugins

Have a field in the records to identify to which Business Unit it belongs to and check for this field as the first thing in the plugin or check for the Business Unit of the user under whose context the plugin is getting fired or use the secure \ unsecure configuration etc. it purely depends upon the way the segregation logic has been implemented.

SLAs

Use SLA lookup field to apply the SLA dynamically through plugin or workflow.

https://nishantrana.me/2019/07/24/apply-sla-choosing-the-sla-in-the-sla-lookup-field-for-the-sla-enabled-entities-in-dynamics-365-ce/

Business Process Flow

If there are multiple business process flow, make sure appropriate one is applied

Apply business process flow while creating an entity record.

https://www.powerobjects.com/blog/2019/04/24/business-process-flows-switch-bpf-dynamics-365-javascript/

There would be multiple other key points to be considered apart from the one listed above specific to each implementation; the above points are some of the things that can be taken as guidelines.

Lastly we must consider  creating different apps for different department \ teams\ users and add required solution components to it.

Design custom business apps by using the app designer

Please feel free to add comments and share your thoughts.

Hope it helps..