For most organizations, successful analytics requires a set of management dashboards coupled with the ability to dive deeply into the data with Ad Hoc analysis.  With Dynamics CRM an organization has a number of options for generating Ad Hoc sets of data and analyzing that data.  This post will focus on outlining some of the key options, and future posts will follow up with the specifics of implemention.  In order to provide some context, I'll define some basic Ad Hoc data interactivity up through advanced with each step allowing for more flexibility, but requiring additional technical accumen to implement -

Basic -

1.  Advanced Find - Dynamics provides the Advanced Find tool out of the box, and this user driven query tool allows you to search on and return sets of records based on a wide variety of user input parameters.  This can be a very powerful engine for gathering records that can then be reported on using the report wizard or exported to Excel and turned into Pivot Charts.  This option is great for getting started with Ad Hoc analysis, but it is limited in its usefullness as data volumes grow and query times slow down the workflow of power users.

2.  Excel Connectivity - Dynamics provides the ability to directly connect Excel to CRM and pull back streams of data based on user selections or advanced SQL queries.  With the ability to directly query the filtered views via SQL this provides a solid environment for technical users to generate both streams of data for analysis as well as Pivot Charts for management visualizations.  This option is great for doing true one off analysis and prototyping reports, but it is limited in its ability to scale up and it requires SQL adept users to really get good performance as data volumes grow.

Intermediate -

1.  OLAP Cube - SQL Server Analysis Services is an available option with the core data platform leveraged by Dynamics CRM.  As a result, organizations can quickly implement an OLAP cube that users can connect to to perform Ad Hoc analysis via Excel or a web page.  The power of this solution is that the OLAP storage format speeds up user performance significantly as compared to running queries through Advanced Find or via Excel directly against the database.  In addition to the performance gains, users get better control over filtering and drag and drop interactivity when setting up pivot tables and charts.  Future posts will focus on this approach as the best option for most organizations looking to move beyond the basics, and will provide implementation tips and some practical examples.

Advanced -

1.  PerformancePoint - Microsoft PerformancePoint is a full fledged planning, analysis, and business monitoring system that can pull data directly from CRM or from an OLAP cube.  This advanced tool allows for businesses to not only identified structured KPIs and their boundary conditions, but also provides the capability to move from structured KPIs into free form Ad Hoc analysis.  The power of PerformancePoint represents a complete platform for managing the business, but my experience has been that most Dynamics customers are currently in a transitional phase from basic to intermediate and it's most important for them to successfully get to the intermediate stage before jumping to an advanced platform that requires both increased technical sophistication and clearly defined KPIs against which to measure the business.

My next post will focus on building an OLAP cube against Dynamics and then we'll dive into how an organization knows it's ready to move from the Intermediate stage to Advanced tools.