The challenge of delivering data from a data warehouse or
data mart to end users can be daunting. When I present data-warehousing talks, people often ask me, "But after you build
it, how do you give users access to it?" Giving users access to
the data they need is what turns a data warehouse into business
intelligence (BI). Even in the best warehouse, data is meaningless
unless users can quickly access it and easily understand it. Many
options exist for delivering BI data to end users, and the methods
you choose depend entirely on the end users and how they'll
work with the data.
Fortunately, a variety of tools support the data in the warehouse, so many different types of users can have their BI needs
met with different tools that all access the same warehouse. This
article describes four types of users and the tools that you can use
to deliver data to them. Although this list isn't a comprehensive
inventory of available tools, you can use it as a practical guide
for examining the needs of your users and selecting appropriate
tools. It doesn't matter whether your organization is using SQL
Server 2005 or 2000; all these tools are available for either
version.
Aiming High: Tools for Executives
Executives and business decision makers such as vice presidents,
directors, and other high-level personnel have decision-making
authority over a department, division, or the entire organization. Because these people have a heavy workload and need to take
a broader view of the business, they typically have a limited
bandwidth for performing their own analysis. Instead, they seek
a simple view of data at a high level that they can use to direct
employees to delve deeper if problems are apparent. Business
decision makers tend to realize the most benefit from scorecards
and dashboards.
Scorecards come in two different types: balanced scorecards
and general business scorecards. Scorecards contain elements that
provide information about key performance indicators (KPIs)
and they typically display a status and trend for each KPI. KPIs
are the metrics that decision makers use to drive the business,
and they provide information about such business areas as sales,
returns, defect rates, customer retention, gross profit margin, and
employee turnover. Each KPI has a value and, typically, a goal; the
comparison of the value to the goal gives you the KPI's status.
For example, the value for a customer-retention KPI might be
80 percent. Is this good or bad? The only way to know is to
compare the KPI to a goal. If the goal was 90 percent, then 80
percent might be considered bad. A scorecard typically displays
status as a simple graphic, indicating that something is good, bad,
or indifferent, so that busy executives can glance at the scorecard
and immediately understand the health of the organization.
Figure 1, shows a simple scorecard that was created with
the Microsoft Office Business Scorecard Manager 2005.
Dashboards are related to scorecards;
in fact, most scorecards are part of a larger
dashboard. Dashboards offer more detailed
information than scorecards and can be customized for different people. For example,
the Vice President of Manufacturing might
need to review KPIs for products per hours,
defect rates, and material costs. The Director
of Human Resources might track KPIs
for employee turnover, salary costs, and
EEOC compliance. So, whereas a dashboard
might include a scorecard, it also includes
more targeted information. In addition, most dashboards also include more detailed
reports that may or may not be interactive.
Microsoft SharePoint Portal Server and
Windows SharePoint Server both work
well as dashboards. You can also find many
other dashboards, such as the ProClarity
Dashboard Server that Figure 2 shows or
similar products from Panorama, Information Builders, and other vendors.
BI to the Masses
Most employees in an organization don't
need to perform complex data analysis. Instead, they need
to be able to access
detailed data by using
fairly simple tools. For
this largest group of
users, your best datadelivery method is
often through reports
in simple grid or
graphic formats.
Reports can be static
or include some interactivity, such as the
ability to drill down
to see additional data
or click a number and
launch a report containing more detail.
Vendors such as ProClarity (which Microsoft acquired in 2006), Panorama, and others
provide the ability to create reports against
Analysis Services. But do careful research
because some products still don't support the
new features in Analysis Services 2005.
SQL Server Reporting Services is an
obvious report-delivery choice that has
several advantages over other options. First,
it comes with SQL Server and has the ability
to display data from both Analysis Services
cubes and relational tables. Reporting Services also includes the ability to create
reports that have regions that can collapse
and expand, showing a simplified view that
users can expand if necessary. Reports can
also be linked so that clicking a number,
for example, lets you drill down to a level
of greater detail. Reporting Services also
includes the ability to export reports in
a variety of formats including Microsoft
Excel, which provides additional benefits to
organizations that depend on Excel spreadsheets for analysis and formatted printing.
Figure 3 shows a report that was created in
Reporting Services.
Another advantage of Reporting Services is the Report Builder that Microsoft
released with Reporting Services 2005.
Report Builder lets end users create their
own reports against either a relational
schema or Analysis Services cube, which
broadens the base of report creators beyond
just developers using Visual Studio.
Analytic Tools: Unlimited Access
for Analysts
Data analysts are a special group of users
who understand the nature of cubes and
need limitless abilities to slice and dice data.
For their complex analyses, analysts might
build mathematical and statistical models
in the course of their work. Therefore, they
need a tool that lets them explore data
using all the dimensions and measures in
a cube while supporting advanced features
such as actions, drill through, perspectives,
and KPIs. Although they're only a small
percentage of users, analysts push the full
capabilities of data cubes and often end up
creating reports for the majority of end users
and performing detailed analysis for upper
management.
Microsoft's answer to an analytical tool is usually Excel, and Excel Pivot Tables are
the most common way warehouse data is
viewed. However, Excel 2003 doesn't support all of Analysis Services 2005's features;
Excel 2007 does. You can improve Excel
2003 by installing the Excel 2002/2003 AddIn for SQL Server Analysis Services, a free
tool from Microsoft that adds better support
for cubes. (You can download the tool from
the Microsoft downloads page at http://www.microsoft.com/downloads/details.aspx?FamilyId=DAE82128-9F21-475D88A4-4B6E6C069FF0&displaylang=en.)
Even with the improvements in Excel
2007, you might decide to look at any of
several third-party products for performing
complex cube analysis. Products from companies such as ProClarity, Panorama, Cognos,
and Business Objects all provide advanced
analytic capabilities. Be sure the product you
choose supports the Analysis Services 2005
features that your BI solution needs. You can
see an example of a ProClarity data visualization in Figure 4.
Prev. page
[1]
2
next page 
