Business Intelligence with Smarts

Unfortunately, Business Objects "doesn't do reviews," and though we tried hard to persuade SAS to participate, the vendor said it didn't want to pit its current version against the other products. SAP asked us to include it at the 11th hour, then bowed out without citing a reason.

So we gathered the remaining players and put them to work analyzing our vast store of market data (see "How We Tested Business-Intelligence Solutions"). We used each product to poke and prod our subscriber data, and we discovered that you, our readers, work for companies that spend money on products we evaluate, and that you don't drink enough caffeinated beverages.

Getting To Know You

Actually, we're kidding about the caffeine. The data we analyzed was from CMP Media's CRM system, but the folks at our corporate offices removed large chunks of information before we saw it, to protect you and your company's privacy. We never saw your names, e-mail addresses or phone numbers, and we certainly don't have a clue about what you drink. Rather, Corporate sent us a lot of interesting data that made little sense to most of us until we ran the business-intelligence software.

We evaluated the products based on their methods of report distribution, formats of the analysis presented, data security and mundane configuration and management issues. We also challenged the products to answer two specific questions: one that pertained to interest in Internet and intranet products based on company size and infrastructure spending, and a second that tried to correlate the subscriber's title, industry and IT budget. All five business-intelligence applications provided the same basic results, but their report-development methods and presentation interfaces differ greatly.In the first scenario--correlating concrete numbers, such as IT budgets and infrastructure spending, with interest in Internet/ intranet-related products--we sought a relationship that could show whether people interested in these products came from a company with particular spending patterns.

Indeed, each product generated a report that comprised these variables and concluded that readers from companies with huge IT budgets that spend copious amounts of money on infrastructure are not particularly interested in Internet/intranet products. But those of you who work for technology companies with comparatively small IT budgets and very little spending on infrastructure were extremely interested in these products. We haven't told our marketing people this, though, so if they appear to target you, it's not our fault. Really.

Our second scenario was designed to examine the tools' ability to analyze more nebulous concepts. In this case, we tried to determine whether there is any relationship between a subscriber's job title and the size of his or her company's IT budget, ostensibly to help automate and streamline the subscription consideration process.

Every product pulled together the relevant information and came up with the same answer: No such relationship exists. If our IT staff had been considering such an option at the behest of our marketing department, we could have provided hard data that showed that such an undertaking would not be cost-beneficial. In other words, the result of our analysis was cost-avoidance--something that can benefit every company.

The cliché about the picture and the thousand words holds true for the tools we tested: A business- intelligence solution's means of presenting and distributing information can make or break the product. Most companies have so much data to consider that they often overlook relationships and trends. We spent most of our time evaluating the presentation facet of the products.

We evaluated the reports' formats, as well as how each product delivers those reports. We considered not only the graphical means by which the products present the results of queries--which were impressive for the most part--but also the breadth of formats in which the data could be delivered.All five business-intelligence solutions provide a Web interface to view reports (in HTML), all can generate in an Excel format as well, and all but Microsoft's Data Analyzer can produce PDF-formatted reports. The similarities stop there. Market leaders Cognos and MicroStrategy offer many additional formats to examine data both online and off, including CSV and XML (XML was not available for Cognos when we tested, but the company said it would be by the time you read this). Brio lets you examine data as raw text and images, as well in Lotus 1-2-3. Only Microsoft can generate PowerPoint reports--a capability we'd like other vendors to consider, given the output's intended audience.

The manner in which reports can be displayed does make a difference. Brio's initial report for our first scenario was an impressive 3-D chart that immediately showed exactly which segment of our subscribers was most interested in Internet/intranet products. While generating a report took longer with the other products we tested, we were also equally impressed with the graphing capabilities of all the products.

The Web interfaces of all the products we evaluated rocked. All the tools also let you manipulate reports and data from within a Web browser. Information Builders' WebFocus and Cognos Series 7 have the most complete interfaces; both let you manipulate OLAP (online analytical processing) cubes directly via the Web (see glossary). We could change graphs on the fly from bar charts to pie charts to scatter and plot charts, and we could manipulate the columns displayed in all the products easily.

While Cognos and Information Builders use straight DHTML and scripting to provide a rich Web experience, Brio Intelligence requires ActiveX or native Netscape plug-ins. Microsoft Data Analyzer also requires an ActiveX component, limiting browser support to, of course, Internet Explorer. This is not a huge drawback, as most companies have standardized on Internet Explorer, but we appreciate Cognos' and WebFocus' compatibility with open-source browsers such as Mozilla.

Security and PerformanceWe also considered the products' security and performance. Because of the difficulties inherent in testing performance for these products, our performance evaluations focused on how long it took for a report to be generated. We didn't clock specific times, but during our tests it was obvious that all the products except MicroStrategy are on an even footing in this category. The other products returned results lickety-split, but MicroStrategy took noticeably longer. Performance should not be a huge factor when deciding on a business-intelligence solution, but any tool that requires users to wait for a report for too long will sit unused. For this reason, we kept an eye on this aspect during our product testing.

Security, on the other hand, is a huge issue--not in the omigod-a-hacker-just-got-onto-our-system way, but in ensuring that data access is limited to authorized employees. MicroStrategy provided the most comprehensive security model, offering RBAC (role-based access control) and ACL (access-control lists) to limit access not only to the data but to specific functionality within the product. Cognos Series 7 and Brio Intelligence do an excellent job of addressing this issue, but do not extend their security models to the feature-use level as MicroStrategy does. By comparison, Microsoft's Data Analyzer relies solely on the security provided by a database--which may or may not be active if the data is aggregated from multiple sources in a warehouse.

Pricing

Although pricing was only a small factor in our evaluation--our scenario was based on a small to midsize company, and because package deals were available, cost was less of a factor than it would be in a large organization--it is still important to consider. Microsoft and Cognos use a flat per-user fee. Pricing for the other products depends on the deployment platform of the server as well as the specific number of users and their needs. To put all the participants on even footing, we asked for pricing for a company with 100 users (business analysts), three report developers and one administrator. The products would be deployed on a dual-processor, 1.2-GHz Pentium 4 server running Windows 2000. We also included the vendors' annual maintenance fees, which ranged from 0 (Microsoft) to 25 percent (Cognos).

Depending on your needs, a business-intelligence solution's pricing could range from affordable for even small organizations to prohibitive (and note that IT, not sales or marketing, will pay, because it's an application that requires installation and maintenance). Because most of the vendors license their products on a per-user basis, it's often feasible to start with a small implementation that you can later expand as needed.After careful consideration, we chose Cognos Series 7 as our winner. Series 7 beat out the competition, with top scores for its distribution, analysis and automation of data as well as its excellent data-access and application-access security.

Cognos Series 7 | Information Builders Webfocus 4.3.6 | Brio Intelligence 6.6 | Microstrategy 7i | Microsoft Data Analyzer

Cognos Series 7



The ease with which you can create and navigate through reports with Cognos Series 7 is amazing. With dozens of options and a Web interface whose functions are almost indistinguishable from the desktop client's, this product is the very definition of intuitive and robust. Cognos had us up and analyzing data within an hour.

Cognos uses your LDAP directory for data and application security and is certified for Netscape Directory Server and Microsoft Active Directory Services (we were surprised by the lack of support for Novell eDirectory). This security measure gives Cognos an advantage over the competition, especially Microsoft Data Analyzer and Brio Intelligence, which both depend on native database security to provide access control to data.

We installed a Netscape Directory Server for testing, and then connected the Cognos Server to our SQL 2000 database. Some metadata (table names, column names and descriptions, and relationships) is read from the database and stored locally for better overall performance. Any additional metadata (aliases, renaming of columns and so on) is stored locally on the server. In comparison, MicroStrategy 7i stores and manages metadata in the target database. Based on our tests, the products that store metadata locally perform better.

Cognos makes creating reports a snap and provides robust tools to manipulate the data in report format. The package even lets you change the view of the data--including report layout, data included and chart types--and save that personalized view for future use. No other vendor's product lets you personalize reports. We liked the flexibility this feature offers end users.

Cognos' pricing fell right in the middle of all the the products, at $130,000 for the scenario we used, including a 25 percent annual maintenance fee--the highest fee of any product in this review. Like Microsoft, Cognos offers a flat per-user fee. Such a pricing model is comparatively easy to calculate, and makes it simple to start small and grow as user demand increases.Cognos Series 7, $300 to $500 per user depending on configuration and project parameters. Cognos, (800) 426-4667. http://www.cognos.com

Information Builders Webfocus 4.3.6

Through its partnership with Microsoft, Information Builders provides Web support for Excel that, well, excels. If you're running an all-Microsoft shop with Excel jockeys for business analysts, you'll derive benefits from this program that you can't get from the competition--even Microsoft Data Analyzer. Not only can you view and manipulate reports in Excel format within the WebFocus Web interface, you can drill through data while in an Excel spreadsheet--something unique among the products tested. We were pleased with Information Builders' extensive database and system support, which outpaced the competition. In addition to its exceptionally varied platform support (Linux, Sequent and Tru64 included), WebFocus supports any industry Web server that provides CGI, Servlet and ISAPI. Kudos!

Like Brio Intelligence and Cognos Series 7, Information Builders' WebFocus stores its metadata locally, so its performance was on par with the competition. With out-of-the box reporting functionality that provides KPI (key performance indicator) reports for the insurance, health-care and automotive industries, Information Builders makes getting running quickly a no-brainer.

WebFocus does a good job of notifying you of changes in your data. If a high-profile customer calls, inventory falls below a certain threshold, your profit margin falls below a given level, or you reach your monthly sales target and will be receiving that commission check you've been waiting for, these notification systems can alert you immediately to the event. WebFocus is not unique in this regard, however: Cognos provides "agents" that take the concept a step further by letting you perform actions based on events, such as running additional queries and taking into consideration values that arise from the analysis of additional data.We liked Information Builders' package-deal pricing. With a tag of $67,309 for our scenario, only Microsoft could beat its pricing.

WebFocus 4.3.6, pricing based on configuration. Information Builders, (800) 969-INFO, (212) 736-4433. http://www.informationbuilders.com

Brio Intelligence 6.6

Brio Intelligence ties with Cognos for the best methods of dealing with dirty data. The product also provides an easy-to-use interface that makes report building a breeze. However, limited Web server support (Microsoft IIS and iPlanet only) and a less robust security model than Cognos Series 7's prevented this product from earning our top spot. Web server support is an issue; if an organization has standardized on IBM's WebSphere or BEA WebLogic, for example, the company will need to make an extra purchase to deploy the solution.

Brio Intelligence impressed us with its quick uptime and all-in-one report creation. The drag-and-drop interface let us install, connect to our database and create a detailed analysis within half an hour. We also liked the product's integration with Netegrity, which enables single sign-on access and data security across the Brio product suite. Brio provides access control on its design models, ensuring that developed reports cannot be modified without proper authorization. However, the product still lacks some of the security tools, such as role-based access control, that Cognos Series 7 and others provide. This shortcoming resulted in a lower score in the area of security.Brio Intelligence handled our fairly dirty data without a problem, as did the products from Cognos and MicroStrategy. For example, our database contained several columns that required a simple Boolean value--an X designated a "yes" and an empty string meant "no." Some data, however, was entered as neither an X or empty, but contained other single character values, such as Y for yes. Using grouping functions provided by Brio, we grouped all the tuples (a collection of attributes) that represented a positive value (X's and Y's) into a single "virtual" column.

Normalized data (see "Data: Clean and Normal"), on the other hand, was a problem for both Brio and MicroStrategy. While both solutions eventually dealt with the problem, they entailed much more work than Cognos and Information Builders. Both Brio Intelligence and MicroStrategy required us to create separate "views" to deal with the normalized data. For each duplicate column that used the same lookup table, the products had to create a separate report.

Brio Intelligence 6.6, from $55 for Quickview and $54,995 for full server. Brio Software, (800) 879-2746, (408) 496-7400. http://www.brio.com

Microstrategy 7i

MicroStrategy 7i provided the most extensive RBAC-based security model of the products we tested, but it took a long time to generate reports, unlike the rest of the products. ACLs can be created on a combination of object/report/function/user basis, providing for the most control over data and application access in our tests. We loved the ability to limit access to report functionality and objects (prebuilt queries that represent typical business objects, such as a customer or an order). Other vendors should provide such a high level of security.MicroStrategy, unlike its competitors, stores its metadata in the target database. This means that creating and running reports requires a connection to the database; it also degrades the solution's overall performance noticeably and sucks up CPU resources. The other products ran consistently at less than 20 percent CPU utilization while running reports, but MicroStrategy maxed out our server, with 100 percent CPU utilization while generating reports.

Right out of the box, MicroStrategy includes the interface to its financial modeling language. FML offers specialized functionality and assistance in building reports designed specifically for analysis of financial data. This feature is unique among the products we tested, and lets you create financial reports much more easily than the other products do. MicroStrategy's FML also includes aggregation and functions specifically designed for financial reports that were not found in other products we tested.

Also included out of the box is a series of templated reports for measuring KPI across a number of business functions. Although Cognos and Information Builders include templates and canned reports, neither provides the breadth of coverage offered by MicroStrategy.

MicroStrategy's pricing model is the most confusing and frustrating of the products tested. We understand (but dislike) pricing based on number of CPUs, but server pricing based on the speed of those CPUs is truly undesirable. Oh, and by the way, MicroStrategy has the highest cost to implement our scenario--$210,016, including an 18 percent annual maintenance fee.

MicroStrategy 7i starts at $900 per user not including development software. MicroStrategy, (888) 537-8135, (703) 848-8600. http://www.microstrategy.comMicrosoft Data Analyzer

While Microsoft Data Analyzer integrates with Microsoft Office far more completely than the competition and is an excellent tool for analysis of numerical data, customers should be wary of this inexpensive solution for analyzing data in more complex ways. We liked Data Analyzer's unique ability to create reports in PowerPoint format as well as in Excel. The desktop client provided an intensely graphical means of viewing and analyzing data, but it fell short of the other products in terms of platform and database support. Drawing data from non-Microsoft sources, though possible, is more complicated than with other products. To build cubes to access additional databases, Data Analyzer requires SQL Analysis Services, which requires SQL Server.

The desktop client's filters let you focus on specific aspects of the data set being analyzed. You can filter by any column in the report with a single click or you can get more complex and build a filter based on data in the OLAP cube. Drill-down capabilities are comparable to other products that deal directly with OLAP data, but the cubes must be prebuilt and are static. Once you hit the end of the cube's drill path, you are finished. In comparison, Cognos' solution let us drill down into the database even if the path was not built into the cube.

Data Analyzer requires the use of SQL Analysis Services, and while Analysis Services provides support for OLAP, MOLAP and ROLAP (multidimensional and relational OLAP, respectively), you can't access data directly from Data Analyzer. This means that Data Analyzer cannot easily manipulate data provided by the competition from desktop or Web clients. Data Analyzer's performance was more than acceptable, and while we'd recommend Microsoft's solution for small or midsize shops for regular reporting of financial-based data, we would not be comfortable recommending it for enterprise-class data analysis of CRM or less numerically based data.

We were impressed with Microsoft's no annual maintenance fee and per-user pricing. With a price tag of $18,000 for the scenario we used, a small to midsize organization can incorporate a business-intelligence solution easily.Microsoft Data Analyzer, $170. Microsoft Corp., (800) 426-9400. http://www.microsoft.com

Technology editor Lori MacVittie has been a software developer and a network administrator. Most recently, she was a member of the technical architecture team for a global transportation and logistics organization. Write to her at [email protected].

When we set out to test business-intelligence solutions we began searching for large volumes of data. We managed to obtain more than 2 million CRM (customer-relationship management) records from our corporate headquarters and imported them into a Microsoft SQL Server 2000 database in our Green Bay, Wis., Real-World Labs®. The CRM data consisted of subscriber information from a number of CMP publications--with identifying information about the subscriber (e-mail address, telephone and fax number, last name) removed during the export process.

We cleaned up the data, created all the necessary supported tables and then used that data as the basis for our tests. Each BI product was installed on a Dell Optiplex running Microsoft Windows 2000 Server and used our SQL Server installation as the data source. We evaluated each product based on its architecture, its method of accessing remote data sources and its metadata management.

That enabled us to build and run queries and analyses against an extremely wide data set. Each data set had an average of 600 columns and several hundred thousand records. We evaluated the products in terms of the ease with which reports were built and presented, and we performed a loose performance evaluation while reports were run. There was a huge difference between the products that stored metadata and query information locally versus MicroStrategy 7i, the single product that used a remote data store as its metadata repository.

To evaluate report building, we asked each product to attempt to answer a couple of questions:1 What is the level of interest in Internet/intranet products across industries as it relates to the organization's IT budget and the part of that budget spent on infrastructure products?

2 Is there a relationship between the organization's industry and IT budget and the job title of the subscriber?

We evaluated how the product answered these questions and the ways in which the results were presented--graphs, columnar data and so on--as well as how much dynamic control the business analyst had over the data presentation. Normalizing data is an important part of database design. And cleaning data is critical for effective analysis. Essentially, normalization is the process of removing duplicate tuples (a tuple is a collection of attributes). This procedure reduces database size and helps ensure data integrity. Notice that several cells in the non-normalized chart below contain the same value multiple times. To normalize this data, the values are removed and placed in a separate table'commonly referred to as a lookup table'and then referenced from the original table.

When a data query is performed on the second set of tables, a join must be performed. Data residing in a data warehouse is often non-normalized because of the amount of data stored and the performance degradation resulting from joins across more than one table when dealing with excessively large data sets. The data used in testing was non-normalized.

It was also very, very dirty. But clean data is essential to ensuring that the information a business-intelligence tool generates is valid and useful. An enterprise application-s native database almost never contains a clean data set. Changes from migration, upgrades and day-to-day interaction introduce errors. As an example, imagine a database in whose cells an X is supposed to represent "yes" and blank spaces represent "no." If, instead, "Y" or "N" appears in place of X or the empty cell'a common occurrence'your data is dirty.

R E V I E W

Business Intelligence Software

Sorry,
your browser
is not Java
enabled

Welcome to

NETWORK COMPUTING's Interactive Report Card, v2. To launch it, click on the Interactive Report Card ® icon

above. The program components take a few moments to load.

Once launched, enter your own product feature weights and click the Recalc button. The Interactive Report Card ® will re-sort (and re-grade!) the products based on the new category weights you entered.

Click here for more information about our Interactive Report Card ®.