There’s a funny thing about business intelligence: people are baffled by the technologies involved in building insights but the most challenging things about the discipline have little to do with technology. I’ll grant that using PIVOT and GROUPING SET queries is not trivial but any developer can figure those out in an afternoon. Where I see people struggling more is in deciding which tools to use for BI projects.
Let’s take dashboards as an example. There are a great many dashboarding tools available: stand alone tools, tools for developers, tools for end users, tools for specific applications and tools that run across enterprise data sources. Deciding which of these tools to use isn’t a matter selecting the best of breed tool or even just deciding on the one that comes with your application; it is a question of which tool is best for your organization.
For any sort of application you should evaluate your organization’s BI capabilities. Let’s take three cases:
1. Capabilities: BI at your business is mostly about reporting. The IT organization is fairly efficient at churning out RDLs on demand.
Dashboard Solution: Use RDL. With a team of developers proficient in building reports you should use their existing skills and leverage existing infrastructure. It's easy for RDL developers to build dashboards with RDL and with SQL 2008 you can go some really great looking stuff. For users whose KPIs and report insights don’t change very often this is the best choice.
An RDL based dashboard from SQL Server Reporting Services 2008
2. Capabilities: End users are good at doing their own analysis or are always changing their minds about what they want to monitor or are not happy with the speed of report development that they get from IT.
Dashboard Solution: Use Excel. Users already have Excel on their desktop and they know how to use it. The native ability for users to link live CRM data to Excel will allow them to build sophisticated pivot tables and charts without learning to use another tool. The flexible layout capabilities in Excel give analytic users a dashboard that they can change frequently, carry with them and work with while disconnected.
A highly interactive dashboard in Excel with live links back to CRM data
3. Capabilities: Your BI team has built data cubes for CRM data and has even combined the CRM cube with data from other sources. Your organization has the MOSS Enterprise CAL.
Dashboard Solution: Use Performance Point Server. PPS is now part of the MOSS Enterprise CAL. To take the fullest advantage of it you’ll want to work with OLAP data (though you can use PPS to embed reports into a dashboard). The scorecarding capabilities are great, data from multiple sources can be displayed and the ease of access to SharePoint based dashboards makes this the best solution for organizations that need to get dashboard data in front of managers and executives who won’t start up a business application to get their dashboard.
A Performance Point Server dashboard inside of Microsoft Dynamics CRM
None of this guarantees that you won’t ever have to buy another BI tool again - in some cases you should take a look at new BI tools like the awesome Zap Technology cube builder for Microsoft Dynamics CRM or Powerobjects’ slick dashboard tools. The real point is that with BI projects, just as with the CRM implementation itself, success will come when you match the tools to the technical and social environment in which they are being deployed. I’ve described three ways to deliver dashboards for Microsoft Dynamics CRM but the more important information is how you decide which of those tools to use.