How to recreate Filtered views in Dynamics 365 (code sample)

For all of us ‘CRM’ users who were used to writing SQL reports for CRM On Prem, one of the major features we’ve lost in moving to Online was the lack of SQL access. In December 2016, Microsoft gave us a fantastic tool to replicate the data to an Azure SQL Database (details on TechNet). With this tool you’re able to get the data from the cloud to a SQL database you have direct access to and everything is right in the world – except it’s really slow and it’s tough to write your SQL reports.

What’s the problem?

There are two key features included in the On Prem database that aren’t available when you replicate the data:

  1. Indexes aren’t automatically created so your queries are slow
  2. There are no Filtered views, so getting the display values for your report is a pain

Missing indexes are easy – when you’re writing queries that are underperforming, SQL will generate index suggestions. I used https://blog.sqlauthority.com/2011/01/03/sql-server-2008-missing-index-script-download/ and it got my contact query (1 million+ records) from 55 minutes to less than a second.

The big problem is that you don’t have Filtered views. In the On Prem, filtered views handled security for the user running the report, and it also provided extra columns to make reporting easier. Since Option Set fields are stored as an integer, you need to take extra steps if you want to display the label of the field.

What’s the solution?

I solved the display problem for one of my clients by writing code which mimics the Filtered views by generating views with extra columns for each of the labels. Here’s what you end up with:

  • The ‘contact’ table has a column ‘donotfax’ with value ‘0’
  • The ‘displaycontact’ view has a column ‘donotfax’ with value ‘0’, and ‘donotfax_display’ with value ‘Allow’.

Not only do these views give you all the original data with more details, but they’re just as fast querying the tables directly (at least, I can’t notice a difference). I spend a significant amount of time tailoring the views to get the best performance (it took me a while to track down a performance issue when you have too many join conditions).

How does the code work?

It’s pretty straightforward:

  1. Query the database to see what tables are there and match them up to entities in Dynamics 365 to determine the entities that need a view.
  2. Query for the existing views to see which ones need to be dropped and recreated.
  3. For each table that needs a view, find all the attributes that should have a display field (option set, Boolean, statecode, statuscode), and build up a column to get back the right value.

When do I need to run the code?

When you change the labels in Dynamics 365, the label is automatically updated in the database and your view will reflect the change immediately. The only time you need to regenerate the views is if you add new entities to the synchronization (requires a new view), or if you add new option set or Boolean fields to Dynamics 365 (the view needs a new column).

That’s it!

Now when you’re writing SQL against your replication database you’ll be able to query against the “displaycontact” and get back “donotfax_display” without having to write any other joins!

protected void GenerateViews(string sqlConnectionString, string crmConnectionString)
{
 // Adjust these to have different view or columns names
 string displayViewPrefix = "display";
 string displayViewSuffix = String.Empty;
 string displayFieldPrefix = String.Empty;
 string displayFieldSuffix = "_display";

 this.SqlConnectionString = sqlConnectionString;
 // Testing SQL connection
 string sqlError = this.ValidateSqlString();
 if (!String.IsNullOrEmpty(sqlError))
 {
   throw new ConfigurationErrorsException("Connection String error: " + sqlError);
 }


 CrmServiceClient service = new CrmServiceClient(crmConnectionString);

 // Retrieve existing tables from the replication database
 DataTable databaseTables = this.ExecuteQuery("SELECT [name] FROM sys.tables order by name");
 List existingTable = new List();
 for (int i = 0; i < databaseTables.Rows.Count; i++)
 {
   existingTable.Add(databaseTables.Rows[i][0].ToString());
 }

 // Retrieve the existing views
 DataTable databaseViews = this.ExecuteQuery($"SELECT [name] FROM sys.views where name like '{displayViewPrefix}%'");
 List views = new List();
 for (int i = 0; i < databaseViews.Rows.Count; i++)  {    views.Add(databaseViews.Rows[i][0].ToString());  }  // Retrieve the entities   RetrieveAllEntitiesRequest request = new RetrieveAllEntitiesRequest() { EntityFilters = EntityFilters.Entity | EntityFilters.Attributes };  RetrieveAllEntitiesResponse response = service.Execute(request) as RetrieveAllEntitiesResponse;  foreach (string tableName in existingTable)  {    // Ensure the table represents an entity and isn't an extra table in the database    EntityMetadata metadata = response.EntityMetadata.FirstOrDefault(e => e.SchemaName.Equals(tableName, StringComparison.CurrentCultureIgnoreCase));
   if (metadata == null)
   {
     continue;
   }

   // Drop the existing view
   string viewName = $"{displayViewPrefix}{tableName}{displayViewSuffix}";
   if (views.Contains(viewName))
   {
     this.ExecuteNonQuery($"DROP VIEW {viewName}");
   }

   // Build up the new view command
   StringBuilder sb = new StringBuilder($"select {tableName}.*");
   foreach (AttributeMetadata attribute in metadata.Attributes.OrderBy(a => a.SchemaName))
   {
     if (attribute.AttributeType == AttributeTypeCode.State)
     {
       sb.Append($", (select {attribute.SchemaName}_metadata.localizedlabel from ");
       sb.Append($"StateMetadata {attribute.SchemaName}_metadata with(nolock) ");
       sb.Append($"where {attribute.LogicalName} is not null and ");
       sb.Append($"{attribute.LogicalName} = {attribute.LogicalName}_metadata.[State] and ");
       sb.Append($"{attribute.LogicalName}_metadata.entityname = '{tableName}') as {displayFieldPrefix}{attribute.SchemaName}{displayFieldSuffix}");
     }
     else if (attribute.AttributeType == AttributeTypeCode.Status)
     {
       sb.Append($", (select {attribute.SchemaName}_metadata.localizedlabel from ");
       sb.Append($"StatusMetadata {attribute.SchemaName}_metadata with(nolock) ");
       sb.Append($"where {attribute.LogicalName} is not null and ");
       sb.Append($"{attribute.LogicalName} = {attribute.LogicalName}_metadata.[Status] and ");
       sb.Append($"{attribute.LogicalName}_metadata.entityname = '{tableName}') as {displayFieldPrefix}{attribute.SchemaName}{displayFieldSuffix}");
     }
     else if (attribute.AttributeType == AttributeTypeCode.Boolean ||
       (attribute.AttributeType == AttributeTypeCode.Picklist && ((Microsoft.Xrm.Sdk.Metadata.EnumAttributeMetadata)attribute).OptionSet.IsGlobal != null && !((Microsoft.Xrm.Sdk.Metadata.EnumAttributeMetadata)attribute).OptionSet.IsGlobal.Value))
     {
       sb.Append($", (select {attribute.SchemaName}_metadata.localizedlabel from ");
       sb.Append($"OptionSetMetadata {attribute.SchemaName}_metadata with(nolock) ");
       sb.Append($"where {attribute.SchemaName} is not null and ");
       sb.Append($"{attribute.SchemaName} = {attribute.SchemaName}_metadata.[Option] and ");
       sb.Append($"{attribute.SchemaName}_metadata.entityname = '{tableName}' and ");
       sb.Append($"{attribute.SchemaName}_metadata.optionsetname = '{attribute.SchemaName}') as {displayFieldPrefix}{attribute.SchemaName}{displayFieldSuffix}");
     }
     else if (attribute.AttributeType == AttributeTypeCode.Picklist && ((Microsoft.Xrm.Sdk.Metadata.EnumAttributeMetadata)attribute).OptionSet.IsGlobal != null &&
       ((Microsoft.Xrm.Sdk.Metadata.EnumAttributeMetadata)attribute).OptionSet.IsGlobal.Value)
     {
       sb.Append($", (select {attribute.SchemaName}_metadata.localizedlabel from ");
       sb.Append($"GlobalOptionSetMetadata {attribute.SchemaName}_metadata with(nolock) ");
       sb.Append($"where {attribute.SchemaName} is not null and ");
       sb.Append($"{attribute.SchemaName} = {attribute.SchemaName}_metadata.[Option] and ");
       sb.Append($"{attribute.SchemaName}_metadata.optionsetname = '{attribute.SchemaName}') as {displayFieldPrefix}{attribute.SchemaName}{displayFieldSuffix}");
     }
   }
   sb.Append($" from {tableName}");
   this.ExecuteNonQuery($"CREATE VIEW[{viewName}] as {sb.ToString()}");
 }
}
protected string ValidateSqlString()
{
  string errorMessage = String.Empty;
  if (String.IsNullOrEmpty(this.SqlConnectionString))
  {
    errorMessage = "Connection String is empty";
  }
  else
 {
   try
   {
     using (SqlConnection connection = new SqlConnection(this.SqlConnectionString))
     {
       try
       {
         connection.Open();
       }
       catch (Exception ex)
       {
         errorMessage = "Error establishing connection to SQL server: " + ex.Message;
       }
     }
   }
   catch (Exception ex)
   {
     errorMessage = "Configuration error in sql connection string: " + ex.Message;
   }
 }
 return errorMessage;
}

protected DataTable ExecuteQuery(string queryString)
{
  DataTable table = new DataTable();
  using (SqlConnection connection = new SqlConnection(this.SqlConnectionString))
  {
    SqlCommand command = new SqlCommand(queryString, connection) { CommandTimeout = 1200 };
    connection.Open();
    try
    {
      SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection);
      table.Load(dr);
    }
    finally
    {
      connection.Close();
    }
  }
  return table;
}

protected bool ExecuteNonQuery(string queryString)
{
  bool successful = false;
  using (SqlConnection connection = new SqlConnection(this.SqlConnectionString))
  {
   SqlCommand command = new SqlCommand(queryString, connection) { CommandTimeout = 1200 };
   connection.Open();
   try
   {
     command.ExecuteNonQuery();
     successful = true;
   }
   finally
   {
     // Always call Close when done reading.
     connection.Close();
   }
 }
 return successful;
}

 

Improving Performance of CRM Forms with IFrames

Note: I originally published this at cobalt.net

Microsoft Dynamics CRM has long supported Iframes on forms but the way they’re implemented usually has a negative impact on form load times. While I was researching the form load process in CRM, I came across a great topic on MSDN which describes how Iframes should be loaded for best performance. The article at ‘Write code for Microsoft Dynamics CRM forms’ (http://msdn.microsoft.com/en-us/library/gg328261.aspx) suggests using collapsed tabs to defer loading web resources, which means we can eliminate the load times for Iframes completely if you’re not going to use it. The idea is, instead of setting the Iframe URL in OnLoad you use the TabStateChange event, which fires when the Tab is expanded and collapsed.

To implement this we need to add a Javascript web resource for setting the URL, the form customizations with the tab and Iframe, and update the tab event to call the Javascript. In this scenario we’re going to set up an Iframe to show an Account’s website.

Setting up the Web Resource

Go to your default (or named) solution and add a new Web Resource of type Script (Jscript). The name of my script is cobalt_DeferredFormLoad. After saving the resource, click on the Text Editor button to enter your script.

improving-performance-of-crm-forms-with-iframes1.jpg

This is the script I’m using to set the URL. It’s quasi-generic, and will work for any entity where there is a website field on the form. The function takes in the name of the tab, the frame, and the attribute of the website.

function LoadIFrame(tabName, iframeName, websiteAttribute) {
if (tabName != null && iframeName != null && websiteAttribute!= null ) {
var isTabExpanded = (Xrm.Page.ui.tabs.get(tabName).getDisplayState() == “expanded”);
var websiteUrl = Xrm.Page.getAttribute(“websiteurl”).getValue();
if (isTabExpanded == true && websiteUrl) {
var IFrame = Xrm.Page.ui.controls.get(iframeName);
if (IFrame != null) {
IFrame.setSrc(websiteUrl);
}
}
}

}

Adding the Form Customizations

From the Account form, insert a new Tab and leave the default values. Click in the Section area of the new tab and insert a new Iframe from the Ribbon Bar. Give the frame an appropriate name (IFRAME_CompanyWebsite) and any default URL. I unchecked ‘Display label on the Form’ because the tab label looks good on its own.

improving-performance-of-crm-forms-with-iframes2.jpg

Now that the Iframe is setup you need to configure the Tab properties to load the Iframe. Under the Display tab, give an appropriate name and label, and uncheck ‘Expand this tab by default’.

improving-performance-of-crm-forms-with-iframes3.jpg

Configuring the Event

With the Tab properties open, go to the Events tab, Add your new web resource, then Add a new Event Handler

improving-performance-of-crm-forms-with-iframes4.jpg

In the Event Handler screen, fill in the name of the Function from the Javascript (LoadIFrame), and in the parameters section, list the parameters that need to be passed in the to function. The parameters are the name of the Tab, the name of the Iframe, and the name of the field that contains the URL to set. Remember that you’re specifying a string value and each parameter should be enclosed with a single quote mark.

improving-performance-of-crm-forms-with-iframes5.jpg

Once you save and publish the customizations you can verify the results on your entity. Open the account form and find your new Tab, expand it, and verify everything opens correctly.

Miscellaneous Notes

  • If your CRM instance uses SSL and the Iframe src doesn’t, then you may receive a warning from your browser (or no information at all). You will need to adjust your security settings.
  • You can easily update the Javascript function to not take any parameters and hardcode the tab, frame, and URL. I set it up this way so you could use it on other forms (contact, lead maybe) but it may not be needed in your case
  • If you use this same example, you can add a few other event to make the process a little cleaner. Since the website field is on the form, you know that if the website is blank then there’s no need to even show the Tab at all. I would add scripts to these events:
    • OnLoad of the form– If the website is empty then set the visibility on the tab to false
    • OnChange of the website field – If the website is set then show the Tab, if it’s cleared out then hide the Tab

Understanding the Microsoft Dynamics CRM Performance Center

Note: I originally posted this at cobalt.net

The Microsoft Dynamics CRM Performance Center is a fantastic addition to CRM which can help you troubleshoot performance issue related to loading forms in CRM. I’ve found a few online references about the CRM Performance Center but they all focus on how to access it and don’t really give you context for interpreting the results. This is my attempt to combine the diagnostics messages with other CRM materials (Microsoft Dynamics CRM 2013 Updated Form Performance, http://blogs.msdn.com/b/crm/archive/2013/10/31/microsoft-dynamics-crm-2013-updated-form-performance.aspx) in order to make the Performance Center more helpful for the CRM community.

Starting the Performance Center

The Performance Center is available in CRM Online and CRM OnPremise 2013 SP1 or higher. The performance center doesn’t seem to work in Chrome (the browser closes with the key combination), but IE 8+ works fine.

To enable the Performance Center:

  1. Open a form that you want to benchmark
  2. Hit Control+Shift+Q to open the Performance Center window
  3. Click ‘Enable’ to start logging, and then ‘Close’

performance center 1

  1. Refresh the form and open the performance center again
  2. Verify the benchmarks are now loaded
  3. Click ‘Select Major’ to get a summary of milestone.

performance center 2

  1. When you’re done with the Performance Center, make sure to click ‘Disable’ to stop the logging, so you aren’t adding additional overhead.

Diagnostic Messages and Associated Events

Diagnostics Message: Form Load Start (→ 0ms)

  1. The diagnostics timer starts and form initialization begins
  2. The form load bootup process starts, which requests entity instance data, configuration information, CSS, and Javascript tags from the CRM server.
  3. Entity metadata and the form retrieval – The layout is retrieved from the browser cache
  4. Data binding and building the read ready form – The form layout and record data from the previous two steps are built and displayed to the user. All of the fields and their data are visible but they aren’t editable yet. This state is called View Ready, or Read Ready.

Diagnostic Message: Read-Ready (→ 931 ms)

  1. Transitioning the form to edit ready – The form downloads any additional Javascript which has logic for any controls on the form. The Javascript is executed in order. Individual field controls are initialized.
  2. Social pane and Inline subgrids initialization – In this stage, data is pulled from related records for display in the Social pane or subgrids on the form.
    • Expanded grids in the current viewing area are loaded
      Diagnostic Message:  Initialize Controls – ViewportInlineEditControlInitializer (→ 2452 ms)
    • Expanded grids off the current viewing area are loaded
      Diagnostic Message:  Initialize Controls – NonViewportFormBodyInlineEditInitializer (→ 2567 ms)
    • Collapsed grids are loaded
      Diagnostic Message:  Initialize Controls – DeferredQuickFormInlineEditInitializer (→ 2778 ms)
    • Load the Social Wall
  3. The Form’s OnLoad event is executed

Diagnostic Message:  Form Full Controls Init (→ 3390 ms)

At this stage, the form is completely editable. Clearly there are a lot of moving parts when it comes to rending a single form in Dynamics CRM.

How to Improve Performance

The messages can be a bit misleading because a lot of the events are handled, at least partially, asynchronously. For instance, grids can begin to load in step 6.a, but there’s another event “CompleteInlineEditInitialization” that runs parallel with loading the social wall in 6.d. I think the general approach should be to minimize that critical path of code that must run synchronously.

Before getting into specifics improvements, one easy way to help load time is to simplify what’s available on the form. CRM can be such a powerful tool that sometimes we might get carried away and try to just show everything possible on each form, at the expense of performance and really, user experience. If you’re judicious about what you’re showing then every step could be quicker.

Between initialization and read-ready
The two primary actions here are retrieving the record from the database, and merging that with the HTML template for the form. The big gain you can get here is just from reducing the number of fields that need to be handled. If you have a plugin registered on the Retrieve event of the entity, that time would be logged here, so you may want to look at optimizing you plugin step as well.

Between read-ready and Initialize Controls – ViewportInlineEditControlInitializer
The two big actions here are downloading and running field Javascript and loading expanded grids in the current viewing area.

To reduce time associated with loading and executing Javascript, you can follow a number of best practices described in the article Write code for Microsoft Dynamics CRM forms, available at http://msdn.microsoft.com/en-us/library/gg328261.aspx . Best practices include:

  • Avoid including unnecessary JavaScript web resource libraries
  • Avoid loading all scripts in the Onload event
  • Use collapsed tabs to defer loading web resources

I can’t find any firm references online, but from talking with other CRM developers the consensus is that collapsed Subgrids load more asynchronously than expanded Subgrids. Since they’re collapsed, the form only has the header to render because they records aren’t needed until the grid is expanded.  Based on the diagnostics messages, it seems like the most intensive grids to load are:

  1. Expanded grids in the current view
  2. Expanded grid outside the current view
  3. Collapsed grids

If you’re seeing large times in this area, you may want to look at changing grids to be collapsed, or at least lower down the page. Either way, you should see a reduction in the critical path to loading a page.

Another approach here is to limit the columns that are displayed in the grids (not just the count, but the logical location of the records). If you’re on an Account record looking at the names of associated Contacts, you’re touching one table for the contact info. If the contact grid also includes address information, now you’re linking to both the contact and customeraddress table and your query will take longer to execute.

Between Initialize Controls – ViewportInlineEditControlInitializer and Initialize Controls – NonViewportFormBodyInlineEditInitializer
It seems like most of the time in this section is for loading expanded grids off the current viewing area. As I mentioned before, these can either be collapsed or removed completely to speed up load time.

Between Initialize Controls – NonViewportFormBodyInlineEditInitializer and Initialize Controls – DeferredQuickFormInlineEditInitializer
Since the times here are for initializing collapsed grids, there’s not much you can do to speed up this stage besides removing the Subgrids altogether.

Between Initialize Controls – DeferredQuickFormInlineEditInitializer and Form Full Controls Init
The form Social Wall is loaded and the forms OnLoad event is executed. If you’re seeing excessive slowness here you can try to limit what’s on the Wall or remove it altogether, or dig into your event code to look for inefficiencies in either the script logic or even what’s loaded (see Write code for Microsoft Dynamics CRM forms above).

Conclusion

Microsoft Dynamics CRM is an incredibly extensible and customizable tool, but with that freedom comes a potential for performance issues. By removing unnecessary form fields and optimizing Subgrids, we’re able to speed up performance and give a cleaner, crisper user experience. Although there will always be some minimum load time for forms, if we’re selective about what we present and how it’s presented, we can greatly enhance the user’s experience and increase overall satisfaction.