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

managed.

These best practices adhere to some basic principles and

experience-borne lessons:

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.