Canned reports/dashboards aka custom reports or out of box reports are inevitable part of any BI ecosystem. It’s a good point of reference for conversations with business users especially at the start of a project when requirements are often a bit hazy. In many instances, business users see report layouts as an effective and convenient medium of communication with BI project teams. This however should not be considered as THE business requirement. Instead of dwelling on canned reports, the focus should be on building a well-designed DW/BI system that empowers users to perform analysis on a self-service basis. The quality and the range of analysis that a self-service BI platform provides far outweighs the benefits of just rolling out a set of canned reports. It empowers business users to do their own analysis while the BI team can work towards expanding and refining the analytical platform instead of operating as a “reporting team”.
BI projects that deliver only canned reports are often an indication of lack of project vision, immature/insufficient business analysis, poor design and inadequate project governance. Here is some of the shortcomings of delivering just the canned reports
Canned Reports Constitute a Tiny Percent of Analytical Requirements
Canned reports usually represent the top 10% (or less) of frequently queried information. BI investments should certainly deliver more bang for the buck than that. A better practise is to promote a frequently used adhoc report from a self-service BI system into a canned report. Not the other way around.
Canned Reports Divides Target Audience
Reports are usually targeted at operations staff or executives. Operational reports are usually more granular in nature compared to executive reports. The objectives of operational and executive reporting are completely different even though they are sourced from the same system. What is applicable for executive reporting may not be applicable for operational reporting and vice versa. Depending on who wields the clout one group prevails over another. Publishing a set of canned reports for one group without the capability for adhoc queries requirements that is not covered in canned reports only divides your business users. It also raises questions on the value of the BI investment.
Canned Reports Promotes Rebel Data Warehouses
Many organisation have data silos that are not supported by the enterprise DW/BI framework. Some examples of these rebel data warehouse are macro-ridden excel reports that find their way to boardrooms, reports from access databases, lotus notes “reports” database. The very existence of rebel data warehouses indicates that canned reports have failed to support analytical needs of business. The business users in turn with their limited DW/BI skills have taken things into their own hands. By providing a self-service BI platform and engaging the business this situation could have been averted.
Canned Reports Contribute to Bad Design Practises
Perhaps the biggest drawback of delivering just the canned reports is that it leads to stunted DW design. Typical symptoms of such systems includes:
Limited drill-down capability: Indicates inadequate dimensional modeling when only the bare minimum attributes required for the report is included in the dimension. Often a chance to leverage on an existing conformed dimension or build a new one is completely missed. Instead a cut-down “report” version of the dimension is created. Such dimensions usually have only one or two attributes.
Limited drill-through capability: Indicates that data at lower granularity is not available. Instead data is captured at report granularity which may be at a higher level of granularity than what the source system can actually provide. Any report/dashboard that cannot drill-through and account for the aggregated value is just a smoke screen.
Limited drill-across capability: Inability to report from multiple fact tables that share similar dimensions. This indicates failure to leverage on conformed dimensions to link with measure groups that share the same dimensions.
The problem with poorly designed system actually begins when it makes it to production. Such systems are prone to scalability issues, high cost of maintenance, excessive turnaround times for enhancements and performance deteriorations with time.
Canned Reports Diminishes Opportunity to Improve Data Quality
Unknown surrogate keys are used in fact tables to represent missing dimension values. The presence of unknown surrogate keys is an indication that either the data quality is poor or the dimension is late-arriving or an ETL issue that needs further investigation. Adhoc queries usually picks up these data anomalies. A better understanding of this imperfect data usually triggers process/procedures for better data capture, better data validation at source and better data cleansing rules to keep this figure low thereby improving data quality. One of the advantage of DW/BI systems is that it brings to light all these data anomalies that was previously obscure enabling product owners to fix their source systems. However canned reports are usually customized to exclude these imperfect records (for a good reason) thereby losing the opportunity to improve data quality.
Canned Reports Contributes to Embedded Calculations
Canned reports often gives rise to embedded calculations. A well-designed DW/BI system does all the measure calculations at DW and the reports just read the measure values from DW. Calculated measures should never exist in the report definition. Calculations embedded in reports undermine the DW. The source of truth is the DW and not the reports. Eventually this will create same measures with different definitions and different values across different reports.
Canned Report is usually a Square Peg in Round Hole
Business models, business needs and business leaders change with time, canned reports don’t. Merger, acquisition, diversification and organisation re-structure leads to new business processes rendering a canned report that was once useful completely irrelevant. By not having a self-service BI capability, businesses end up creating more and more canned reports in the name of business continuity. Eventually there is stack of reports where only a fraction is used at any point in time.
Canned Reports Leads to Proliferation of Reporting Tools
Canned reports place undue importance on the presentation layer. This has resulted in a proliferation of reporting tools. It is not uncommon to see even within the same business unit there are multiple reporting tools. A decent reporting tool is necessary, but let’s face it – the reporting tool is not going to solve gaps in business analysis and DW/BI design. Reporting tools with non-standard language constructs that are neither based on SQL or MDX only build vendor dependency. Several fringe players in reporting tools space have exited the scene over the years taking with them all the BI investment.
Canned Reports Promotes Wow Factor over Data Stewardship
Presentation layer is important but it’s not everything. A dashboard with 12 reports is impressive but initiates very little actionable decision. The ability to slice and dice data and make informed decisions is the best possible service that the DW/BI team can provide to business. The value of making a good business decision is priceless. The key to achieve this is better data stewardship where emphasis is on data integrity, data quality, data modeling, data cleansing, master data management and data security. Of course all of requires a strong stakeholder who isn’t swayed just by pretty pictures.
A well-designed DW/BI will satisfy most of the analytical requirements of business which includes but not restricted to canned reports. Canned reports are a given, not the end game to any BI strategy. Reporting tools available today are incredibly easy to use. A power user with a little bit of training can build reports and dashboards with reasonable ease while BI teams can focus on expanding and enriching the self-service BI Platform.