One of the projects I was recently involved in implemented a series of reporting tools in addition to Analysis Services cubes built from their Data Warehouse. As well as the basic slicing and dicing functionality provided by the cubes, reporting services reports were implemented in both a drill through and parameterized capacity.
The user base for the reports was vast, as were their individual requirements. As such, in the absence of a top down report design directive, it became apparent very quickly that either a huge number of reports would be required, or ad-hoc reporting tools needed to be given to the users. In this context, with the project keen to meet as many requirements as quickly as possible, Microsoft Report Builder was chosen as an ad-hoc reporting tool.
Microsoft tells us Report Builder ... delivers intuitive report authoring capabilities to end users and ... benefits power users that are looking for a more sophisticated authoring environment. One way of interpreting this is to discount the importance of the report design process safe in the knowledge that the users can do it themselves. It seems the era of reporting utopia is upon us. Or not ....
Without getting into the nuts and bolts of Report Builder, users are able to drag and drop fields from predefined report models onto a design surface before filtering, sorting and designing the layout as required. In essence, the report model shields the user from the complexity of the underlying data model by presenting them with a filtered, name friendly view of the database upon which they can design the reports they require. Other enterprise reporting platforms such as Cognos have similar tools. Like any tool, used correctly in the right hands, Report Builder can be very powerful. Having said that, using it as an excuse to avoid or fast track the business requirements for reporting will almost certainly end in tears.
Let me give you a hypothetical example; 1000 report users are asked for their reporting requirements. 950 of them ignore the request (reserving their right to complain later), and of the remaining 50, 47 different requirements come back. Let's walk through the various options for dealing with this.
Option 1: Refuse to develop/deliver anything until the users agree on what they want and management signs off. Good in theory, but like herding cats, probably not realistic.
Option 2: Develop a few basic reports for the obvious and common requirements, and hand the users an ad-hoc tool to let them custom develop the rest of their specific requirements. The theory behind this option is something like "Why have 3 report developers when you can have 1000?". My take on this is "Why have 3 professional, dedicated & experienced report developers when you can have 1000 part time amateurs"?
Let's talk about option 2 a little further. Giving users the tools to create whatever they like sounds like a great idea, but in the end, it's more work for everyone. Those responsible for performance tuning the database can hardly tune an environment for the needs of unknown requirements. From a users point of view, unless they're experienced with the reporting tool (or have sufficient training) they will most likely struggle with the report creation process, leading to increased support costs, frustrated users, partially met report requirements, and most likely poor performance, a consequence of the inability to performance tune for unknown requirements. But hey, at least we got something out the door fast right?
The other significant issue with option 2 is the fact that a large number of custom developed, ad-hoc reports will splinter off in all sorts of directions, the danger being that decisions will be made on the basis of some of these reports, which may or may not be accurate depending on how they were developed. Further, if the underlying database schema changes, and the developed reports are stored locally on users desktops, how will these be updated for the new schema design?
As with most problems of this sort, the best approach is usually a compromise between opposing views. In the current context, let's examine a third option;
Option 3: Develop common reports for the known requirements, release them, and then wait for the customization requests to roll in. Having used something, users typically have a better idea of what they really want, and (hopefully) the design process becomes somewhat more targeted and manageable. After having gone through such a process, the number of genuine ad-hoc reporting requirements should typically be a lot less that at the start of the process. At such a point, tools such as Report Builder can be deployed to a limited number of trained users, with targeted, customized and optimized report models in place, therefore supporting ad-hoc requirements whilst minimizing the performance impact on other users.
Handing a large number of untrained users an ad-hoc reporting tool is like opening the doors to the kitchen and inviting the customers to cook their own meal using the kitchen's ingredients and cooking equipment. Sure, the customers will probably initially enjoy the freedom to cook what they want, but with 1000 cooks crowded around 3 ovens and ingredients running out fast, this is clearly inefficient and lacking in scale.
Whilst option 3 requires a fair degree of hard work up front, I believe it's typically the best option. Whilst not as sexy as delivering intuitive report authoring capabilities to end users, at the very minimum, it certainly helps in controlling the performance impact of a large number of unknown and ad-hoc reports.
As with life itself, trying to please everyone all the time is rarely a successful strategy.
Cheers
Subscribe to this blog!
