Article from the Architecture Journal ? 12-15-2009
by Paul Turley
Once upon a time, there was a big company whose IT department
wanted to ensure that everyone would see only good data in
their reports. To make sure of this, they ruled that all reports
would be created by IT from data that was stored on IT-controlled
databases. Business managers and users quietly circumnavigated
this?downloading data into spreadsheets and data files. Another
company?s IT group enabled the business to perform its own
reporting by using an ad-hoc tool?opening databases to everyone.
In both of these companies, when leaders had questions, everyone
had answers! The only problem was that the answers were all
different. Many organizations operate in one of these extremes.
Business users can gain important insight by using self-service
reporting tools. Armed with the right answers, leaders and workers
can take appropriate action and make informed decisions, instead
of shooting from the hip or waiting for reliable information to come
from somewhere else. Functional business-intelligence (BI) solutions
don?t evolve into existence and must be carefully planned and
These best practices adhere to some basic principles and
Manage the Semantic Layer
A single version of the truth might consist of data that is derived from
multiple sources. By simply giving users the keys to the database
kingdom, you aren?t doing anyone any favors. One size doesn?t fit all,
but business-reporting data should always be abstracted through a
semantic layer. This might be a set of views on a data mart, a report
model, or an online analytical processing (OLAP) cube. There are
substantial advantages in using the latter option, if your organization
is prepared for some development and maintenance overhead.
Analysis tools?such as the new generation of Report Builder in
Microsoft SQL Server 2008, and the pending release of SQL Server
2008 R2, Microsoft Office Excel, and Office PerformancePoint Services
for SharePoint?might be given to users, but the semantic layer must
be managed centrally by IT.
Separate User- and Production-Report Libraries
User reports might be used to make important decisions and might
even become mission-critical, but the reports, scorecards, and
dashboards that are ?guaranteed? to be accurate and reliable should
go through the same rigorous IT-managed design, development,
and testing criteria as any production-ready business application.
Designate a library for ad-hoc reports, separate from production
reports. Office SharePoint is an excellent medium for this purpose.
Conduct Formal Review Cycles, Validate Reports, Consolidate
Them in Production
One of the most effective methods for IT designers to understand
business-reporting requirements is to leverage user-designed reports.
For mission-critical processes, use these as proofs of concept, and
then work with the business to design consolidated, flexible ?super
reports? in a production mode.
Full presentation video here
Paul Turley is a business-intelligence architect and manager for
Hitachi Consulting, and a Microsoft MVP.
Weblog by Paul Turley and SQL Server BI Blog.