Plugin on Retrieve and Retrieve Multiple – How bad is it?

I have managed to be in the Dynamics CRM/365 world for over 7 years without having to write a single plugin on Retrieve and Retrieve Multiple. The recommendation that I give is to stay away from those. The reason is simple, it sounds horrible from a performance standpoint, and even people from Microsoft have recommended against it in many scenarios. Faced with an issue recently where we had to really consider it, I did some research and testing to try to measure the impact of such plugins on system performance. This article provides some background as to why we recommend against these types of plugins, and it also provides some of our finding after we tested for performance.

Why are Plugins on Retrieve Multiple scary?

When looking at the event execution pipeline for Dynamics CRM/365, we need to consider that there are a lot of steps involved as part of every CRM transactions. To do anything, we need to go through the CRM web service APIs which will start the chain of events in the pipeline (pre-validate, pre-event, core action/database access and then post-event).


This means that in general, it is a good practice to build everything for optimized performance to give your user base a good experience. It’s not like having a custom database where you can create store procedures easily, add triggers and so on, taking advantage of the SQL Server features and infrastructure.

Now, back to Retrieve and Retrieve Multiple plugins.

Retrieve Multiple: Think about it this way, you retrieve a list of 200 accounts, your plugin on Retrieve Multiple fires once and gives you the list of accounts being returned to the screen with all columns being retrieved. For each of these row, you do some type of operation. It doesn’t too sound good, does it?

Retrieve: You double click on an account from a list view. As the account columns are being retrieved to display the account form on the screen, you plugin on Retrieve fires and gives you the account object. At that point you can modify the content of the columns being retured as required before they are returned to the screen for the user. This really doesn’t sound too bad.

Some Findings on the impact on Performance

To provide some context into what we were trying to do, I wrote about multi-language lookup in a previous blog post. One of the solution that we have considered for one of our client is to use a plugin on Retrieve and Retrieve Multiple in order to change the value of the lookup primary fields in order to display a value in the user’s current language. The method we used is similar to what Aileen Gusni does here and almost identical to what Scott Durow does here.

We store a Region in a custom Entity. Accounts have a lookup that indicates its region.

Scenario 1:

The Region’s Primary Field contains a concatenation of the English and French region names with a relatively safe separator (we use “|”). When you load the list view of accounts, we have a plugin on Retrieve Multiple that looks at the columns being returned. If the Region column is returned, we retrieve the user’s language, we split the name of the region with the separator (the name is available in the entity reference) and we replace its value in the target object by the region name in the user’s language. The plugin on Retrieve does the same operation on the single role being retrieved.

The average execution time for the Retrieve Multiple plugin when loading 150 rows was 15.43 milliseconds so 0.01543 seconds.
The average execution time for the Retrieve plugin to load one rows was too little for the system to return a value (we got 0 milliseconds every time).

Scenario 2:

The Region entity has English Name and French Name attributes. When you load the list view of accounts, we have a plugin on retrieve multiple that looks at the columns being returned. If the Region column is returned, we retrieve the user’s language, we then retrieve the English or French name from the Region entity and we replace its value in the target object by the region name in the user’s language. The plugin on Retrieve does the same operation on the single role being retrieved.

The average execution time for the Retrieve Multiple plugin to load 150 rows was 1003.94 milliseconds so 1.00394 seconds.
The average execution time for the Retrieve plugin to load one rows was 16.02 milliseconds so 0.01602 seconds.

What should you read into this?

While these numbers don’t look too crazy at all, especially in the first scenario, there are a lot of factors to take into consideration that are not really showing here and that will vary in almost any scenario.

  • What is the infrastructure you are running on? The faster your severs and networks, the better the performance will be.
  • What you do in these plugins matters a great deal. You should avoid or limit the number of read/writes to the database during the execution of those plugins.
  • Our tests were made with a low level of users in the system, it is critical to scale up and see what these numbers look like at peak time of your system.

With all this said, I still recommend against it. Use with a great deal of caution! If/when possible, use calculated fields instead of writing plugins on these messages. This will also keep you away from limitation such as this one.

Hope this helps!

Dealing with Multi-Language Lookups

Very often, CRM entities are used as reference data tables, for example to keep a list of countries, states or provinces or other business/industry specific data. For some businesses I have seen entities to keep a list of distributors, list of business roles, regions to only name a few. When used that way, CRM entities provide a lot of great features that cannot easily be met with option sets such as the ability to manage large reference tables, lookup search, lookup filtering, ease of adding/editing/modifying data by power users without a deployment.

One of the issues with using CRM entities for reference data is that they is no concept of multi-language lookup in the Dynamics 365 / CRM platform. Lookups will always display the value of the primary field by default. This can cause an issue in places where you must have a fully multilingual application. In this article, I provide a few possible solutions to solve this issue.

As an example, we’ll use the context of a task for which we need to track its type. The list of the available task activity types is stored as records in an entity called “Task Activity“. The Task entity has a lookup to the Task Activity entity. The information needs to be stored in English and French


1 – Task Form with Lookup to Task Activity


2 – List of Task Activities

Resolution Option 1 – Concatenate multiple languages in one field with a separator

You will be disappointed, this is not a fancy solution. In the Task Activity entity, we have one field for the name in both languages and use concatenate both field values in the primary field using a workflow or plugin.

  • Name English (Single line of text – 100)
  • Name French (Single line of text – 100)
  • Name (Single line of text – 203) – read only for users, populated with “Name English | French Name”


3 – Task Activity Form

This is the most common approach that I have seen when the number of languages is small (2 languages). This has a disadvantage of sometimes creating long name values that are not fully visible in the views and on the forms, but it’s cheap and you keep the ability to search using lookup, and display the columns in French or English the views if you need to.

Resolution Option 2 – Plugins on Retrieve & Retrieve Multiple

This solution is a little more interesting, but risky. In the Task Activity, we still have one field for the name in both languages and we still concatenate both field values in the primary field.

  • Name English (Single line of text – 100)
  • Name French (Single line of text – 100)
  • Name (Single line of text – 203) – read only for users, populated with “Name English | French Name”

The principle is to write plugins on the Retrieve and Retrieve Multiple events of the Task Activity. In both of these plugins, you need to retrieve the connected user’s language (query the user settings table), and then replace the text being returned in the Name field by the value in the user’s language. This value can be obtained by querying the task activity record and retrieving the name in French or English, or simply splitting the Name field (primary field) with the separator and return the part in the desired user’s language. One plugin will handle the lookup column in list views (Retrieve Multiple), and the other will handle the form views (Retrieve).

Generally, it is not recommended to write plugins on the Retrieve and Retrieve Multiple events for performance reason. If the operations executed in those plugins are simple and optimized, it might be a viable solution. This is a solution that can scale well if you are dealing with more than 2 languages because in all cases, users see only the value in their selected language and the multiple values are transparent to them.

Stay tuned, I have an upcoming post where I provide some metrics about the impact of a plugin on Retrieve and Retrieve Multiple on performance.

Resolution Option 3 – Automated mapping of Option Set with Reference Entity Records

This is a bit of a complex solution, by far the fanciest. The idea is to use an option set instead of a lookup to reference the task activities, but the option set values will be “controlled” with records from the Task Activity CRM entity. It goes like this:

  1. Create a global option set named Task Activity
  2. Create your Task Activity entity with a primary “Name” field. Put the name of task activities in the primary language (language of the CRM org)
  3. When a record is created in the Task Activity entity, use a plugin to create an option set value in the global option set
  4. When a record is updated in the Task Activity entity, use a plugin to update the corresponding option set value in the global option set
  5. When a Task Activity record is deleted/deactivated, use a plugin to update the corresponding global option set value by putting brackets around the name for example, and also pushing the value to the bottom of the option set list
  6. You can then get the CRM Translation file and get the Task Activities values translated as part of the global solution.


4 – Records to Option Set Value mapping

The outcome is that for entities that need to capture the task activity information, there will be an option set field as opposed to the lookup field:


5 – Task Form with Option Set

While this has low impact on performance and leverages the out of the box language-aware option sets, it requires a serious time investment to define the development framework for each entity that required this mechanism to be implemented. It also requires a translator to update the CRM translation on a regular basis (every time there is a deployment). This is a fancy solution that requires a lot of coding and maintenance. In addition to that, you lose the ability to search and filter the content easily like you would do with lookups. You should make sure yours lists are not very long if you don’t want to end up with Option Set lists that are very long which will result in poor user experience.

I have rarely seen companies making such large investments to circumvent the lack of multi-language lookup in CRM. This is usually seen when there are laws that force you to have a system running fully in multiple languages.

Resolution Option 4 – Custom Screen for Lookup views & search

This is another fancy one for which I unfortunately don’t have any screenshot. We want to leverage option sets to “overwrite” lookup values and selection process with the following steps:

  1. Create a set of standardized Web Resources for Lookup Display and lookup value selection
  2. Display the web resources on the CRM Forms and hide the lookup controls
  3. The web resources will have built-in logic to display the value in the language of the current user, as well as a mechanism to allow searches (could be auto-complete based)

Writing a standard web resource control for that purpose is relatively simple. However, you might have additional work to do if you want to take advantage of filtering based on other fields, or custom filters. Also, this solves the issue on the form in the sense that you will see the values in the right language on the form, but for list views, reports etc. the problem will still exist so you’ll need to find another solution there.

Closing Thoughts

As you can see there is no perfect solution. Each organization has to decide the level of investment and risk they want to take to make sure they have multi-language option sets. Living in Canada where we have two official languages, this is a challenge that we often see in public sector implementations because having fully bilingual system is mandated by law. There are very few countries where this is the case (which is probably why Microsoft has not made investments in this area). Most private sector companies will usually impose a primary language for the entire organization.

Hope this helps!

Creating readable unique identifiers for your CRM records

A common requirement in CRM implementations is the need to have readable identification numbers auto-generated for custom attributes. There are various ways of achieving this. This article provides a few options to do that.  

Let’s use one scenario as a context. Let’s say we are building a Grants Management application and we are tracking scholarship applications in Dynamics CRM. We have an application entity which contains an Application Id field. For business reasons, we need to have a unique identifier to refer to the applications. What are out options? For starters, let’s look at some of the elements to consider when looking for an auto-numbering solution in Dynamics CRM?

  • Are you using an entity that is auto-number enabled? (e.g. Case, Quotes, Orders etc.)
  • Do you need the numbers to be sequential?
  • Do you have potential race conditions? (i.e. can the event assigning the auto-number be triggered by multiple processes simultaneously?)
  • Do the auto-numbers need to be human readable?
  • What is the format required? (numeric only, alpha-numeric, letters only, prefix, suffix etc.)
  • Can you and/or do you want to write code to generate these numbers or do you want an simple configurable solution?

Generate an unique number based on the current Date Time

Of course, this required a plugin on an event that will generate the application Id. From a technical standpoint, there are a few algorithms out there that will give you an indication as to how this can be done. The full date time and the “tick” should be use to generate the number to ensure uniqueness. But even with that, there is a very very very small chance of having race conditions depending on your application and on your (bad) luck. Also, this will not allow your to use sequential numbers.

Create a record of an auto-numbering CRM entity

If you are *not* using one of the CRM auto-numbering enabled entities (for example, Campaign), this solution is have a plugin (or a Workflow) on your triggering event that will create a Campaign record. This campaign will automatically be assigned a unique number based on your CRM configuration (image below). You can re-use that number as the unique ID for your record and delete the Campaign record that was created. While this is a simple method that can be implemented without coding, the numbers are not really sequential, “garbage” records are created at a cost (create/delete transactions time, what happens if you want/need to use that entity in the future?..). To be honest, it feels like a quick-and-dirty way of doing things, but it works.

The advantage here is that because the auto-numbers are platform generated, so you do not have to worry about race conditions in theory.


Use a model based on CRM Synchronous Workflow + Custom Auto-Number entity

I will not go too much into details about this one. There are lots of articles on the topic. A few “how-to” articles for reference:

Bottom line, this is a great solution if you cannot write any code and don’t want to spend any money for something that should be standard in the platform (yes, I’m ranting, can’t help it). That being said, it you have race conditions, I’m sure you can have a few duplicated numbers with this solution.

Using a 3rd Party Tool

There are plenty of options out there. Some are more flexible than others. Some explicitly guarantee uniqueness, some don’t really mention it. Some are free, some are not.

These are just a few, a research will allow you find more of these and decide the one that fits your needs the best.

Build your own solution

At this point, you can do it anyway you want.

  • If you have access to an external database, a CRM plugin that create a record in the database with an identity column will guarantee to always have unique and sequential numbers. This comes at a cost obviously (external database dependency, speed)
  • There is also a smart way of doing robust auto-numbering suggested by Ken Heiman of Green Beacon here. Good read, smart idea.
  • Some enterprises already have Auto-Numbering web services, you can simply consume that web service from a CRM plugin as well (same cost as database, dependency, speed)
  • I have also seen the idea of using the Guid as a unique identifier, by copying the value into a text field with a plugin. I don’t think it’s readable, but definitely unique.

 

It had been too long. Happy CRM’ing ! 

Custom User Interface in CRM – HTML5/JavaScript or ASP.NET Web application?

Lots of times on CRM projects, we are required to build and display custom screens inside of Dynamics CRM. There are a lot of factors that come into play in the choice how we build these custom user interfaces. It is a very key decision to make because it can sets the standard technologies and tools that you use for custom screens on project (among other things). I thought I’d share some key points to consider in your decision making process to determine what technologies and tools to use.

The need for custom user interface

First, it is important to know and understand when it is required to build custom UI as part of your CRM solution implementation. As a wrote a few years ago, we sometimes find ourselves in situations where we can come up with a complex data model and we feel like the out of the box UI capabilities won’t be user friendly enough to drive user adoption. Situations like that create the need to build more user friendly interfaces to simply make people’s lives easier when they start using the CRM/XRM application. Some examples are

  • Custom calendar view for timekeeping
  • Tree view to display a complex hierarchy
  • Interactive Bing or Google maps views of CRM data
  • Editable grids
  • Complex grids with aggregates, grouping functionalities
  • Integrated views to other systems

And the list goes on. The needs are very different depending on the nature of the enterprise looking to implement a CRM/XRM solution.

Most common technology choices

From experience, I have seen a lot of custom screens built using the following tools/technologies:

  • Silverlight
  • ASP.NET / ASP.NET MVC
  • HTML/HTML5 + JavaScript + CSS

The screens are usually displayed in web resources or in an IFRAMES inside CRM on forms or as standalone modules (open from a button, display in sitemap area). I will not cover Silverlight since Microsoft announced its end of life and continued support for a few additional years, plus the world has started to move on.

ASP.NET / ASP.NET MVC

  • Pros
    • Flexible server side business logic
    • Easily integration with external systems (web services, databases etc.)
    • Can be very quick to develop depending on the requirements, expertise and accelerators (advanced controls provided by Telerik, Infragistics or others can speed up development time significantly)
    • Develop with a familiar programming language (C# + Visual Studio) – this of course depends on your development background
  • Cons
    • Requires the deployment and maintenance of a hosted web application
    • You have to deal with the weight of ASP.NET (ViewState, Postbacks etc.)
    • Not a great fit with CRM Online and cloud offerings in general (cloud deployment, cross origin web sites, integrations etc)
    • Required additional configuration in CRM + JavaScript to set the IFRAME’s sources (URL)
    • Effort for developing custom web applications is often under-evaluated

HMTL(5)/JavaScript

  • Pros
    • Usually, a much lighter solution and if done properly, custom UI components can be very responsive and provide great user experience
    • All components are in CRM Web Resources (html pages, JavaScript files, images…)
    • Custom screens are CRM Solution aware and there is no need for deploying and maintaining an additional web site on a web server
    • A definite plus for cloud based Dynamics CRM implementations
    • Can be very quick to develop depending on the requirements, expertise and accelerators (Kendo UI controls or others can speed up development time significantly; other frameworks like Knockout JS, Bootstrap can also be useful)
    • Leverage trending technologies and tools
    • Use any IDE you want (no obligation to use Visual Studio, even if you should J)
  • Cons
    • Possibly additional complexity if you need to display information from an external system
    • If not structured properly, JavaScript code can become massive and hard to navigate/maintain
    • For non-web developers, might be a steep learning curve
    • Need to establish good project structure and standards (e.g. use TypeScript or Script Sharp)

What else to consider?

There are other factors to take into account, some being specific to each organization. A few examples:

  • What is the expertise or your resource pool?
  • Do we have people to build and maintain what we deploy?
  • Are we in CRM Online or planning to move to CRM online?
  • Are we, as a company, trying to get into HTML/JavaScript more?

This is good food for thoughts for starters. If you want more guidance, feel free to reach out to us.

Using Full-Text Search to improve Dynamics CRM search experience

Consider the following scenario: You need to perform email searches in your CRM and your search needs to look at the content (body) of your email messages. When you start having a lot of emails in your CRM (500k+ in my case), searches become extremely slow and you need to start to look at indexes or other solutions to make the search faster and keep your users happy. This post describes a solution that leverage a new CRM feature in CRM 2015 Update 0.1 (On Premise) : Full-text search.

Enabling Search on email body

This is the easy part. If you are having this issue, you have probably done this already. To enable the ability to search on the email body, you need to add the “Description” field as a “Find Column” in the Quick Find view of the Email entity, as shown in the screenshot below. After completing that step, save the view and publish the entity to make the change available in the system.

image

Searching through emails

Now that we’ve configured the search column, we can do a quick search to get our emails by using the search box on the activity list view view or by using the “Global Search” on the top right of the top navigation bar, next to the “Quick Create” button. Note that emails are not enabled in the “Global Search” by default so you’d have to add them (not required).

If you have a few emails in your system, results are going to come back quickly. In the system that I tested against, I had about 700.000 emails in CRM so the search result took on average just over 30 seconds to return results (and I consider this good, because I’m running on SSD drives). That being said, in real life, 30 seconds or more of waiting time is enough to make users think that the system is frozen, or to just stop trusting in the application, especially if they rely heavily on this type of search.

Making search return results faster

If you are using CRM 2015 On Premise, you are in luck. In CRM 2015 Update 0.1, the product team introduced the ability to enable full-text search for Quick Finds queries. To enable it, go do Settings –> General –> Set up Quick Find. There you’ll see the option to “Enable full-text search for Quick Find.

image

Greater details about the feature can be found here and you can also take a look at this video on the Microsoft Dynamics YouTube Channel.

Important points to keep into consideration before enabling full-text search:

  • Full-text indexing for Quick Find uses SQL Server full-text indexing
  • It can take up to 24 hours for the system to enable or disable full-text search, or add and remove find columns (maintenance job has to run on the server and make the adjustments specified in the CRM configuration)
  • As a bonus, you should also activate the Org Settings “EnableQuickFindOptimization” and “EnableRetrieveMultipleOptimization” in order to get the best results. Thanks to Jean-François Cantin for that indication. If you don’t know how to update the Org Settings, here is a reference.

After waiting long enough time for the emails to be indexed, the email searches started to return results within 5 seconds. Drastic improvement. The same solution was implemented into the client’s database and it seems to have made a huge improvement. 

Have fun.

Leverage the power of Power BI with Dynamics CRM data

Yesterday I attended a presentation from felow MVP Sophie Marchand about Power BI. I had previous exposure to Power BI when it became generally available for free as part of the Office 365 suite. The content of the presentation was around multiple areas in Power BI, so I thought I’d revisit some of them and put some thought into how we could leverage the power of the Business Intelligence platform to manipulate and play with Dynamics CRM data.

Connecting Dynamics CRM Online to Power BI (web)

This area is well documented. When you create or log on to you Power BI account, you can get Dynamics CRM Online data by going to the “Get Data” section, selecting “Services” and finding the Microsoft Dynamics CRM content pack.

image

Once you click on Connect, you will need to provide your Dynamics CRM Online OData Service URL (make sure you don’t leave a trailing slash in the end).

image

Once this process is completed, Power BI will connect to your Dynamics CRM Online Organization, get a set of data and build a Sales Manager Dashboard. That dashboard is pre-configured as part of the native integration between CRM Online and Power BI. Keep in mind that you can also connect to CRM Online from Power BI for Desktop. The way the data is structured is a bit different and you don’t get the Sales Manager Dashboard by default. That being said, it bring the data in using the OData Service and gives you the ability to create your own visualizations and reports. Let’s take it a step further and see what options you have if you need to manipulate data from custom entities and create visuals, dashboards that are useful for your business.

Custom Dynamics CRM data sources for Power BI

If you want to leverage Power BI, which you should in many ways, there are other ways to connect it to your CRM data. Here are some of the options that you have.

Extract Dynamics CRM in Excel Spreadsheets and Import the files to Power BI

Power BI (web)

image

Power BI for Desktop

image

For CRM On-Premise organizations, using Power BI for Desktop, you can simply connect to your CRM Database start creating for dimensions, cubes and reports from there. If you need more information on how to get started with Power BI for Desktop, make sure you check out Microsoft’s article here.

image

Worth noting, you can export your Power BI for Desktop content and upload it to the Power BI web platform. This gives you the ability to view the dashboards online, question it differently and also share it with people in your organization. In the Dynamics CRM and Microsoft ecosystem, it has never been easier to build reports and intelligence around your operational data. Enjoy!

Dealing with record level access restriction with Dynamics CRM

CRM works extremely well with giving users access to records based on a different set of security configuration tools : cumulative security roles with a “most permissive win” mechanism, business units, team memberships, access teams, hierarchy and so on. One thing it does not do well is restricting access to specific record. Imagine the following scenario:

  • Company ABC uses Microsoft Dynamics CRM to manage its cases
  • An employee leaves company XYZ to join ABC
  • After joining ABC, the new employee is given access to Dynamics CRM and has access to ongoing and past cases, say in his/her region
  • Company ABC has open cases against firm XYZ. The employee should not be able to see those cases because it represents a conflict of interest (e.g. he/she might give insights to his/her old friends at XYZ)

The following image provides an illustration of what we are trying to accomplish.

image

If you have worked with Dynamics CRM and know its security model, you can already read between the lines here. There are no easy ways to make this happen. This article provides a possible solution for this requirement based on a recent experience on a large CRM implementation.

Ruling out Team Membership & Ownership

In our scenario, members of a team or a business unit get access to all records  owned by their team or business unit. If we follow basic team/BU record ownership, the problem in this case is that once a record needs to be isolated from one individual member of the team, one of two things needs to happen:

  1. The record owner has to be changed (it can no longer be the team everyone is a member of) and the record needs to be shared with users who can still see it, but not with the individual that is getting the restriction applied OR
  2. The individual who is getting a restriction applied has to be removed from the team and all the records owned by his/her team need to be shared with him/her except the one he/she is restricted to see.  This technique implies heavy maintenance since the user will need to access new cases created for the team he/she used to be a member of.

Sharing in both cases can be done via the record share functionality, access team or any other mechanism that suits the business need. It should also be automated if there is a large number of records to share. You also need to save the information about the user and its restriction on a separate entity to be able to backtrack and understand why the records/ownership and so on have been modified.

Why we didn’t like the “Sharing” functionality

Using the out of the box sharing of record can cause problems in the long run. The PrincipalObjectAccess (POA) table gets bigger and bigger, causes performance issues and has to be cleaned up on a regular basis. In addition to that, the more access is controlled by sharing, the more you run the risk of having performance issues because of the complexity overhead in system lookups when it needs to display a record or list of records to users.

A solution using “Access Teams”

We decided to go with a design centered around Access Teams. The “Case” entity gets an “Access Team Template.” When a user is created and added to his/her group (team or business unit), we start a process to insert the user in the Access Team of each case related to his/her business unit. In order to restrict access to a specific case for a user, we can simply remove the user from the case’s Access Team.  For tracking purposes, we created a “Security Exception” entity that is created and has a relationship with a case and a user and an additional attribute that indicate the type and reason for the “Restriction”. To apply the access restriction on a case for a user (i.e. remove user from access team), we create a Security Exception record. We link the target case and user, and specify the restriction type. After the Security Exception record is created, a plugin is fired to perform validation and to remove the user from the case’s access team.

One of the reasons with went with that model was that we didn’t want to have a base security model that gets “broken” every time there a single person with restricted access, by changing record ownership or removing user from the team he/she is supposed to be a member of. With access teams, the record ownership remains the same, and users also remain on their team at all times which makes everybody happy and maintenance easier. The downside is that when a user is created, we have to look for all the cases in his/her business unit and manually (with code) add the user to each access team. Same when a case is created, users member of the case’s business unit have to be added to the case’s access team manually (also with code). That is the comprise we had to make.

As far as performance goes, the end results remain to be seen in the long run. Our system will have between 2000 and 3000 users when we complete our rollout. It will deal with over 1 million cases across multiple teams. Preliminary performance tests results look promising. I will repost if we get into any issue as it relates to performance.

It’s been a while! This feels great Smile  – Happy CRM’ing !