A review of new software products that can make a big difference to your business

Second survey of business intelligence systems* 

By Michael Burns

*This is an expanded version of a column that originally appeared in the June-July 2005 issue of CAmagazine.

Most organizations don’t get the information they want from their existing systems. Some get their IT department to write a report, which usually takes too long. Others create spreadsheets that let users do exactly what they want. But errors can slip into spreadsheets through rekeying or calculation mistakes. To make matters worse, spreadsheets are not usually shared across an organization and they are not updated as things change. So decisions are made with old data.

That’s where BI tools come in. They are supposed to take the mechanics out of the process. Analysts don’t need to fiddle with the spreadsheet or wait for the IT department. With BI, analysts can slice and dice data any way they want. Since the BI information is all stored on a central server rather than on one user’s desktop, BI tools should also provide just one version of the truth.

The BI world is full of technical terms, such as extract, transform & load (ETL), data warehouse, cubes and online analytical processing (OLAP).  This may explain why the technology has not done well in organizations without an IT department. But what does it all mean? First, data must be extracted, usually from multiple sources, and transformed (cleaned up) for consistency and accuracy. Then it is loaded into a data warehouse that stores the data in a logical way. ETL can account for 50% of the total cost of a BI implementation. The data is then placed in “cubes” that are designed to optimize retrieval of information using online analytical processing (OLAP). It is possible to create these cubes with tools from BI vendors or from databases such as Microsoft SQL Server. With an OLAP cube, you can interactively slice and dice the data across multiple dimensions and drill down for more detail either in a spreadsheet or graphic view of the data.

One way to think about business intelligence is to consider reporting needs by level within an organization.

Operations need the traditional/preformatted reports. Middle management wants to slice and dice across multiple dimensions using OLAP. Middle management will also want the flexibility to look at information in new or creative ways to analyse a particular problem, and need ad hoc querying, i.e.,  the ability to get information out of the system without relying on a programmer. At the top of the reporting pyramid is senior management, who may just want a dashboard that summarizes the critical information on one page. Depending on the organization, many may want to drill down for more detail. Same goes for middle management.

It’s also true that operations may want some of the reporting tools at higher levels, but there are licence and training/support costs associated with providing these tools especially if it’s OLAP. As well, perhaps operations should be more focused on the day-to-day operations, rather than potentially spinning their wheels analyzing information.

Vendor survey
The accompanying survey [click here] includes responses from many of the leading BI systems – including Microsoft, Cognos, Hyperion, Panorama, Clarity, SunSystems, Applix and Actuate. We asked a variety of questions about their cost, user base, target market and technology, as well as more than 80 questions related to BI functionality.

This year’s survey includes several new features, including a section where vendors list the top five reasons to purchase their software, and another section on target market by industry. In the charts, you’ll see a lot of the products do the basics. What makes them different? Cost and average number of users, among other things. BI vendors also vary in their offerings. Some provide budgeting, forecasting and consolidation in what is referred to as business (or corporate or enterprise) performance management. The need to comply with Sarbanes-Oxley has turned business performance management (BPM) into the technology du jour.

You also need to take a good look at the technology section. You can’t go wrong if you just need support for Microsoft SQL Server, but you may find some of the products don’t support other databases. The same applies to the operating system/network: Microsoft is a sure bet but the others may be a problem.

The survey includes seven responses from Microsoft for their various ERP solutions. According to Nancy Teixeira, ERP product manager, Microsoft Canada, BI can be achieved through ERP. In fact, there are distinct advantages to working with a one-stop shop that offers BI and ERP from the same vendor. Advantages include having one repository for data, enabling companies to move away from information silos spread across multiple systems.

Microsoft has been creative in responding to the survey using various reporting tools, which differ across systems. Microsoft’s financial report writer, FRx, is partly used for both Great Plains and Solomon. As well, hot off the press this year, is what Teixera calls the business Intelligence layer for Great Plains. Great Plains now includes analysis cubes for Excel, that were acquired from Professional Advantage, a Microsoft Business Solutions partner. The system delivers BI across an organization by using OLAP cubes for more in-depth analysis. With Navision and Axapta, Microsoft has taken another approach in offering what is called business analytics -- a BI tool developed by TARGIT, another Microsoft Business Solutions partner. Other BI tools include Microsoft SQL Server Reporting Services, which was launched in 2004 and competes with products such as Crystal Reports.

Microsoft also brings a platform for BI that includes extract, transform and load (SQL Server Data Transformation Services), a data warehouse (Microsoft SQL Server), OLAP (SQL Server Analysis Services) and reporting (SQL Server Reporting Services). What makes all this so compelling is that it all comes with Microsoft SQL Server. Microsoft is also gearing up for the release of SQL Server 2005, which is targeted for the second half of 2005. The new version includes many new BI enhancements, including an end-user ad hoc reporting environment.

Challenges
At Gartner Inc.'s Business Intelligence Summit in London recently, analysts warned that although BI offers great business opportunities, most enterprises still don’t use it strategically. Gartner's leading BI analysts highlighted a number of major flaws inherent in the technology:

Flaw 1: If we build it, they will come. Too many IT departments build a data warehouse based on the assumption that once it is built, users will automatically see the benefit.
Flaw 2: Reliance on spreadsheets. Too many people hide behind spreadsheets because they are used to them and because they know how to manipulate the numbers to satisfy the politics of their organizations.

Flaw 3: Data quality. More than 50% of data warehouse projects will have limited acceptance or fail completely because data quality has been ignored. Many organizations don’t even see they have an issue with data quality, focusing rather on identifying, extracting and loading data.

According to Alex Resnick, president of the Catalytics Group, part of the problem with BI today is that analysis is not linked back to strategy . Alex sees business users struggling to get data from core transactional systems and using spreadsheets to meet their needs. Data is extracted in a variety of ways, often merely by keying the data from printed reports, and seldom fully reconciled back to the core systems after being manipulated to provide the desired analytical views. Although Alex works with one of the BI solutions called Panorama, he does not think spreadsheets need to be eliminated.

Resnick suggests using a database such as Access or Microsoft SQL Server to store the data. This eliminates the need to save multiple copies of a spreadsheet to reflect snapshot data at different points in time, and overcomes the challenges associated with changes to historical data that then cause the snapshot-type spreadsheets to be incorrect. Good spreadsheet design should also include the use of pivot tables rather than duplication of data through the use of tabs or additional spreadsheets. Alex speaks about hitting the wall of spreadsheet functionality when the spreadsheets are too complex, there’s too much data, or you need to restrict access to certain dimensions. This is when you need to select an appropriate BI tool.

Bottom line
BI continues to offer accountants a big opportunity to help their organizations and clients better analyse their operations. It’s time to get on the BI bandwagon – preferably in the driver’s seat. It’s not just about number crunching. You can help reduce the risk of BI errors. You can ensure the systems are effective and efficient, and not too dependent on one person. Most important, you can make sure BI is answering the right questions.

Michael Burns, MBA, CA, is president of 180 Systems (http://www.180systems.com/), which provides independent consulting advice including business process review, business case development and system selection. Michael can be reached at 416-485-2200 or by email at mburns@180systems.com