Business Intelligence User Tools

Key Points

  • Use a specialist Report Writer for complex layout or precise look and feel but beware of creating long list reports that do not support effective decision making
  • Ad hoc query tool give users ultimate flexibility, and provide a usable way of exploring the data. However, for routine analysis and decision making ad hoc query tools have a number of flaws
  • If spreadsheet integration is a priority then you should inspect candidate tools closely for this functionality. Some BI tool simply export data to a spreadsheet format whereas tightly integrated offering will allow users to automate data integration and publish complex reports and analysis without leaving the spreadsheet environment
  • Many dashboard tools have amazing visualisations that instantly wow the audience. However, keep in mind that users are interested in something that will support their primary business goal. They will certainly be curious to see the latest gadget, but will not come back for more unless it helps them with their work

The preferred style of interaction for end user business intelligence tools is highly subjective with different user groups favouring different tools. The generic business intelligence solution architecture includes a number of different tool types. Understanding the value of each type is the key to matching business problems with business intelligence solutions.

About CMBI

User interaction

Different user groups will benefit from different business intelligence tools and interactions styles. In the following sections, I discuss each of the user interaction tools in the generic business intelligence solution architecture and provide guidance for their use.


Static and parameterised reports are not at the glamour end of the BI toolset. It is easy to overlook a specialist report-writing tool because it may be possible to produce reports using a spreadsheet or dashboard interface.

Report writer tool

I recommend that you include a specialist report writing tool in your solution architecture because they provide the best support for complex layout, consistent presentation, pagination, report scheduling, pixel perfect rendering and printing, various export formats, controlled parameters, and integration into other applications.
One common application of a report writer is to automate reports previously generated in spreadsheets. A good reporting tool should be able to replicate an existing report format precisely. This may be crucial if the report is for a supplier or a submission to a government authority. Specialist dashboard tools struggle with this kind of task. They have limited layout capability, do not support fine-tuned pagination, or cannot export to the required format.

Report proliferation

There is nothing more depressing to a BI specialist than a 100-page report reeling off the printer destined for someone’s in-tray (trashcan). I cannot think of any justification for producing a 100-page report, let alone printing it.
Another woe of the reporting world is the situation where we produce dozens of routine reports– either manually or with a reporting tool – and deliver them by email to all and sundry without regard for their usage or relevance.
In both of these instances, the lack of focus undermines the value of the information. In the first case, it is clear that a 100-page report will not efficiently support any decision process because of the effort required to find relevant content, coupled with the risk of missing the pertinent information. In the second instance, we cannot accurately gauge whether the report is useful and who might benefit from more focused support.

BI in practice
The dangers of unfocused reporting
A company distributes a large stock inventory report to all the warehouse managers on a weekly basis. The report has a section for each warehouse containing all the products for that warehouse sorted in product order.
The warehouse manager goes to the section in the report that relates to the warehouse they manage and looks at the stock for each product. Each section has hundreds of lines for all the different products. The warehouse manager looks through the report to determine products that are low on stock.
Unfortunately, a data entry error has resulted in some products without an allocated warehouse. Because each warehouse manager only considers the section in the report relevant to their warehouse, the unallocated products go unnoticed until some are totally out of stock, resulting in an investigation.
It is clear that the report does not support the business process because it forces the recipients to analyse a large amount of irrelevant data to complete their task. The more noise in a report the less likely genuine issues will be noticed.

Ad hoc query

Ad hoc query tools are at once a blessing and a curse. The great selling points are that they take little configuration, give users ultimate flexibility, and provide a usable way of exploring the data. The user community often insist that an ad hoc query tool is the first and only required deliverable. Given the apparent ease of deployment, solution designers are happy to comply.
Complete reliance on ad hoc query tools is a sign that something is missing in the business intelligence cocktail. It can also result in a lot of unnecessary development work as the solution tries to cater for all eventualities. The advantages of ad hoc tools are immediately apparent but the disadvantages are more subtle and benefit from further discussion below.

Business goal

Ad hoc query tools do not directly support a business goal or decision. Where someone uses the tool in an unstructured way for regular decisions there is no easy way to measure the effectiveness of the decision process. Where someone uses the tool to produce a regular report or analysis, we should capture the procedure and deliver the content using automated reports or dashboards. This will guarantee the consistency of approach and remove repeated manual effort.
A legitimate use of ad hoc query tools is to respond to a new opportunity, line of enquiry, or prototype a new solution. All too often, the recipient of an ad hoc query then requests that the information is delivered on a regular basis, but we continue to produce the report using the same manual methods.


Ad hoc queries on small or simple data models generally have very good query performance. However, when you introduce algorithmic calculations or complex data relationships into the mix, query performance can suffer and server resources may be strained.
Ad hoc query analysis is very resource inefficient. Typically, the user will execute a large number of redundant queries in order to arrive at their final destination; this consumes time and server resources. Slow query response time frustrates the immediate user and potentially denies good service to other users sharing the same infrastructure.

Data model usability

Ad hoc query tools shift the complexity of implementation from the front-end tools into the data model. Users become frustrated that the data model is not easy to use, or where ease of use is not a problem, they find it is not powerful enough to answer all their requests.
It is very difficult to create a data model that captures all the complexities of a domain and is immediately intuitive to non-technical users. Manipulating the data or storage model to the advantage of one set of users can have an equally detrimental impact on another.
Even a simple data model will yield unintuitive results if the user does not understand the nuances of the underlying business rules. When we create a structured deliverable, like a report or dashboard, we can augment charts and tables with comments or description to clarify their meaning. Many analytical databases provide support for augmenting the data model with descriptive metadata but often this is insufficient to avoid ambiguity and misinterpretation completely.

Presentation of information

The value of information lies as much in its presentation as its content. If the instructions for a piece of self-assembly furniture have ever frustrated you, then you have had firsthand experience of this axiom.
Ad hoc query tools place the responsibility for layout and presentation firmly in the lap of the user. It can be time consuming to present data at different levels of granularity or subject areas side by side for comparison. The most interesting patterns in data seldom present themselves in simple summaries that are easy to produce using ad hoc tools.

Ad hoc query – final words

Despite the shortcomings of these tools, I would still advocate providing ad hoc query tools to anyone who feels they might benefit from them. The best way to prevent inefficient use of these tools is by promoting alternatives as part of the deliverables in each project. If the advantages are compelling then behaviour will change without the need to be prescriptive.


Just about every BI tool on the market will claim to support spreadsheet interaction. This is not surprising because many users judge the power of a BI tool by analogy to the functionality of their spreadsheet application. This is a very high bar because spreadsheet applications have become extremely powerful BI tools in their own right. Not surprisingly, some BI vendors have cut out the middleman by providing tight integration within the spreadsheet environment; the tool effectively extends the existing functionality of the spreadsheet application. Other BI applications simply export text files, which you can open and edit with a spreadsheet application in the normal way.
If the business community are spreadsheet gurus then nothing short of full spreadsheet functionality will satisfy them. In this case, I would advocate using an analytical database or BI tool with tight spreadsheet integration where the users can work directly with the BI tool and data within the spreadsheet environment. Using these tools, users can quickly automate routine reporting and analysis because they are already familiar with the spreadsheet tool and data. They can continue to use the spreadsheet to create the layout and presentation but the content will now update automatically when the underlying source acquires new data. There are a growing number of offerings in this space so it is worth researching the alternatives.
If spreadsheet integration is a priority then you should inspect candidate tools closely for this functionality. If the BI tool simply exports data to a spreadsheet format this is less than ideal as users will still have to perform manual data integration and routine formatting in the spreadsheet environment. We may just exchange one manual process for another.


Anyone who has seen a vendor presentation of BI tools will be familiar with executive dashboards. They have colourful gauges and traffic lights indicating the status of the business KPIs. The user can click on a KPI visualisation and drill down to a lower level of detailed data that explains the high-level trend. Whilst there is some debate amongst practitioners about the best way of visualising KPIs, most agree that dashboards and scorecards are a very effective delivery channel when carefully designed.
My personal experience is that dashboards work as well, if not better, for supporting operational processes as they do for executive level summaries. Operational dashboards have a narrower focus making it easier to source the data in the early stages of a BI program. Events tend to move quickly at an operational level and a dashboard can provide high visibility of changes and anomalies.
A real issue with executive level dashboards is finding the balance between providing a holistic view of the organisation whilst creating something that is responsive to events. If the metrics in the executive dashboard change infrequently or imperceptibly, executives will look at them once or twice and then move on.

Dashboard tools

Dashboard tools share some interesting characteristics with movies and computer games. Great graphics and special effects look amazing on the trailer and hold your interest for the first ten minutes of the film. But after the initial high, we want to engage with the storyline and the characters. If there is no substance underlying the flashy effects, we will quickly lose interest. Dashboard tools are the same. Many of them have amazing visualisations that instantly wow the audience. However, keep in mind that users are interested in something that will support their primary business goal. They will certainly be curious to see the latest gadget, but will not come back for more unless it helps them with their work.
It is important to separate the process of dashboard design – which is analytical and business oriented – from the implementation and delivery channel, which could as easily be a static document as a specialist BI tool. The impressive data manipulation features of dashboard tools will be most useful for analysts who can correctly interpret the impact of slicing and dicing the data. If senior managers and executives are your target audience, they may not appreciate or use these features.
Remember, the hardest part of implementing dashboards is deciding the content and integrating the data. Once we complete this preparation work, a good dashboard tool will provide a user-friendly interface for designing the layout and visualising the data. With this in mind, we should assess the strength of a dashboard tool by considering the:

  • Usability of the design interface
  • Flexibility of layout
  • Support for a wide variety of chart types and visualisations
  • Ability to realise a conceptual design

It is difficult to evaluate these criteria from a vendor demonstration alone, because most presentations focus almost exclusively on the end user interaction with the final dashboard design. The level of end-user interactivity of the dashboard tool may be important but we should also remember the primary purpose of the tool and the target audience. You may be judging the dashboard product with the criteria for an ad hoc analysis tool.
Some products integrate a dashboard tool with an analytical database engine – the data must be in the vendor’s database to use their dashboard tool. In this instance, you should ensure that the database component is capable of supporting all the data and calculations you require in the dashboard.  


See Also