April 16, 2002

Path to Enlightenment

Direct business intelligence can help you avoid data warehousing headaches. But without the proper planning, it offers plenty of its own

By Cindi Howson

Times are tough, and even tougher for IT executives. Budgets have been slashed, and yet, now more than ever, businesses must analyze information to increase customer profitability, lower inventory holding costs, and manage expenses.

Ambivalent about the value of a full-blown data warehouse, some small to midsized companies (and even some large ones) have opted for "direct" business intelligence (BI), in which robust reporting and analytic tools provide direct access to OLTP or ERP systems. (See Figure 1.)

A direct BI implementation can help a company dip its toes into BI without building a full-blown data warehouse, but be careful: Without the right preparation, you can also end up with frustrated users, unfulfilled information needs, and a sluggish transaction system. In this article, I'll describe how to avoid those problems.

Why Direct BI?

If you were to ask an IT executive or a power user who works with direct BI if they would rather be using a data warehouse, the answer would almost certainly be "Yes." Yet, they would also have no immediate plans to implement one. Here's why:

• Timing. A data warehouse may be a long-term goal, but most companies need to achieve some immediate benefits and don't have the time or resources to develop an enterprise information strategy. Furthermore, some companies stumble into direct BI, thinking they're implementing an operational reporting solution for a new ERP system. As they start to use standard BI tools increasingly bundled with ERP systems, the company discovers advanced reporting and analysis capabilities.
• Lack of sponsorship. A successful data warehouse project requires strong business sponsorship and agreement across departments and functions. In contrast, because direct BI is often a limited user base, one department can often sponsor an implementation without having to obtain buy-in from other groups — the politics of a data warehouse project are deferred. The biggest risk here is that usage will spin out of control and that a tidal wave of demand will force companies to implement a data warehouse in a knee-jerk fashion. Whoever sponsors a direct BI implementation must manage expectations: Direct BI is a point solution, not an enterprisewide strategy.
• Cost and complexity. Data warehouse implementations range in price from $50,000 to millions of dollars. Poorly managed projects can take years to achieve measurable benefits, and even well-managed ones will take several months. In contrast, direct BI takes weeks to implement. Small to midsize companies don't have the luxury of drawn-out return on investment (ROI) nor do they have the resources and expertise to take on a full data warehouse project. However, the direct BI approach can only achieve limited ROI and will be negative if expectations and rollout aren't carefully managed. (Can "killer" queries hinder orders from being processed? Absolutely.) Furthermore, as the complexity in reports increases, the total cost of direct BI can soon exceed that of a data warehouse. Companies must carefully watch this curve and recognize that, even if they aren't ready for a full data warehouse, creating a data mart to separate the reporting and transaction environments may be a better investment than complex, direct BI.

Features That Facilitate Direct BI

When BI tools first appeared in the early 1990s, they were well suited for data warehouses but not for normalized OLTP systems. However, as they have added more features to address enterprisewide data warehouses and limitations in SQL, these same features also facilitate direct BI deployments. (See Table 1 (PDF, 20K).)

The safest solution: fixed reports. Most users will have two types of access requirements: fixed reports with prompts in which users can view the same columns of information but may want to change the selection criteria, or ad hoc reports in which the information requests are unique. In a direct BI environment, the more you can limit reporting to fixed reports, the better you can manage query response time and reduce the impact on the source system. The following features can make fixed reports more successful:

• Customizable prompts (items 1 to 3). All the leading tools provide powerful prompting capability in which users can run a fixed report that prompts them for a product ID, for example. The pick list can display a list of valid product IDs along with more meaningful descriptions, sorted in any number of ways. Also, look for tools that have multilevel prompts — for example, on region, first select the country, and if USA is selected, present cities or states located in the USA.
• Report scheduling (items 4 and 5). No matter how much you tweak your fixed report, some queries will still be slow and resource-intensive. These reports are best run during nonpeak hours, with results cached on a midtier server. With the exception of the Cognos Impromptu Windows client and Crystal Decisions Windows client, all the tools in Table 1 (PDF, 20K) provide scheduling capability. Finished reports can be sent to email or a corporate intranet, and there is increasing support for mobile computing devices such as PDAs and cell phones.
• Report bursting (item 6). Because the source system is optimized for inputs (not reporting), users may create queries that do full table scans and bypass indexes. For example, let's say you have two supply chain managers responsible for different regions. One manager needs to view shipments on the East Coast, and the other on the West Coast. Destination state isn't an indexed field. If both managers run a report, filtering on destination region, then two full table scans hit the source system — and two managers wait hours for information.

To minimize the number of full table scans, look for bursting capabilities. With bursting, a user subscribes to a shipment report. The shipment report is run once on the mid tier, but the results are parsed on the report server so the East Coast manager views only her data. Bursting also lowers your administrative costs to create multiple reports.

Ad Hoc Queries: Risky Business

Ad hoc reporting against the source system is something you'll want to limit to a few, well-trained users as the risks outweigh the benefits in a direct BI implementation.

If you do allow ad hoc reporting, forcing users to filter on indexed fields (item 7) can minimize the impact on the source system and improve query response time. Web tools from Brio Software, Business Objects, and Cognos Impromptu are the only ones listed in Table 1 (PDF, 20K) that allow an administrator to define which fields can be used in query filters. As a work-around, some companies have come up with naming conventions or use uppercase names to inform users which fields are indexed.

Aggregate awareness (item 8) may also limit the impact on the source system. Let's say you have a dollar amount on an order header table. Your source system is normalized so this also has an order detail table with individual line items, product IDs, and amounts. If a user only wants to know the customer and dollar amount, then this query can be answered from the order header table — a smaller one, and thus a faster query.

As leading database vendors have provided aggregate awareness in the RDBMS, it's now less important in the BI tool itself. However, not all RDBMSs have this functionality, and your source system may not be set up to take advantage of it because it wasn't designed for reporting. Business Objects is the only tool that has aggregate awareness so that a user need only specify "amount" in the query and Business Objects is smart enough to know when to get the amount from the header or detail table. Ad hoc reporting will also frustrate users with slow and unpredictable response times. The two features that may help minimize user frustration are report streaming (item 9) and time estimates (item 10).

Report streaming means that as soon as the query is done, the BI tool will present the user with the first screen of data, even though it may still be shipping the remaining rows across the network. This approach makes the query appear faster to the user. It is also useful for fixed reports that haven't been scheduled, as well as for ad hoc queries. Crystal Decisions, Impromptu, and Arcplan all provide streaming capabilities.

With ad hoc queries, it's helpful to know if the query you just wrote will take a minute or hours. While it's the database's job to provide a time estimate, the BI tool has to be able to present this estimate to the user before the query is run. Business Objects is the only tool that provides time estimates, but other tools will let an administrator limit rows returned or give row estimates. However, row limits only prevent the client PC or midtier server from being overloaded, not the OLTP system.

Enabling Analysis

Now that you have gone through the complexities of accessing your data from the OLTP system, you need to analyze it and turn it into actionable information. The reporting and analysis features in BI tools enable companies to move beyond basic operational reporting to complex analysis. The most important aspects to look for are online analytic processing (OLAP) (items 11 and 12), prebuilt functions (items 13 to 15), and the ability to integrate data from different sources (item 16).

Small-scale OLAP. The biggest benefit of OLAP tools is that they let users view information from different perspectives such as sales by product, region, and time. If a manager then sees that sales in one region are lower this month than last, the manager can drill down to see why.

• Privacy
• Your California Privacy Rights
• Copyright © 2008 United Business Media LLC