Business Intelligence
By Peter Worlock
Data is meaningless if you don’t have the means to make sense of it. Peter Worlock finds out what the modern BI solution can offer.
HardCopy Issue: 48 | Found In: Business | Published: 01/05/2010 | Last Revision: 07/07/2010
In the early days of the IT industry, its raw material – information – seemed like a rare and precious commodity, easily managed and packaged in a spreadsheet, a database, a word processed document, an email. But then the floodgates opened. Today, the average organisation is overwhelmed by information. IT systems deliver raging torrents of it, every hour of the day, every day of the year. Business Intelligence (BI) is the industry’s solution to the problem, a way of managing and processing a mountain of raw data so that you can extract the nuggets of meaningful information.
These days, BI systems can deliver a wide range of benefits to their users, that go way beyond standard sales performance analysis. For example, British Airways has attributed the thwarting of a travel agent fraud to its BI system, while high street retailers are reporting reduced losses to customer and employee theft because of BI analysis. Many organisations have been able to improve the effectiveness of their marketing campaigns, implement better risk analysis, and even improve their HR management – to the extent that Google claims it can predict when staff might be thinking of leaving before they know themselves.
The case for BI
In 2007, Gartner Research asked 1,400 CIOs whether management believed it had access to “the right information to run the business”. Nearly two-thirds replied in the negative. A BI solution should deliver that ‘right information’, as well as other benefits, such as:
• Alignment of the organisation around a consistent set of Key Performance Indicators (KPIs) and metrics.
• Quicker, fact-based decision-making with far less reliance on ‘gut feelings’ and anecdotal evidence.
• Simplified graphical presentation of KPIs and metrics, so that goals and performance become easier to understand and measure.
• Reliable and consistent presentation of all information (often referred to as ‘one version of the truth’).
• Combined and separate access to multiple data sources, regardless of department (i.e. sales, marketing, manufacturing, purchasing) or software (i.e. database, spreadsheet, accounts, CRM).
• Faster collection and dissemination of information, allowing the organisation to be more responsive and more agile.
Traditional BI systems tended to be backward-looking, because they were limited to historical data. The latest systems can interact with up-to-the-minute information, revealing the underlying causes of trends and results, and allowing you to construct and explore what-if scenarios.
For many business managers the challenge of data analysis meant relying on the IT department, a process that is often time-consuming and frustrating. The executive asks for a report; after several days IT delivers a version that isn’t exactly what the executive wants or needs; after several revisions the exec gets the right report - but in the meantime the scenario has changed, an opportunity has been lost, or a drama has become a crisis.
Because of that frustration, one of the first BI tools, and still the most widely used, is Microsoft Excel. One industry analyst estimates that there are more than $8 billion of internally-developed business analysis applications with Excel as their front end. Because so many managers use Excel daily, they are able to extract data from other reports for manipulation and analysis, and to use Excel’s charting features to visualise the results.
However the Excel solution leads to other problems. Chief among them is that the spreadsheet is divorced from the rest of the organisation’s IT so there can be major issues of security and data integrity.
So entrenched is Excel that Forrester Research recognised it in a 2007 report. While emphasising the security and integrity issues, the analyst warned that spreadsheets “now play an integral role in all layers of the BI stack”. That reality in turn has led Microsoft to expand BI-specific features in Excel (see below).
BI solutions
Many vendors offer what appear to be standalone BI applications, however it is more useful to think of BI as a set of tools and techniques that include data mining, analysis, querying, reporting and visualisation. It then becomes apparent that while BI is often discussed in terms of very large-scale enterprise systems, even quite small businesses can employ BI technology on a scale appropriate to their own needs.
One of the difficulties in discussing BI solutions is that a variety of tools can be employed, some overlapping and some entirely different. However broadly speaking they include spreadsheets, reporting and querying software, OLAP tools, digital dashboards, data mining, and business performance management tools.
Another approach to BI solutions is to focus on the core functions of BI, which can be boiled down to three categories: data collection and organisation; data analysis; and reporting and visualisation. When you focus on these functional aspects it becomes apparent that not even the largest and most complex BI solutions do everything.
Since BI systems are so complex, a comprehensive buyer’s guide would require an entire book. Instead this guide will focus on the major categories, and some of the leading solutions, to illustrate what you should look for when implementing your own system. Other products worth mentioning include Dundas Dashworks (for .NET development), Toad, QlikView and Pentaho (commercial open source).
Microsoft Excel 2010
As noted, Excel is the de facto leader in BI for analysis, what-if scenarios, reporting and visualisation. Although it has evolved in directions Microsoft perhaps never intended, the company now recognises Excel’s role and is building new BI functionality into the forthcoming upgrade.
The core BI feature is an add-on called PowerPivot that gives access to a wider range of data sources, and the ability to report and analyse more raw data in Excel itself. The program is currently limited to around 64,000 rows of data, but PowerPivot will increase that to hundreds of millions of rows with near-instant response times. Critically, PowerPivot will automatically update spreadsheets when changes are made to the original data sources, overcoming one of the current drawbacks in the use of spreadsheets for BI.
The new version of Microsoft Excel offers many new BI features.
Other new BI features include ‘sparklines’, which are in-cell graphs that provide instant visualisation capabilities; and ‘slicers’, a new kind of filter for pivot tables that adds visual filtering and make it easier to explore large amounts of data interactively.
Collaboration also features highly through new features such as the Excel Web App which allows you to edit workbooks remotely from a Web browser. This allows co-authoring of spreadsheets and adds the ability to publish and share analysis and results through Excel Services Web publishing.
Microsoft’s strategy is to provide business users with instant-access BI features while overcoming many of the shortcomings of the spreadsheet as a BI tool since it allows the IT department to centrally store, secure and distribute Excel spreadsheets throughout the organisation.
Excel may be extremely affordable when compared to many dedicated BI solutions, but it does require a comprehensive upgrade of several Microsoft technologies to unleash the power. You need the Enterprise Edition of SQL Server 2008 R2, plus Office 2010 and the new SharePoint Server 2010 Enterprise CAL. You’ll also need the new storage engine built on the SQL Server Analysis Services OLAP engine, although this will be delivered as a free DLL. (See Kay Ewbank’s article on page 22 for more on SQL Server 2008 R2.)
SAP BusinessObjects
To a large extent, BusinessObjects defined the early BI market, and its central role was only confirmed when the company was acquired by SAP in 2007.
Although BusinessObjects was primarily aimed at very large organisations, coming in a bewildering variety of configurations and add-in packages, SAP has now introduced SAP BusinessObjects Edge, a version specifically designed for smaller and medium-sized businesses. In many respects, this new version is more comparable to Microsoft’s forthcoming solution since it can use Microsoft Office and Sharepoint Server 2007 as key components.
Also like Microsoft, SAP has employed a Web interface to the software, allowing business users to access company data from anywhere. Edge has extensive support for wireless networking, too.
Major BI functionality includes the ability to access and analyse data from any Microsoft Office document, while retaining live links to the underlying data. The software also has built-in reporting, dashboards and visualisation tools. Reporting is particularly strong and includes the ability to publish personalised reports to up to 100 recipients automatically (additional recipients require an additional licence). BusinessObjects Edge also supports ‘report bursting’, a feature that lets you create multiple reports based on different data sources and distribute them individually to a dynamic list of recipients.
Designed to be easy to deploy, easy to use and affordable, BusinessObjects Edge may be the nearest thing to a one-stop BI solution for smaller organisations that have limited IT support.
SAP Crystal Reports
SAP acquired Crystal Reports as part of the BusinessObjects merger since the latter had taken over the former in 2003. As the name suggests, Crystal Reports is a dedicated solution to one part of the BI puzzle: reporting. It lets you design interactive reports linked to virtually any data source including leading databases, spreadsheets (including Microsoft Excel), text and XML files, groupware such as Lotus Notes and Novell GroupWise, or any source accessible through a Web service, ODBC or OLAP.
However, the ‘reporting’ label is something of a misnomer since the latest version goes far beyond reporting and into analysis and visualisation. Interactive features include in-report sorting, reformatting and parameter filtering, as well as the ability to create data presentations. Like Edge, Crystal Reports allows you to create multiple reports from different data sources, combine them into a single file format (such as PDF) and send them to a dynamic list of recipients with a single action.
There is also support for Adobe Flash and Flex, so you can use Adobe Flex Builder to create a customer user interface to access reports and integrate external Web services. Flash SWF files can be integrated into a report for additional interactivity and visualisation. SAP also offers Xcelsius, a dedicated charting and dashboarding solution that features strong Excel interoperability.
Putting BI on the map
Visualisation is an important part of BI, and for many organisations location-based data is a vital component. Whether you’re dealing with retail outlets, different sales territories, manufacturing plants, transport distribution or simply customer movements through a supermarket, geo-location can be critical to understanding. Pentaho has long been using the Google Maps API, for example, while Tableau and QlikView offer options for geographical visualisation.
Tableau allows you to analyse and visualise data in a wide range of formats, including geographical.
Microsoft now has a comprehensive solution to geo visualisation through the interplay between several existing and new tools, including SQL Server 2008 and Bing Maps (the mapping tool in the company’s Bing search engine). SQL Server 2008 includes spatial data types and spatial functions that let you add location capabilities to existing data and to analyse business data in a geographical context. Visual Studio Tools for Office (VSTO) makes it possible to generate a Bing Map to display data, and to embed the resulting map alongside spreadsheet data in an Excel task pane.
Similar functionality can be added to Web portals. Maps and analysis features can be integrated in Microsoft SharePoint Portal Server by combining Bing Maps with data from any source including BI datasets, SharePoint lists and SQL Server itself.
A good introduction to the BI applications of SQL Server and Bing Maps can be found in a free Microsoft white paper entitled “Developing Business Intelligence and Data Visualization Applications with Web Maps”.
Unify Q
For many smaller organisations the key data source will be a database, in which case Unify’s Q application could be the only BI system you need. Unify Q plugs into any Oracle 10g or 11g, SQL Server 2005 and 2008 or ODBC data source and lets you perform queries and generate reports, save results and update records from a single Windows client.
Reports generated can include images, graphs and crosstabs, and can be saved to PDF for publication. Alternatively, data can be exported to Excel, XML or HTML for further processing or sharing.
Datawatch Monarch
The most significant obstacle to any BI implementation is the organisation of the raw data on which the BI software depends. For large organisations seeking a full-service BI system, most of the effort will go into creating, cleaning, de-duplicating and managing the data stores. However, even after all that work, much of the organisation’s data remains in unstructured content – particularly emails, but also existing reports, contracts and other documents.
Adding to the problem are legacy systems that are difficult or impossible to integrate with other software.
Monarch from Datawatch is designed specifically to address the problem. Using patented technology it can extract data from any existing document created within any information system – PDFs, spreadsheets, raw ASCII or HTML files, or virtually any database. Addressing the question of data integrity, Monarch also allows you to embed the original source file.
Once the data has been extracted, Monarch can export to an Excel spreadsheet or Access database for analysis and querying, reporting and visualisation.
There is also Monarch Datapump which provides an automated solution for extracting, formatting and forwarding reports; and the Monarch BI Server and BI Enterprise Servers for large-scale deployment. Datawatch also provides a dedicated Dashboard solution that allows personalised layouts, charts and filters, with automatic installation and simplified administration.
Tableau Desktop
On the subject of visualisation and dashboards, perhaps the most powerful solution available is the eponymous Tableau from Tableau Software. Based on technology developed at Stanford University, Tableau lets you graphically analyse almost any data to produce stunning charts, graphs, dashboards and reports.
Drawing raw data from an Excel spreadsheet or Access database (or an SQL database in the Pro edition), Tableau lets you organise data using colour, shape, size and time. You can work with charts interactively, so that a timeline can be expanded to individual days, or collapsed to weeks, months or years. Tableau also understands geography, so you can generate map-based graphics.
The most recent version is available in a multi-user server edition capable of supporting more than 2,000 users on a five-machine network. Enterprise support includes security features, improved database performance when working with large databases, and Web services that allow users to access and work with data from anywhere, even providing offline functionality.
The right solution
It should be obvious that finding and implementing the right BI solution can range from the comparatively simple to the undeniably complex. The answer depends as much on the nature of your organisation, and your current IT environment, as on the nature of the BI result you want. Among the key questions to consider are:
• What information do your business users need for better decision-making, and how do they want that information presented (i.e. reports or charts, static or interactive, online or hard copy)?
• What data sources do you have and can they provide the right input to a BI system?
• Are you prepared to invest time and money in creating new data warehouses, or would you be better served by an ad-hoc system that can work with the data sources you already have?
• What are your core applications, and will your preferred BI solution work with them?
However choose the right solution and you will have the tools you need to extract real business benefit from the mountains of data that are available to the modern organisation, helping you to discover new opportunities, avoid pitfalls and unearth those all-important trends.