Setting up – Using SQL to query data in Dynamics 365 (Preview)


Recently we were trying out the preview feature of using SQL to query CDS data.

Below are the steps à

To get started,

Download or open SQL Server Management Studio (18.4 or later)

Use Azure Active Directory authentication to connect.

Specify the organization address URL followed by port 5558 in the server name.

If you get the error “TDS protocol endpoint is disabled for this organization”, follow the below steps to enable the TDS i.e. Tabular Data Stream.

Enable the TDS endpoint (preview)

https://docs.microsoft.com/en-us/power-platform/admin/settings-features

enableTDS

enableTDS

or

Download the OrgDBOrgSettingsTool i.e. CRM2016-Tools-KB4046795-ENU-amd64

https://www.microsoft.com/en-us/download/details.aspx?id=56131

Make the following changes in the configuration file of the tool

Highlighted in green

Run the below command (& enter the password for the admin account specified)

Microsoft.Crm.SE.OrgDBOrgSettingsTool Update /u <org-unique-name> EnableTDSEndpoint true

Get the organization unique name from Customization à Developer Resources

After making the above changes, we were able to connect successfully.

This is a definitely one of the most pleasing additions to the product !

Also check out the wonderful XrmToolBox plugin SQL 4 CDS

and

Skyvia Query 

for running SQL query against Dynamics 365.

Hope it helps..

Advertisements

Data Migration in Dynamics 365 – Key considerations


I was recently watching the FastTrack TechTalks on Data Migration.

https://community.dynamics.com/365/b/techtalks/posts/data-migration-january-16-2018

Below are some of the key points from the session à

Customer 360 View is more often than not, the starting point of the data migration discussions. Storing all the data in Dynamics 365 might not be a good idea as it could impact the performance and more importantly, the storage has a cost associated with it.

Learn more about the storage in Power Platform here

https://docs.microsoft.com/en-us/power-platform/admin/whats-new-storage

Important Considerations as part of the Data Migration Strategy

Data Source

  • What is the source of the migration? Is it a single system or multiple systems from which the data needs to be consolidated.
  • The appropriate connectors or technology needed to fetch or extract the data from the source system(s). (SQL, File-based, Web Service, etc.)
  • Need for a staging database for transformation and cleansing of data before migrating it to Dynamics 365.
  • Having a well-defined Dynamics 365 data dictionary and model.

Data Cleansing

  • Removal of the old data.
  • De-duplicate of the data.
  • Checking for null, missing required fields and references.
  • Identify and define the uniqueness of the record.
  • Need for defining Alternate keys

Data Transformation (mapping with Dynamics 365 entities and fields)

  • Transform the data to adapt to the target Dynamics 365 Data model.
  • Format and Range of the fields.
  • Calculate or combination of different fields.
  • Filtering and Enriching of the fields

Mapping OptionSet in KingswaySoft SSIS Integration Toolkit for Dynamics 365

Mapping OptionSet in Scribe Online

Lookup and Cache Transformation component in KingswaySoft SSIS Integration Toolkitfor Dynamics 365

Data Loading

  • Is it one-time migration (plus delta load daily) or n times migration
  • Based on the time it takes, the batch can run over the weekend or after office hours daily.
  • Order of data load – Master \ Configuration Data à Parent records àChild records.
  • Use Create or Update whenever possible instead of Upsert.
  • For Inactive records, first, we need to create those records as active, associate any child records to it if any, before updating the status of the record.
  • Use multithreading and execute multiple requests.
  • Figure out the optimum batch size and thread.

Optimum batch size

For Upsert in KingswaySoft SSIS Integration Toolkit for Dynamics 365

For Delete in KingswaySoft SSIS Integration Toolkit for Dynamics 365

Volume

How many records are to be migrated and how much time will it take.

Storage / Scalability

Do we need all the data to be stored in Dynamics 365, or for Customer 365, can we leverage Power BI reports which could pull data from different data sources?

Do we need all the data for all the entities or last x months?

Compliance and regulations

We need to consider what information is allowed to be stored/accessed by the users in Dynamics 365.

Other key points

  • Use WebAPI for migration.
  • Use Execute Multiple for batch operations.
  • Leverage Application User that provides Server to Server authentication.
  • To update CreatedOn field use OverriddenCreatedOn field.
  • Use Create OnBehalfOf / CallerID for Created By field.
  • Map ModifiedBy and ModifiedOn to custom fields for the historical purpose.
  • Check for the sequence of the auto-number fields in the entities.
  • If possible for improved performance – during migration disable duplicate detection, workflows, plugins, audit, activity feeds, etc.
  • Leverage the same location/region for running the package as the Dynamics 365.
  • For faster date-time conversions set regional settings to UTC for the service account being used.

Check out the below articles

https://kunaltripathy.com/2019/06/04/dynamics-365-data-migration-demystified-part-i/

Hope it helps..

Fixed – An unsecured or incorrectly secured fault was received from the other party. See the inner FaultException for the fault code and detail while trying to connect using XrmToolBox


While trying to create a new connection in XrmToolBox, we got the below error

We had used the Connection Wizard as the connection method

After failing a couple of more times, we realised the mistake.

For the user who is trying to log in, MFA is enabled.

So, instead we opted for the SDK Login control connection method.

Clicking Open Sdk Login Control button opens up the standard login control to connect to Dynamics 365.

Provide username, password, and click on login.

Enter the code (received on registered phone number).

Thus, using the SDK Login Control, we were able to connect successfully.

You can also connect by registering the Azure AD App

https://alphabold.com/connecting-to-mfa-enabled-microsoft-dynamics-365-using-xrmtoolbox/

Check the below article for further details and different ways to connect to Dynamics 365 using XrmToolBox

https://www.xrmtoolbox.com/documentation/for-users/connecting-to-an-organization/

Hope it helps..

Advertisements

Book summary: PowerApps Portals Authentication


I recently read the kindle version of the book PowerApps Portal Authentication by Debajit Dutta and Chandana Kommuri.

The book covers in detail the different authentication methods available for the PowerApps Portals and also provides detailed instructions to set them up.

The different topics included in the book are

  • Local authentication – Self Registrations and Invitations.
  • External Authentication – with Google, Facebook, LinkedIn, Azure Active Directory (B2C and B2B)

This is the first and the only book in the market on PowerApps Portals, and I think it would be very helpful for the Dynamics 365 professionals, as authentication is the basic and most important piece of information needed while configuring PowerApps Portals for the customers.

What I like about the books in general is it gives us all the required information at the same place, instead of being scattered all around out there in the web, and also that information coming from the expert(s) who have been working on it (because of which you’d be buying the book in the first place) adds to its credibility. The book not only covers all the required details (how and why) for setting up the authentication but also presents it simply and concisely. The chapters start with how to register the portals with the external authentication provider, the authentication mechanism available, settings required in portals, etc. There is a specific pattern or consistency about the way chapters are written in the book, which will make it very easy to be referenced when required. Being around 150 pages, the book was also a quick read.

Grab your copy at the link below !

https://www.amazon.in/PowerApps-Portal-Authentication-Debajit-Dutta-ebook/dp/B085X2HL4W

How to – Use Word Template to combine multiple images attached to notes in a single document in Dynamics 365


Recently in one of our projects, we had proposed Dynamics 365 for Phone / Tablet as the device for our sales user. The user will be capturing the image of the customer’s document and will be attaching it in notes, which will then later be moved to the document management system.

Here, one of the requirements was to merge / combine all the images that are attached to the notes for a particular record into a single document.

Here we can make use of Word Template to achieve the same.

Open the entity’s (e.g. lead) record for which we want to create the word template.

  • Click on Download Template to design the word template.

  • Select Note as the related entity.

  • In the word document, go to Developer Tab and click on XML Mapping Pane ribbon button.

  • Insert a table and select its row.

  • Select the appropriate XML part from the drop down and navigate to Lead_Annotation child node in the XML Mapping section.

  • And map it to the row selected as shown below.

  • This is how the table would appear

  • Next add the documentbody à Insert Content Control à Picture to one of its columns to display the images.

  • This is how it would appear
  • We can also add additional fields of notes if needed.

  • Save the template.
  • Go to Settings à Template and upload the template.

  • To test it, let us go open the lead record which has 3 different notes with image attached as shown below.

  • Now let us generate the document based on the template uploaded.

  • The document will show all the images attached to the notes.

  • We can increase the size of the picture and remove the border from the table to make it appear better.

From Mobile –

This slideshow requires JavaScript.

Get more detail on attachments here à

https://nishantrana.me/2020/03/30/attachments-to-notes-in-dynamics-365-for-phone-and-tablet-app/

Generate PDFà

https://nishantrana.me/2020/02/13/save-and-email-pdf-options-added-to-dynamics-365-sales-2020-release-wave-1-plan/

Hope it helps..

Advertisements

Filtering Enhancements to Views in Dynamics 365 – 2020 Release Wave 1


Below are some of the enhancements added to the filtering experience for the views in 2020 Release Wave 1.

Previously to sort the records we had to click on either the sort icon or the header to sort the records.

With the 2020 Release Wave 1 clicking on the header or sort or filter symbol with open the below screen with the option of both sort and filter. Also if we see, the options to sort and filter have moved closer to the header text of the column instead of right-aligned in the previous version.

And for filtering also we had to specifically click on the filter icon earlier.

For the text field below filter options will be displayed earlier

For Wave 1 Release 2020, we need to select Filter By

And below are the filter options available for the text fields.

The most helpful here would be the addition of Contains data and Does not contain data filter.

For the Datetime field prior to Wave 1 Release 2020, we had the below very limited filter options available.

Now in Wave 1 Release 2020, we have the following filter options specific to DateTime field added

along with updated Calendar View for selecting the date

Earlier à

Now à

For Optionset fields

Earlier à

Now à

Instead of having all the options selected now it comes as unselected and to apply the filter we need to select the specific value and then click on Apply.

And select Clear Filter to clear the filtering

For Lookups à

Earlier

Now à

  • For Numeric Field we now have the contains
    data and does not contain data filter options added.

These enhancements specific to filtering will definitely add to more productivity for the users.

Check other blog posts on

Release 2020 Wave 1: https://nishantrana.me/2020-release-wave-1/

Release 2019 Wave 2: https://nishantrana.me/2019/12/31/dynamics-365-2019-release-wave-2-blog-posts/

Hope it helps..

%d bloggers like this: