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.
On the Front Line: Integrating BI into Custom Applications
A crucial group of users is those on the front line, whether they're on an assembly line or at a point-of-sale kiosk with a customer. These employees typically don't even view reports, much less perform any sort of analysis. These workers access data through tools they use every day, such as a machine they manage or a kiosk that's also a cash register. For workers such as these, integrating data from the warehouse directly into day-to-day operations makes perfect sense; they can use BI without knowing they're doing so, and yet the business achieves great benefits.
In the case of a point-of-sale application, data from the warehouse could be mined to provide suggestions for cross-selling that's far from the standard, "Would you like fries with that?" For example, data mining gives employees the ability to recommend to customers the products most often sold together with what they're purchasing now, based on all past sales. Or, based on a series of questions, a clerk might recommend a product to meet the needs of a customer who isn't sure what he wants. For an employee taking phone orders from existing customers or salespeople in the field, applications could easily show a customer's past sales history and margin compared to the average for all customers.
You can provide this level of custom application integration in a variety of ways. You can embed reports from Reporting Services in applications, users can access data directly by using a data access library, and you can render controls in applications that display data. The integration of the .NET Framework throughout SQL Server 2005 makes it especially powerful for working with custom applications.
Data warehousing is powerful because a single warehouse can support so many different kinds of users through different tools. The same data can be rendered in a variety of ways so that it meets the needs of each user group, thus delivering on the promise of pervasive BI by incorporating accessible data throughout the business.
