Tip #609: Higher SQL compatibility level does not mean better

Gayan “Performance Wizard” Perera continues to live up to his nickname with another awesome performance tip. tl;dr Beware setting the SQL database compatibility level to a level higher than 110 on a CRM 2016 system. The scenario You have an entity with more than 5 secured fields. When the record is opened it either times […]

Tip #405: Convert Your SQL To FetchXML The Easy Way (or Cai)

Kingswaysoft has recently updated their SQL2Fetch Convertor Let’s say you are moving from CRM On premises to CRM Online and you want to update your reports to work with CRM Online, you can take the SQL query from your report, paste it in the SQL box and click the “convert” button to generate FetchXML. This […]

Tip #240: Once bitten, twice filtered

Anything that’s been done twice is usually better. That includes double cream and double barreled. Except that report pre-filtering does not work if you need a filtering entity to be included more than once in your SQL. Consider this totally random piece of SQL: You cannot just apply pre-filtering because alias CRMAF_FilteredAccount cannot be used […]

Tip #233: Including external data in internal reports

You want to have a report in Dynamics CRM reference a database other than the Dynamics CRM database. Maybe you want to join external data, like data from your Dynamics AX ERP system, to account data in Dynamics CRM. In many cases, the best answer is to use a data warehouse, Write a report that points to the […]

Tip #221: Lobachevskian geometry

When things get out of control in a parallel universe, people notice and intervene. As turned out to be the case. One comment from Chad Rexin was so good that it deserved its own post. Welcome to the twisted SQL world where, like in Lobachevskian geometry, there is more than one degree of parallelism. Another […]

Tip #219: You want the top one

One invoice only

One of the challenges of creating reports is the lack of real data in development environment. There is not enough Nancies Davolios to simulate real volumes. For that reason alone production organization is often replicated “back” to test and development environments. Reports suddenly come to life and designers can see 7-figures opportunities and otherwise empty […]

Tip #216: In a parallel dimension

I’ve had the following discussion with two different CRM administrators in the past week. We’ll call them “Larry.” Larry: “My CRM organization is very slow. I try to search for records and it takes 25 seconds. I tried to import an organization, and it took 25 hours to complete.” Me: “What is your Max Degrees of Parallelism setting in […]

Tip #39: Custom indexes in CRM Online

A little known secret for CRM Online users and a good reminder from Daniel Cai. If you’ve added additional search fields to your Quick find or other views, the underlying entity contains a large number of records, and the response time is slow, you might need a custom SQL index added to improve the performance. […]