Originally published - June 13, 2006
Economic and regulatory pressures, along with the need to stay competitive in the marketplace, have made business intelligence (BI) more important than ever for enterprise application users. BI gives users the ability to extract, consolidate, change, and analyze data in ways that are not possible in other approaches to enterprise applications. BI also allows users to exploit subsets of data within disparate organizational systems, such as customer relationship management (CRM), enterprise resource planning (ERP), finance, and human resources (HR), to combine various dimensions of organizational data in order to create a single view.
For example, manufacturing and distribution enterprises of all sizes would benefit from leveraging software that not only senses the daily pulse of the operations, but that also spots incongruities, analyzes the performances of multiple areas, and initiates corrective adjustments. BI tools help employees harness data which might be too complicated for manual manipulation. For instance, in departments such as purchasing and sourcing, there are constant and rapid increases in materials costs, deviations in lead times, and growth and instability in the supplier base—all of which require ever increasing buyer dexterity. BI gives organizations the ability to manage these issues proactively.
To build BI solutions within an organization, data warehousing, data integration, analytics, scorecards, and dashboards must also be considered. Each organization has its own use for some (or all) of these tools, depending on how it chooses to use the available tools. We'll look at the main BI components, and at the way BI tools can be applied within an organization.
Contemporary BI Solutions
Contemporary BI solutions enable business users to author, publish, and distribute enterprise reports via a fully integrated report writer, with an easy-to-use report creation wizard. Users can also customize and tailor reports to specific information needs. Report writing and graphing capabilities should enable even nontechnical users to create and share clear representations of complex business conditions. In addition to being easy to use, report writers must also incorporate advanced features like exception filtering and highlighting, calculations with sub-queries, rankings, drill-throughs, and so on.
Nowadays, BI tools generally provide graphical analysis of business information in multidimensional views. Most companies collect a large amount of data from their business operations; to keep track of this information, users require a wide range of software programs, along with more sophisticated database applications for departments throughout their organization. However, using multiple software programs makes it difficult to retrieve information in a timely manner and to perform analysis of the data.
BI represents all the tools and systems that play a key role in the strategic planning process by allowing a company to gather, store, access, and analyze corporate data for decision-making. Generally, these systems assist organizations in customer profiling, customer support, market research, market segmentation, product profitability, statistical analysis, and inventory and distribution analysis, to name only a few.
Data warehousing is a collection of data designed to support management decision-making. A data warehouse (DW) contains a wide variety of data that presents a coherent picture of business conditions at a single point in time. Its purpose is to create a database infrastructure that is always online, that contains all the information from the online transaction processing (OLTP) systems (including historical data), but that is structured in such a way that it is fast and efficient for querying and analysis (as opposed to a database for processing transactions).
Separating these two functions may improve flexibility and performance. The development of a DW includes the development of systems to extract data from underlying transactional operating systems. The DW also installs a warehouse database system that provides managers flexible access to the data. The term data warehousing typically refers to the combination of many different databases across an entire enterprise. This is in contrast to a data mart, which is a database (or collection of databases) designed to help managers make strategic decisions about their business. While a DW combines databases across an entire enterprise, data marts are usually smaller and focus on a particular subject or department, although some data marts, called dependent data marts, can be subsets of larger DWs.
Dimensions of Data Integration
With the advent of data warehousing came the creation of extract, transform, and load (ETL) tools, which use metadata to transfer information from the source systems into the DW. The three functions of ETL combine to pull data out of one database and place it into another:
Extract—the process of reading data from a database.
Transform—the process of converting extracted data from its previous form into a form that can be placed into another database. Transformation relies on rules or lookup tables, or on the combination of data with other data. This allows disparate data sources to be merged, which creates a centralized view of organizational data.
Load—the process of writing the data into the target database or DW.
Again, ETL tools are typically used to migrate data from one database to another, to form data marts and DWs, or to convert databases from one format or type to another. Additional tools, which also make use of structured query language (SQL), have also been developed to give users direct access to the data in the DW. With time, these query tools have become more user-friendly, and many such tools now have a parser (a program that dissects source code so that it can be translated into object code) which can turn natural language questions into valid SQL commands.
Enterprise information integration (EII) is a category of software that confronts the longstanding challenge of enterprise data integration over diverse data sources in scattered enterprise systems. Companies that have overcome the problem of scaling and managing data are now pondering how to unify their data sources and leverage them to solve near real-time business problems. To that end, EII aims to provide unified views of multiple, heterogeneous data through a distributed (“federated”) query. One way to think of EII is as a virtual database layer that allows user applications to access and query data as if it resided in a single database. In other words, the concept takes the existing database capability to merge a query across different tables, but on a virtual basis, shielding users from the underlying complexities of locating, querying, and joining data from varied data source systems.
EII is a fundamentally different approach to such data integration technologies as enterprise application integration (EAI), which provides data or process-level integration, or enterprise portals, which merely integrate data at the presentation level. EAI can be defined as the unrestricted sharing of data and business processes throughout networked applications or data sources.
EII is also different from conventional ETL tools for data warehousing because it neither moves data nor creates new data stores of integrated data. Rather, it leaves data where it is, leveraging metadata repositories across multiple foundation enterprise systems, and visibly pulls information into new applications. As a result, customers may be content to trade in expensive DWs for a data extraction and presentation layer that sits on top of existing transactional systems—but only on the condition that they receive unimpaired performance.
Online analytical processing (OLAP) is a category of software tools that provides analysis of data stored in a database. This enables users to analyze different dimensions of multidimensional data, such as time series and trend analyses. Business users can thus quickly and easily identify performance trends by using time-phased information analysis and graphing capabilities of products that support more sophisticated data analysis and that have calculated field capabilities integrated into reports. For instance, users can quickly isolate and identify products, customers, regions, or other areas that are trending significantly (whether up or down). Some solutions also include a fully-integrated, powerful data-graphing function that enables users to create detailed data visualizations. The graphing capability should ideally be entirely dynamic. In other words, users should be able to rapidly click through various report parameters and see graphical representations for each combination.
OLAP is often used in data mining tools, which is a class of database applications that look in a group of data for hidden patterns that can be used to predict future behavior. For example, data mining can help retail companies find customers with common interests. However, the term is commonly misused to describe software that presents data in new ways. True data mining software does not just change the presentation, but actually discovers previously unknown relationships among the data; this knowledge is then applied to achieving specific business goals. These tools are used to replace or enhance human intelligence by scanning massive storehouses of data to discover meaningful new correlations, patterns, and trends by using pattern recognition technologies and statistics. They are popular in the scientific and mathematical fields, but are also increasingly being used by marketers trying to glean useful client data from their web sites.
Going one step further, predictive analytics is data mining through pattern recognition, along with statistical and mathematical techniques with respect to large amounts of data, in order to support decision-making by forecasting the outcomes of different scenarios. These programs search databases using techniques such as neural networks and decision trees. They look for correlations and patterns that are virtually impossible for humans to detect, and present the information to help management make the right decisions.
Dashboards and Scorecards
Dashboards provide a comprehensive, at-a-glance view of corporate performance thanks to graphical representations, which resemble the dashboard of a car. These graphical representations show performance measures, trends, and exceptions, and integrate information from multiple business areas. They are synonymous with measurements. The centerpiece of any dashboard design is captured metrics, as well as the performance indicators that are combined to form graphs reflecting the health of the business.
Typically, dashboards present a wide number of indicators and associated metrics, arranged in a consolidated view. The visual characteristics of a dashboard are simple intuitive displays, such as dials, gauges, stoplights, charts, and tables. In theory, they are instantly understandable to users, and provide immediate visibility into the well-being of a company's operations and performance. They provide snapshots of daily operations in a single desktop interface, allowing users to pinpoint such problems as inventory or sales levels that violate given thresholds.
Dashboards also enable users to manage their business by using sophisticated tools like key performance indicators (KPIs), scorecards, and other advanced analytics. While a dashboard typically presents an easy-to-understand picture of key metrics at a particular time, scorecards tend to be more dynamic, allowing the greater personalization that might be required by a user's role. Scorecards are lists of financial and operational measurements used to evaluate organizational or supply chain performance. The dimensions of a scorecard might include customer perspectives; business process perspectives; financial perspectives; and innovation and learning perspectives that formally connect overall objectives, strategies, and measurements (since each dimension may have goals and measurements).
To make a clear difference between dashboards and scorecards (the terms are often confused or used interchangeably), a dashboard is a digital cockpit that produces a snapshot of a moment in time, which can be drilled through. Scorecarding is the process of comparing actual results to target results, and of discerning trends over a period of time (which is done by drilling down to reports and operational data sources).
Modern BI Suites
Whether a scorecard or dashboard is implemented as a simple, graphical way to display KPIs, or as a pillar of a comprehensive performance management strategy, it can produce a quick benefit for any company. Thus, modern BI suites should be able to access and present key business measures for sales, customer service, the supply chain, financials, purchasing, inventory, and many other areas. They should also provide the ability to use these information building blocks as the basis for comparisons, calculations, ratios, and metrics. Users should be able to dynamically combine business measures to derive KPIs, such as product profitability, margin analysis, book-to-bill ratios, return on investment (ROI), and other vital metrics.
Typical data that manufacturing enterprises should know about on a daily basis include inventory situation, rejected items, throughput, booked sales, order status, on-time shipments, warranty levels, and so forth. In each of these categories, users might want to investigate numbers and trends to understand the root causes, or to find out what items, regions, channel partners, or customers are involved. In short, this is what BI allows organizations to do successfully.
To recap, BI is an umbrella term that denotes a combination of technologies and architectures. Some important BI tools allowing the storage, access, and analysis of data in DWs and data marts include executive information systems (EIS); ETL tools; reporting, query, and analysis tools; data visualization; balanced scorecards; dashboards; OLAP tools; data mining; and alert and notification systems. It also includes decision support systems (DSS), which is software designed to support groups in unstructured decision-making by supporting brainstorming, conflict resolution, voting, and other techniques. Under the BI umbrella, all these tools are combined, so that BI can transform transactional data into information, information into decisions, and decisions into action.
Some factors for organizations to consider when implementing a new BI toolset or adding to an existing install base include the type of data integration to use, the means of leveraging data warehouses or data marts, and the best type of visualization tools to use to meet an organization's needs.
If an organization requires trend data gathered by consolidating historical data from multiple departments over a period of three to five years, then traditional ETL functionality may be all that is required to load data into a data warehouse for analysis. If monthly or weekly data is required for a specific department, then creating a separate data mart using ETL may be adequate. For organizations that leverage BI tools to perform performance management functions and that need data multiple times a day, it is important to consider an EII tool in order to capture the appropriate data.
As mentioned above, organizations choosing to implement smaller applications across the organization may want to use data marts for each department or individual business units. However, organizations wishing to consolidate organizational data and create a single view of the “truth” may consider using a centralized data warehousing structure and leveraging above-average data integration tools to ensure that data quality and cleansing activities create an accurate view of the organization. Alternatively, data replication or operational data stores can be used to drive right-time BI.
Visualization tools come in the form of dashboards, scorecards, reports, and OLAP cubes. Based on the type of user and the interaction required, the type of tool used will vary. Advanced users can leverage the analysis capabilities provided by OLAP cubes to dissect information and drill down to operational data. Decision makers may want to use a dashboard to get an overall view of the performance of an organization or sales force, and a line manager may want to manage team tasks and employee performance, as well as collaborate with other managers across several regions. The type of data and analysis required will determine how each user takes advantage of the BI tools provided.