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;
}