It’s all about reporting…
As CTO at Orange Leap I’m constantly reminded that CRM applications are only as useful as the reporting capabilities. It makes absolutely no sense to put data into any system unless you can get the data back out in the format the customer desires.
Over the years we have implemented a couple of different reporting solutions each with it’s own shortcomings and benefits. Below I will outline these implementations and the shortcomings and benefits of each solution. Additionally I will give you a glimpse into our new reporting implementation that hopefully will retain the benefits of it’s predecessors while removing the shortcomings.
In the beginning there was Crystal Reports
Our window’s based on premise solution (MPX) used Crystal Reports as it’s reporting solution. By embedding Crystal Reports into our product we provided a complete reporting solution for MPX with very little engineering effort from Orange Leap. Additionally we shipped a slew of “canned” reports with MPX to allow our customers to extract data from our database without having to write reports them selves. By utilizing Crystal Reports our customers had the ability to write custom reports and use them through our application as well.
Benefits to using Crystal Reports
- Many developers know how to write Crystal Reports
- Crystal Reports is fast, stable and provides great output
- Most of our customers where able to answer their questions using the canned reports
Shortcomings of using Crystal Reports
- Expensive. The run time costs money and the design time costs even more
- Most of our customers did not have the capability to write a custom report
- Our services team inevitably had to write custom reports for our customers
- There is no testing framework to test these reports before deploying a new version. This leads to a time consuming and tedious manual testing process
- Reports often “looked” correct but presented “bad” data
- As new functionality is added to the application additional canned report’s needed to be developed
- Customers are forced to view their data the way we “think” they want to view it. Not necessarily the way they do.
Crystal Reports proved to be expensive, inflexible and manually intensive
Then Came The Guru
We decided to build a end user focused “Report Wizard” that allowed the end user to construct report’s on the fly. The Guru is built on a data dictionary that describes the underlying database, it’s content (fields) and it’s relationships (views). As I pointed out that the data dictionary points to “views” that describe the relationships between various entities within the system. The Guru was implemented on top of Jaspersoft’s jasper report’s server. Using Jaspersoft eliminated the upfront licensing costs for our customers and the tools can be had for free… We thought we had hit a panacea…
While this solution is more flexible and less expensive than Crystal Reports we did not provide many “canned” report’s thinking that customer’s would generate their own (and many of them have done so successfully). Some of our customers however have not made it up the learning curve to successfully utilize The Guru.
The underlying design to implement The Guru on “views” created a plethora of problems that have plagued it with problems. First and foremost is performance. On smaller databases The Guru performed just fine. But as the size of our customers databases grew The Guru started to show some cracks. Because our view’s used functions to define some of the columns associated with custom fields the database server was unable to optimize these queries and the result’s where very slow performance.
Additionally because we implemented the solution on “views” as new feature functionality was added to our application the “data dictionary” and “views” needed to be updated with each release. Frequently we would release new functionality and “forget” to update the view’s. This is a show stopper cause as I said “It’s all about reporting…” and customer’s now had data in our system that they could not report on.
Benefit’s of The Guru
- Generates accurate results
- Most of our customers are able to answer their questions with The Guru
Shortcomings of The Guru
- No canned reports left unsophisticated customers scratching their heads
- View’s quickly became out of date
- Lack of sub reports caused some limitations in reporting results
- There is no way to get to data that is not represented by the views
- View and data dictionary maintenance is manually intensive
The Guru proved to be cheap, flexible but manually intensive and slow!
So what’s the solution?
We have identified the underlying ‘views’ of The Guru implementation as the primary source of problems with this solution. As mentioned they create performance issues as well as maintenance problems for us and our customers. So the next generation of The Guru will have an administrative function that will allow the customer or our analysts to populate data sources with straight SQL rather than with views. Think of it as an advanced query builder that uses introspection to construct SQL based on the underlying data model.
I feel this will be the best of all worlds for us and our customers. First off it will facilitate the construction of data sources as new functionality is added to the product. Second it will allow the customer to have data sources that describe their data exactly the way they want to see it (not how we “think” they want to see it). It will also out perform our existing solution significantly.
Watch this blog for announcements on it’s availability shortly.