Recently I had a privilege to work on a xRM project utilizing CRM 4.0 and SQL Reporting Services to create very complex Business Intelligence reports base on transactional records which were housed inside MS CRM.

We had approximately:

• 25,000 accounts, which were assigned to one or more systemusers through sharing
• 3,000,000 detail transactional records, linked to the account
• 400 systemusers

Simplified, the relationships looked like following:

Basic requirement of reporting was to slice and dice sum of transactional data based on users or some account level information.

Some Important findings:

Performance of FilteredViews degenerates fast with more complex queries involving multiple entities. For example, query giving user-grouped sum ran in 20 seconds. Below a simplified core of the query:

select
SUM(po_amt),

    po_useridname

from     filteredpo_transaction,

FilteredAccount

where ( po_accountID = AccountID )

group
by po_useridname

A requirement for adding user title came up, we can do this by joining systemuser table to the query. This does not seem like a large change, but performance fell to 250 seconds. Below is the simplified core of that query:

select
SUM(po_amt),

    fullname,

    jobtitle

    from filteredpo_transaction,

FilteredAccount,

filteredsystemuser

    where ( po_accountID = AccountID )

    and     po_userid=systemuserid

    group
by FullName, jobtitle

Doing the similar comparison with non-filtered views, we experience drop from 15 seconds to 120 seconds, which helps but is not enough for user-friendly performance.

Note that when using Non-Filtered Views, you will need to account for security yourself in the report criteria/filtering.

Custom Indexes

While MSCRM has indexes of its own, when you create custom relationships, these relationships are not indexed by default. In my example case, if I create custom indexes for transaction data and also for the system user assignment, the numbers will change drastically:

The interesting details are that by introducing extra table to query actually improves query performance in this example. This is due to better utilization of indexes. Similarly the Account-Transaction filtered query did not benefit from the index at all.

Note that adding Indexes may slow down the creation and updating of the records.

Final Words

This example shows quite a dramatic improvement from unusable 250s report to blazing fast 2s report. While the results will vary, it shows that you do not have to accept slow performance as direct result of large number of rows or complex data model. With correct planning and design, the speed can be improved.

If this is something you are looking for help on let the CRM Experts at PowerObjects know – we have many tools to help you get the reporting you need out of your MSCRM system.

Happy CRMing