Use Power BI to analyze the CDS data in Azure Data Lake Storage Gen2


In the previous post, we saw how to export CDS data to Azure Data Lake Storage Gen2.

Here we’d see how to write Power BI reports using that data.

Open the Power BI Desktop, and click on Get data

Select Azure > Azure Data Lake Gen 2 and click on connect.

To get the container URL,

Log in to the Azure portal and navigate to the container and click on Properties and copy the URL.

Replace the blob part in the copied URL with dfs

Below is the format of the URL.

https://accountname.dfs.core.windows.net/containername/

replace the account name and the container name.

In case you get the below error

Refer –

https://nishantrana.me/2020/09/07/error-access-to-the-resource-is-forbidden-while-trying-to-connect-to-azure-data-lake-storage-gen2-using-power-bi-desktop/

Select the CDM Folder View (beta)

Expand the CDM folder and select the entity.

In case if you get the below error

Refer

https://nishantrana.me/2020/09/08/error-we-dont-support-the-option-hierarchicalnavigation-parameter-name-hierarchicalnavigation-when-trying-to-load-table-in-power-bi-desktop-using-azure-data-lake-storage-gen-2-cdm-fo/

Once connected we can then create our Power BI report as shown below.

Check the below posts for creating a Power BI report with Dynamics 365 data as the source

https://nishantrana.me/2018/11/24/power-bi-and-microsoft-dynamics-365/

Hope it helps..

Error – We don’t support the option ‘HierarchicalNavigation’. Parameter name: HierarchicalNavigation when trying to load table in Power BI Desktop using Azure Data Lake Storage Gen 2 CDM Folder view (beta)


While trying to connect to a table within Azure Data Lake Storage Gen2 through CDS Folder View

we got the below error

Users have reported this issue with the August 2020 Update of Power BI Desktop.

As suggested in the forums, downgrading to June 2020 Update fixed the issue for us.

Check out Export CDS data to Azure Data Lake Storage Gen2

Hope it helps..

Error – Access to the resource is forbidden while trying to connect to Azure Data Lake Storage Gen2 using Power BI Desktop


While trying to connect to Azure Data Lake Storage Gen2 through Power BI Desktop we got the below error

Came as surprise cause the user was had the owner role assigned to the container

It turned out we need to assign the Storage Blob Data Reader role to the user.

After assigning the role we were able to connect successfully.

Hope it helps..

Export data from Common Data Service to Azure Data Lake Storage Gen2


Azure Data lake store gen 2 can be described as a large repository of data, structured or unstructured built on top of Azure Blob storage, that is secure (encryption – data at rest), manageable, scalable, cost-effective, easy to integrate with.

  • Export to Data Lake allows for continuous replication of CDS entities to Data Lake Storage Gen2, which involves initial write followed by incremental writes, which can be consumed by Power BI, Azure Data Factory, Azure Data Bricks, and Azure Machine Learning.
  • Replication of standard and custom entities having change tracking enabled and create, update, and delete operations.
  • Any changes in data and metadata are pushed automatically without the need of setting any refresh intervals.

Let us first create a general-purpose V2 storage account to access all of the Azure storage services like blobs, files, etc.

The storage account must be in the same Azure AD tenant.

Login to Azure Portal (with admin account)

https://portal.azure.com/

Search for Storage Accounts.

Here we have used a trial to create the storage account.

Leave the Account kind, Replication, and Blog Access as the default values while creating the storage account.

Before selecting Review + Create, navigate to the Advanced tab and enable the Hierarchical Namespace.

After validation is done and is successful, click on Create to create the storage account.


With the storage account created successfully, navigate to PowerApps select the option Export to data lake.

Select New link to data lake

Specify the storage account created earlier.

Select the entities to be exported to the data lake. Enable change tracking for the entities, as only these entities will be exported.

Clicking on save will link the CDS environment with Azure data lake storage.

It will create the file system in the Azure storage account having a folder for each entity selected.

and will start the initial sync.

we can use the Manage entities option for adding or removing the linked entities.

Inside Azure Portal, we can navigate to the storage account and select the Storage Explorer.

Expand commondataservice-environmentName-org-Id
container to view the details.

The CSV file will contain the data

Here Model.json is the metadata file in the CDM folder

that describes the data in the folders, metadata, and location.

More details-

https://www.bluegranite.com/blog/10-things-to-know-about-azure-data-lake-storage-gen2

Hope it helps.

Column Comparison using FetchXML Builder and SQL 4 CDS


Check it out, the column comparison feature is now added in our favorite XrmToolBox plugins (make sure to update to the latest version)

  • FetchXML Builder

https://jonasr.app/2020/07/fxb-july-2020/#more-4374

  • SQL 4 CDS

https://markcarrington.dev/2020/07/15/column-comparisons-in-fetchxml/

Thanks, @Jonas, and @Mark for incorporating it.

Also, refer –

https://www.itaintboring.com/dynamics-crm/we-can-now-do-column-comparison-with-fetchweb-api/

https://prashantmayur.wordpress.com/2020/07/20/use-column-comparison-in-d365-ce-power-apps-cds-queries/

https://debajmecrm.com/2020/07/16/compare-two-columns-in-queryexpression-in-dynamics-365-cds-sdk/

https://nishantrana.me/2020/07/16/the-new-column-comparison-in-queries-in-dynamics-365-powerapps/

Hope it helps..

New Admin Trial (subscription-based) experience – Dynamics 365


Recently a new Trial (subscription-based) environment option added to the tenants in the Power Platform Admin Center

Earlier we were limited to below options –

These new trial types are created and controlled by tenant administrators, as a result of signing up for a trial subscription and can be added to the existing tenant.

This new environment follows the trial subscription lifecycle (which is typically 30 days) and can be extended from the admin center.

https://admin.microsoft.com/Adminportal/Home#/subscriptions

  • A total of 3 trial environments can be created.
  • Trying to create 4th will give us the below error

Get more details here

https://docs.microsoft.com/en-us/power-platform/admin/trial-environments

Hope it helps..

The new Column comparison in queries in Dynamics 365 / PowerApps


Column Comparison is one of the best features that has been recently added to the product.

Column comparison allows us to compare values of 2 different columns in our query be it Fetch XML, Web API, or through SDK API.

Sharing some of the basic examples that we tried using our favorite  FetchXML Builder

  • If we run the following query “Find all contact which has the first name equal to the last name”

The result –

  • Find all contact where the first name is not equal to last name

The result –

The operators supported are –

Equal LessEqual GreaterEqual
Not Equal LessThan GreaterThan
  • Only 2 columns can be compared.
  • Only compatible attribute types can be compared.
  • E.g. “Find all contact where the first name is equal to address 1 city”

The result

It works as both are string attributes.

  • If we try comparing text field with options set, we will get below error message

  • Two option set can be compared as they represent the same type integer

Both having value 1

  • Comparing date fields

  • We are getting the result

  • For the lookups, comparing created by and owner id

works as expected.

  • For the below record we have the First name and job title as null

And here for below record, we have the same value for First Name and Job title.

On running the below query – find all contact where the first name is equal to the job title.

We get only one result. It ignored the blank (null) record.

Get all the details here

https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/column-comparison

Hope it helps..

Installation Failed – UI flows setup (Setup.Microsoft.PowerAutomate.UIFlow.exe)


While trying to run the UI flows setup we got the below error

It was because we were trying to install it on Windows 10 Home

https://docs.microsoft.com/en-us/power-automate/ui-flows/setup#limitations

Hope it helps..

Append and Merge to combine data from multiple data source in Power Platform dataflows


Let us continue with our previous post where we loaded the data from on-prem SQL DB to CDS using dataflows.

https://nishantrana.me/2020/07/07/load-data-from-sql-on-premise-to-cds-common-data-service-using-power-platform-dataflows-in-power-apps/

Now suppose we have another table (or any other data source) having the contact details, which we would like to append/merge along with our previous data source.

For simplicity, here we have created a copy of the existing table and added a new record with different values.

Edit the existing data flow created earlier.

Click on Get data and select the SQL Server database.

*The other data sources that are available.

Specify the connection details to connect to the database.

Select the new table that we had created.

Next click on Append queries, select Append queries as new, to create a new query.

Specify the primary table and table to append.

The new Query by default named Append will have the data from both the tables combined i.e. appended, which can then be used to load data to the existing contact entity or load to a new entity.

Specify the mapping in the data flow that completes the configuration of the dataflow.

That was about Append queries, now let us take another simple example to see Merge queries in action.

Again let us create one more table that has additional detail (e.g. Mobile Number) for the contact records. Just for simplicity, we are creating a table else it could be any of the supported data sources.

Let us edit the dataflow created earlier, and select Get data to get the data from the SQL Server database.

Here we have selected the new table Contact Details.

This time select Merge queries.

Specify the left table and right table for merge and the join type.

Select the join keys column.

Here we have opted for Inner Join.

The result includes the 1 matching row.

Expand the MyContactDetails column to populate the MobileNumber data.

Now with the data transformed, click on Next and define the mapping using the new merged query.

After a successful refresh, we can see the mobile phone field updated.

Let us update the value again for the mobile number field and run the refresh.

We can see the value updated for the CDS contact entity.

If the tables are already related using foreign key relationship for e.g. Contact and Contact Details, the Get data will load the related table, without any need of merge queries.

PkFk

Refer below articles for more details on Append and Merge in Power Query.

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

https://carinaclaesson.com/2020/03/19/combining-information-from-multiple-data-sources-with-power-platform-dataflows/

Hope it helps..

Load data from SQL On-Premise to CDS (Common Data Service) using Power Platform dataflows


Let us continue our previous post where we created a connection to the below On-Premise SQL Server Database using an On-premises data gateway.

https://nishantrana.me/2020/07/06/configuring-on-premises-data-gateway-to-connect-tosql-server-on-premise-data-source-power-platform/

Here we will use the Power Platform
dataflows to load contact entity in CDS using that same on-prem table.

Sign in to Power Apps

https://make.powerapps.com/home

Navigate to Data – Dataflows and create a new dataflow

Dataflow can store the entities either in Common Data Service or Azure Data Lake for analytical purposes.

Leave the Analytics entities only checkbox unchecked as we will be saving the entities in the Common Data Service.

Next, let us specify the data source. Here we have selected SQL Server Database as the source.

Specify connection details

On specifying the Server and Database details, it will auto-populate the rest of the connection details if already defined.

Select the table and click on Transform data

Transform Data give us the option of further refining the data to match the destination.

Click on Next and specify the mapping.

We can either load data in a new entity

Or load to an existing entity.

Here we have selected contact entity and clicked on Automap option which has mapped FirstName and LastName fields for us. Manually map the email field. Here we have not mapped the ContactID field for now.

Here for Key fields in Field Mapping, it will list down alternate key, if defined for the entity.

Specify the refresh settings. We have kept is as refresh manually.

With refresh completed, let us check the contact entity.

Go to Data – Entities and Data tab, we can see the records added.

Now let us add one more record in the source database and refresh the dataflow manually.

As we had not specified key while defining the data flow, we have all the records created again including the new one.

Now let us define an alternate key for the contact entity.

And edit the data flow to map the ContactID field of the source database to the newly created alternate key.

Define the mapping for the alternate key field.

Also, let us delete the old records that were synced. After refresh is done, we can see the records created

What about the update?

Let us update the record in source and run the refresh

As expected, the record is updated and no duplicate records are created in CDS.

What about delete?

Now let us delete the record in the source and run the refresh

The records are not deleted in the Common Data Service.

What about updates in CDS?

As this is a one-directional flow to load the data from source to destination, any change in CDS will be overridden and will not be synced back to the source SQL Server database on the next refresh run.

Let us update records in CRM, and run the refresh.

As expected, the record in CDS is updated back with source DB.

What about the history and log of the refresh?

Show refresh history option gives us all the details of the refresh run and the failure and success of the records.

More on the data flow capabilities

https://docs.microsoft.com/en-us/data-integration/dataflows/dataflows-integration-overview#dataflow-capabilities-in-power-platform-services

Also, check out the interesting article below

https://carinaclaesson.com/2020/05/11/alm-for-power-platform-dataflows-the-story-begins/

Hope it helps..

Configuring On-Premises data gateway to connect to SQL Server on-premise data source – Power Platform


Let us take a simple example to understand the steps to be performed for configuring an On-premises data gateway.

We have below database in our on-premise SQL Server which we connect to using On-Premise data gateway.

Below are the steps we need to perform to configure it –

Login to Power Apps and navigate to Data – Gateways

https://make.powerapps.com/

Download the On-Premises Data Gateway

Follow the installation wizard to install the on-premises data gateway

https://docs.microsoft.com/en-us/data-integration/gateway/service-gateway-install#minimum-requirements

Sign in with the Office 365 Organization account

Select Register a new gateway on this computer

Specify the name and the recovery key and click on configure

This completes the setup of the gateway.

Within Power Apps, we can see the gateway record created.

Now let us create a connection to our On-Premise SQL Server Database that uses the gateway configured

Navigate to Data – Connections and click on the new connection.

Select the SQL Server connection type, and specify the connection details of the on-prem database.

Select the gateway configured and click on Save, which will test the connection.

On a successful connection, we can see the connection created with status Connected.

Now as we have the connection established with our On-Premise SQL Server database through the gateway, we can use it for in various cloud services like Power BI, Power Apps, Power Automate, Logic Apps, Dataflow, etc.

Get more details here

https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-onprem

Another option to connect to SQL On-Prem database is through Azure Hybrid Connections (this would for Azure App Service)

https://nishantrana.me/2018/02/19/using-azure-hybrid-connections-to-connect-to-sql-on-prem-database-from-azure-webjob/

https://www.slideshare.net/biztalk360/the-hitchhikers-guide-to-hybrid-connectivity-81149063

Hope it helps..

Solved – AADSTS50058: A silent sign-in request was sent but none of the currently signed in user(s) match the requested login hint PowerApps


We might get below error while trying to login to Power Apps

AADSTS50058: A silent sign-in request was sent but none of the currently signed-in user(s) match the requested login hint. Trace ID: b4c0c138-c2d9-46c8-999f-3d10414c2d00 Correlation ID: 60ab6eb7-bef1-44e1-8756-bdc33907e6ef  Timestamp: 2020-07-03 19:11:01Z

Error AADSTS50058  is

UserInformationNotProvided – This means that a user is not signed in. This is a common error that’s expected when a user is unauthenticated and has not yet signed in.
If this error is encouraged in an SSO context where the user has previously signed in, this means that the SSO session was either not found or invalid.
This error may be returned to the application if prompt=none is specified.

https://docs.microsoft.com/en-us/azure/active-directory/develop/reference-aadsts-error-codes

Few solutions to this error are

After the cache is cleared or if we open it in incognito mode, the user is asked to enter the login in again.

And the user can log in to Power Apps successfully.

Check below links for more detail –

https://github.com/AzureAD/azure-activedirectory-library-for-js/issues/323

https://docs.microsoft.com/en-us/azure/active-directory/develop/msal-js-known-issues-ie-edge-browsers#other-workarounds

Hope it helps..

Setup Entity records routing – Omnichannel for Customer Service


In the previous posts, we learn about provisioning, setting up a live chat, and WhatsApp channel.

In this post, we’d see how to set up an Entity records channel.

Through the Entity records channel, we can route cases as well as other entities to the omnichannel agents.

To enable an entity for Routing, enable Activities and Queues for that entity.

Leave the “Automatically move records to the owner’s default queue …” checkbox unchecked for automatic distribution of records to work.

Inside the Omnichannel Administration app, navigate to Channels à Entity Records to create a new entity record channel.

A default workstream will be created automatically, we can also select an existing workstream.

Click on the Routing Rules tab to define the routing rule.

Add a new rule item i.e. for case type as a problem, route the record to Complaints Queue.

Activate the routing rule created.

Let us create a new case of type problem and apply routing to it.

We can see the records added to open work items for agents to pick as defined in the CDS entity workstream.

We can also set the Work distribution mode to Push that would send the notifications to the agent

To automatically route the record we can use Power Automate to define a flow that calls the Apply Routing Rule action.

Refer below blogs to learn in-depth about the Omnichannel

https://neilparkhurst.com/2020/05/29/omnichannel-for-customer-service-collection/

https://thecrm.ninja/omnichannel-for-dynamics-365/

along with Microsoft Docs

https://docs.microsoft.com/en-us/dynamics365/omnichannel/omnichannel-customer-service-guide

Hope it helps..

Setup WhatsApp Channel (Preview) in Omnichannel for Customer Service


In the previous post, we provisioned the Omnichannel for Customer Service and had configured the Chat channel.

In this post, we’d see how to set up the WhatsApp channel (preview)

Within the Omnichannel Administration app, navigate to Channels à WhatsApp and create a new WhatsApp account record.

Provide the required consent

As a first step, let us set up the Twilio sandbox account to be used for WhatsApp channel configuration.

Create Twilio WhatsApp account

https://www.twilio.com/whatsapp

Navigate to console and copy the value of Account SID and AUTH TOKEN

https://www.twilio.com/console

Specify Account SID and Auth token of the Twilio account created in the new WhatsApp channel record.

Saving the record will generate the Twilio inbound URL, copy that URL.

Navigate to Twilio Console à Programmable SMS à WhatsApp and activate the sandbox.

Follow the instructions to configure the sandbox.

On successful confirmation, select Sandbox in the navigation menu and paste the Twilio Inbound URL generated eariler in the “When a message comes in” text box

Back in our WhatsApp channel record, add the sandbox WhatsApp number configured

Specify the Twilio Sandbox WhatsApp number and select the out of the box WhatsApp workstream.

The default WhatsApp workstream

Next click on Validate to check the configuration

With validation successful now we are good to test it.

Send the message to the Twilio sandbox number

The agent will receive the notification from the WhatsApp channel configured.

On accepting the notification, Agent can now communicate with the visitor.

Thus, we saw how seamless it is to configure and get started with WhatsApp channel in Omnichannel for Customer Service.

Refer below blogs to learn in-depth about the Omnichannel

https://neilparkhurst.com/2020/05/29/omnichannel-for-customer-service-collection/

https://thecrm.ninja/omnichannel-for-dynamics-365/

along with Microsoft Docs

https://docs.microsoft.com/en-us/dynamics365/omnichannel/omnichannel-customer-service-guide

Hope it helps..

Admin Center URL(s) in Microsoft Dynamics 365 ( Power Platform)


Listing down Administration Center URL(s) for quick reference à

Admin Center URL
Microsoft 365 Admin Center
https://admin.microsoft.com/

Power Platform Admin Center
https://admin.powerplatform.microsoft.com/

PowerApps Admin Center https://admin.powerapps.com/

Dynamics 365 Admin Center
https://port.<region>.dynamics.com/G/instances/instancePicker.aspx

for e.g.

EMEA
https://port.crm4.dynamics.com/G/instances/instancePicker.aspx

UAE
https://port.crm15.dynamics.com/G/instances/instancePicker.aspx

North America
https://port.crm.dynamics.com/G/instances/instancePicker.aspx

India
https://port.crm8.dynamics.com/G/instances/instancePicker.aspx

Replace <region> with

Dynamics 365 Advanced Settings https://[orgname].[region].dynamics.com/main.aspx?settingsonly=true

Dynamics 365 Home
https://home.dynamics.com/

Power Apps Maker Portal
https://make.powerapps.com

Power Automate Admin Center
https://admin.flow.microsoft.com/

Power BI Admin Portal
https://app.powerbi.com/admin-portal

Azure Active Directory https://aad.portal.azure.com/

Hope it helps..

Provision – Omnichannel for Dynamics 365 Customer Service


For one of our projects we are exploring capabilities of Omnichannel.

Below are the steps for provisioning the Omnichannel for Dynamics 365 Customer Service for quick reference.

First, we need to make sure we have the Customer Service Hub already installed.

https://trials.dynamics.com/

Next, we need to have an active subscription of Chat for Dynamics 365 Customer Service or Dynamics 365 Digital Messaging

The other options

https://docs.microsoft.com/en-us/dynamics365/omnichannel/try-channels

Navigate to Microsoft 365 Admin Center

https://admin.microsoft.com/

Select Purchase services
à
Add-ons

Select Dynamics 365 Customer Service Digital Messaging Add-on trial.

And also assign the add-on license to the users who need to work with Omnichannel Service.

Login to Data access consent URL using the Global Tenant Admin account and give the consent.

We’d be presented with the below message.

Login to Dynamics 365 Administration Center and navigate to Applications Tab and click on manage for Omnichannel for Customer Service.

https://admin.powerplatform.microsoft.com/environments

This opens the Manage environment page for Omnichannel.

Click on Add Environment to add the environment.

Here we can select the environment to which we want omnichannel solutions to be configured.

Click on next and we can then add chat as one of the channels.

Click on Next to similarly setup SMS, Social, and Microsoft Teams as the channels.

Click on Finish to start the installation.

It will show the status as Installed after some time. (took around 1 and half hour in our case)

To manage the channels or delete the omnichannel we can use the same Manage option for Omnichannel for Customer Service in the Applications tab within Dynamics 365 Admin Center.

We can use the Delete button to remove the Omnichannel configuration à

We’d also see the Omnichannel Administration and Omnichannel for Customer Service apps in the list of Dynamics 365 Apps.

Get all the details here

https://docs.microsoft.com/en-us/dynamics365/omnichannel/administrator/omnichannel-provision-license

Hope it helps..

Few points to consider -Status Reason transitions in Dynamics 365


We can specify Status Reason Transitions for Case as well as custom entities. Using the status reason dialog box, we can define (filter) which values are available to be set as next status reason.

Let us take a scenario where we have the following values defined for Status Reason for Active State in a custom entity named Test Entity.

Just to keep it simple we have a just on transition defined i.e. from A1 we can only select A2.

So now when a user creates the Test entity record with A1, the only other option user can select is A2.

Now let us try updating it through backend and set value as A3 for the same record with value A1 as status reason. (Here we have used SQL 4 CDS plugin)

Well, it updates the record with value A3 and doesn’t throw any error.

This would make us think that it only works from the client side and will not work from the server side.

Let us now create a workflow to update the value to A3.

Let us update the subject of the record to trigger the workflow for the below record.


Interestingly we get the error and are not able to set it to A3 as expected.

That was with the real-time workflow, for the asynchronous workflow also we get the error.

Let us give it one more try and update it through service.

Through the service call, we can update it.

So basically, the State transition rules will work as expected from user interface and workflows, however, through service we are still able to override it, which we need to be aware of before using this feature.

Hope it helps..

Differences between Subject and Category Entity in Dynamics 365


Subject entity \ feature has always been an integral part of Dynamics CRM at least since 3.0.

It gives a nice hierarchical way of organising and maintaining information.

To create or update Subject, navigate to Service Management area.

Similar to Subject entity, Category entity was introduced in CRM 2016 Update 1 (Service Pack 1 – On-Premise).

Navigate to Service Management area, to work with Category entity.

  • Can we customize Subject or Category Entity?

We cannot customize Subject. It is available for Case, KB Articles, Product Catalog items and Sales Literature.

We can customize Category Entity.

We can create forms, add fields, views, business rules, dashboards etc.

Can create 1 – N relationship from Category to other entities.

We cannot create N- 1 or N-N relationship.

It has N-N relationship with Knowledge Article entity

  • Is category User Owned or Organization Owned entity?

User Owned

  • How are they rendered inside form?

Subject are rendered as tree structure


Category are rendered as lookup

We can also specify Auto-numbering for Categories

  • What happens if a delete a Subject or category?

Deleting the subject will not delete its child record.

Deleting the category will delete its child record.

  • Can we run advanced find on Subject and Categories?

We can do it on Categories, not on Subject.

  • How does Subject render when used in advanced find filter condition? Tree view?

It renders like a lookup


Few considerations while migrating  Subject records

https://crmchap.co.uk/importing-exporting-subject-records-between-dynamics-365-customer-engagement-environments/

Hope it helps..

Check Dynamics 365 App and Power Platform features availability at different geography


Recently we had to find out what all Dynamics 365 Apps and Power Platform features are available in our UAE region.

To get these details navigate to

https://dynamics.microsoft.com/en-cy/geographic-availability/

and open the report

Below are the different Products and Geography available.

Below is the report filtered for UAE for Power Platform.

For Dynamics 365 App

Hope it helps..

Different ways of getting record count (total) in Dynamics 365


Let us take a scenario, where a data migration package is running, and it either creates or updates (or deletes) a large number of records into Dynamics 365, and we want to get the count of records created/updated/deleted in the last x hour or so.

With views, we are limited to just 5000 records.

One option is to write the console app having the required QueryExpression or FetchXML condition using the Dynamics 365 SDK to get the count.

We can also make use of SSRS reports here.

Create a report using the report wizard, specify the criteria

In the LayOut fields window, specify Count as the summary type for grouping.

Run the report to get the count.

Another option that we have used the most is to use the FetchXML
Builder
plugin to build the query, copy it.

And use it in the FetchXML / View Record Counter plugin of XrmToolBox.

Select the entity, contact, in this case, paste the FetchXML query and click Execute Count.

We will get the count.

Along with FetchXML / View Record Counter we can also use
SQL 4 CDS plugin.

Within the FetchXML builder click on Edit in SQL 4 CDS button.

The result

And now with CDS T-SQL endpoint (preview), we can use SQL Server Management Studio as well to directly write the T-SQL instead of fetch xml

https://nishantrana.me/2020/05/21/setting-up-using-sql-to-query-data-in-dynamics-365-preview/

We can also use the CDS T-SQL endpoint within the SQL 4 CDS Plugin.

https://markcarrington.dev/2020/05/24/sql-4-cds-2-1-0-the-t-sql-edition/

What if we want total record count for an Entity?

Apart from all the methods above,

  • we can use Record Counter XrmToolBox Plugin for that

https://www.xrmtoolbox.com/plugins/AndyPopkin.RecordCounter/

  • or use RetrieveTotalRecordCountRequest

https://dreamingincrm.com/2019/07/22/getting-entity-record-counts/

  • or use Count aggregrate function

https://docs.microsoft.com/en-us/previous-versions/dynamicscrm-2016/developers-guide/gg309565(v=crm.8)?redirectedfrom=MSDN#count

however, it will fail in case of more than 50000 records


‘AggregateQueryRecordLimit exceeded. Cannot perform this operation.’

To increase the limit (on-premise)- https://nishantrana.me/2012/09/06/aggregatequeryrecordlimit-exceeded-cannot-perform-this-operation/

  • Will returntotalrecordcount property of FetchXML help ?


It will be helpful if total records are less than 5000.


Hope it helps..

Using Data Spawner component (SSIS) to generate sample data in Dynamics 365


At times we need to generate sample data for our entities in Dynamics 365 for various reasons, performance testing is one of them.

Data Spawner component which is part of KingswaySoft’s
SSIS Productivity Pack provides us the most efficient way of doing so.

Download the component here –

https://www.kingswaysoft.com/solutions/ssis-data-generation-anonymization-components/data-spawner-component

Let us generate the sample data for Contact Entity.

Add the Data Spawner component to the Data Flow along with the CDS Destination component in the integration service project.

Double click the Data Spawner to open the editor.

Click on Add + button to specify the columns, here we have specified four different columns.

We have kept the name for each of the columns, same as the schema name so that it is easy to map them in CDS Destination.

For the First Name column, we have specified Data Type as nvarchar and Spawn Type as the First Name, which will generate the string similar to first name value.

In Gender property for the First Name column, we can specify either to generate Male or Female first name.

Random will generate both Male and Female first name.

For the email address field, we have selected Spawn Type as email (personal), the other option is email (business).

For our option set field preferred contact method, we have selected data type as an integer and Spawn Type as Custom, which will allow us to specify the list of available values, which is 1 to 5 in our case.

We have specified the total number of records to be generated as 100000.

Lastly, we have set the output of Data Spawner to the CDS Destination component. (use the Map Unmapped fields to auto map the fields as we have set the column name same as the schema name of the attributes)

Execute the package

We can see our sample contact records start getting created.

Read more about Data
Spawner

https://www.kingswaysoft.com/products/ssis-productivity-pack/help-manual/data-generation-and-anonymization/data-spawner

Here are few other articles on SSIS (KingswaySoft)

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

Hope it helps..

Get User’s last logon time in Dynamics 365


We can get the different metrics about the usage of the platform like active user usage, the operation performed, the entity used, plugins and API statics, etc. through Command Data Service Analytics (formerly Organization Insights).

https://admin.powerplatform.microsoft.com/analytics/d365ce

These reports can also be downloaded.

Some of these details can also be captured by enabling Audit user access.

And navigating to Audit Summary View

This view can be filtered to show only the User Access via Web event to get the last logon details for the users.

We can use below FetchXML query to get the same details

<fetch mapping=’logical’ aggregate=’true’ version=’1.0′ >
<entity name=’audit’ >
<attribute name=’createdon’ alias=’LastLoginDate’ aggregate=’max’ />
<filter>
<condition attribute=’operation’ operator=’eq’ value=’4′ />
</filter>
<link-entity name=’systemuser’ from=’systemuserid’ to=’objectid’ alias=’su’ link-type=’inner’ >
<attribute name=’fullname’ alias=’fn’ groupby=’true’ />
<attribute name=’domainname’ alias=’dn’ groupby=’true’ />
<attribute name=’userlicensetype’ alias=’ult’ groupby=’true’ />
<attribute name=’accessmode’ alias=’am’ groupby=’true’ />
<attribute name=’isdisabled’ alias=’id’ groupby=’true’ />
</link-entity>
</entity>
</fetch>

We can also run the following SQL Query (in case of on-premise) to get the details


SELECT su.fullname,
su.domainname,
su.userlicensetype,
su.accessmode,
su.isdisabled,
max(a.createdon) AS LastLoginDate
FROM audit AS a
INNER JOIN
systemuser AS su
ON su.systemuserid = a.objectid
WHERE a.operation = 4
GROUP BY su.fullname, su.domainname, su.userlicensetype, su.accessmode, su.isdisabled;

Now there could be some users who have never accessed the application, to get details of those user we can use the below query


SELECT su.fullname,
su.domainname,
su.userlicensetype,
su.accessmode
FROM systemuser AS su
WHERE su.systemuserid IN (SELECT systemuserid
FROM systemuser
EXCEPT
SELECT DISTINCT objectid
FROM audit
WHERE operation = 4);

We can also use the wonderful User Audit Viewer XrmToolBox Plugin for getting the user audit details.

Can we use the new SQL Data Connection for CDS (preview) to query Audit information ?

We cannot as the Audit Table is not available

Audit

Hope it helps..

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..

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..

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

Using 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..

PCF Control for Email Validation using Email Validator API


The control uses the Email Validator API that checks for fake DNS and regex functions to check email for length and accepted characters.

To use the API, Login to RapidAPI and get the API Key.

Use this key for the API property required by the PCF Control to call the API.

The control will check pass on the email address and based on the response received i.e. isValid true or false, will display the appropriate message just below the field.

The Email Validator API needs to be updated as at times it will show valid domains as invalid. So will not suggest to use this in the production environment.

This control is more of an example of how we can call the APIs, get the response and show the result.

Get the source code here

https://github.com/nishantranacrm/PCFEmailValidatorControl

Hope it helps..

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..