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.

Weblog by Paul Turley and SQL Server BI Blog.