locked
Expected query performance for analytic reports using filtered views RRS feed

  • Question

  • Hi.  I'm a Business Intelligence solution architect with a little prior experience with MSCRM.  I've been asked to design a whole bunch of SSRS analytic reports for a new CRM client.  All of the design work is being done against small sets of sample data.  Integration and large loads of historical data from their old system will occur long after my reports have been in place.

    I would appreciate your insight based on your experience working with the CRM database.  Some of the reports that they've requested will need to aggregate large volumes of historical data.  One report in particular is designed to show quotation totals for two years, comparing parallel quarterly totals.  Keep in mind that I’m a BI guy and we usually advise against doing analysis over live operational data stores.  We usually use a data warehouse and cubes for these types of reporting scenarios – however the logic is relatively simple.  The filtered views select from other views which, in turn, join many tables together.  I’ve analyzed the SQL Server execution plan for some simple queries that use the filtered views and there are many (70-80?) separate execution steps.  Based on your prior experience with MS CRM, can we expect these queries to perform adequately with years of production data?   I'd really like to hear some real stories abot how large companies with seven years of live production data are producing line charts and deep analytics with no performance issues to speak of.  Please tell me that it's true!


    Paul Turley, MVP [Hitachi Consulting] SQLServerBIBlog.com
    Friday, December 3, 2010 9:45 AM

Answers

All replies

  • Hi Paul,

    I have experience developing reports for fairly large MS CRM implementations. I’m afraid my experience developing reports based on the recommended filtered views has less than ideal.

    In my experience the performance of reports on filtered views disintegrates fairly rapidly as the volume of data and the complexity of the report increases. I ended up writing reports directly on the SQL tables and had to create custom indexes on tables.

    Unless you are developing fairly simple reports, my recommendation to you would be to use your BI development experience and apply the same principals to the MS CRM database.

    The URL below details a similar experience and some recommendations.

    https://community.dynamics.com/product/crm/crmtechnical/b/crmpowerobjects/archive/2010/11/03/optimizing-complex-report-performance-in-microsoft-crm-4-0.aspx

     

    Hassan.


    Hassan Hussain | http://hassanhussain.wordpress.com/
    Friday, December 3, 2010 1:45 PM
  • Thank you Hassan for confirming what I belived to be the case.  Data is data regarless of the business application, and BI solutions must be implemented using proven design patterns and best practices.
    Paul Turley, MVP [Hitachi Consulting] SQLServerBIBlog.com
    Friday, December 3, 2010 5:59 PM