In concept, designing reports is such a simple thing… you connect to a data source, write a SQL query, optimize the query so it runs fast and efficiently, build the report UI and deploy it to the server.  Piece of cake, right?  Not always.  Reporting on simple, normalized data structures can be simple but sometimes reporting on specialized application data can get complicated, and the best approach may not be so clear.

I’m not going to pretend to have all the answers about using Reporting Services to report on MS CRM data but I have learned some lessons and will certainly learn more.  I’ve recently worked on two different client’s reporting projects where the rules of engagement were not so clear and it took several weeks just to reach the point of knowing what questions to ask in order to define sufficient business and technical requirements to design reports.  My most recent experience is with a customized implementation of Microsoft CRM 4.0.  Our statement of work is to deliver several reports similar to those in their old billing & ERP system along with the new CRM deployment.  I actually worked on the MS CRM team in 2004 as a consultant at Microsoft when they were developing CRM version 1.2.  The filtered views didn’t exist yet and the database was much more simple than it is now.  I designed a handful of SSRS 2000 reports for inclusion in the stock product to replace the original Crystal reports that shipped in the first version of CRM.  This new project is my first experience with the CRM product since that time and it’s a whole different ball of wax.  Our company has a dedicated Microsoft Dynamics practice with many people who know the Dynamics products inside and out.  It’s great to work with people who know their products very well but sometimes the challenge can be tapping into a culture that has evolved around a vertical product and its market along with some very unique concepts and practices.  As the general database and BI reporting expert, I’m very much the outsider in this culture.

Our CRM client’s business needs are quite unique from the typical call center or order-taking desk that might use a CRM tool.  Every business will have some of their own rules and requirements and the CRM framework provides for a good deal of customization to address some of these unique needs.  In the case for this client, many new database entities and fields have been added, the application UI has been modified and several business entities in the application have different names than those that are common in their business culture.  Here’s one or many possible examples… the client refers to the work they do for their customers as a “job”.  Depending on where they are in the sales cycle, a “job” could be equivalent to either a quote or a sales order in the MS CRM application context.  A quote can be converted or copied to an order record, which has roughly the same field structure as a quote but there are subtle differences.  Therefore, the translation of job to quote or order isn’t always apples-to-apples.  Since the new CRM solution is still being implemented for the client, the database schema hasn’t been completely locked-down during report design.  Adding few fields as we go wouldn’t be such a big challenge but some of the fields have been dropped and renamed, which of course, breaks queries and existing report designs.  To a certain extent, some query and report design can be a integral part of the field mapping discovery process but it’s very important to set the clients expectation and separate the requirement definition effort from production design.  Until you have sufficient experience, this can be time-consuming and difficult to provide work estimates.

Another issue stems from a database design pattern in MS CRM where all data access is supposed to be made through a set of special views that filter data based on each users’ Windows user permissions and group membership.  All of these views are prefixed with the word “Filtered” and provide simple data presentation over the complexities of the normalized database schema.  Every significant business entity has one of these filtered views that developers and report designers are encouraged to use in lieu of the base tables.  In many ways, this is great news for report designers who don’t have to content with complexities of the database schema.  The trade-off is that the report designer using them is completely oblivious to the specific field mappings and table relationships.  In many cases, this makes the design experience much easier but in some cases, locating a specific piece of application data can be a frustrating needle-in-a-haystack game.  Several fields have similar names and the views contain lookup table joins, concatenations and calculations.  If you don’t know that these derived columns exist, you may end up duplicating the same functionality in a report query and then figure out that the work had already been done for you.  Some of the filtered views are multiple layers deep, with views based on other views.  In one case, I joined two filtered views together, analyzed the execution plan for the query and found about 90 execution steps.  I can only imagine how slow a seemingly simple query may be with the database fully-populated.

Bottom line: the business rules must be well-known and documented before starting the report design.  The database schema should also be locked-down before designing report queries.  That’s the theoretical answer.  The reality is that MS CRM allows “CRM application designers” and non-database professionals to make schema modifications through the application UI.  These changes not only create tables and columns but modify corresponding views and indexes accordingly.  In the end, the challenge comes down to project management discipline and adhering to application design standards rather than the typical DBA/database developer design principles that exist for traditional database scenarios.