Database management ch 7 Business Intelligence

ETL tools

Data extraction, transformation, and loading (ETL) tools collect, filter, integrate, and aggregate internal and external data to be saved into a data store optimized for decision support.

Query and reporting

This component performs data selection and retrieval, and it is used by the data analyst to create queries that access the database and create the required reports. Depending on the implementation, the query and reporting tool accesses the operational database, or more commonly, the data store.

Data visualization

This component presents data to the end user in a variety of meaningful and innovative ways. This tool helps the end user select the most appropriate presentation format, such as summary reports, maps, pie or bar graphs, mixed graphs, and static or interactive dashboards.

Key performance indicators (KPIs)

are quantifiable numeric or scale-based measurements that assess the company's effectiveness or success in reaching its strategic and operational goals.

To speed data access, data cubes are normally held in memory in the cube cache.

cube cache.

Data visualization

is abstracting data to provide information in a visual format that enhances the user's ability to effectively comprehend the meaning of the data.

The precursor of the modern BI environment was the first-generation decision support system. A decision support system (DSS)

is an arrangement of computerized tools used to assist managerial decision making. A DSS typically has a much narrower focus and reach than a BI solution.

Sparsity

measures the density of the data held in the data cube; it is computed by dividing the total number of actual values in the cube by its total number of cells.

Relational online analytical processing (ROLAP)

provides OLAP functionality by using relational databases and familiar relational query tools to store and analyze multidimensional data.

Multidimensional data analysis techniques are augmented by the following functions:

Advanced data presentation functions. Advanced data aggregation, consolidation, and classification functions. Advanced computational functions Advanced data-modeling functions.

A modern BI system provides three distinctive reporting styles:

Advanced reporting. Monitoring and alerting. Advanced data analytics.

In general, BI provides a framework for:

Collecting and storing operational data Aggregating the operational data into decision support data Analyzing decision support data to generate information Presenting such information to the end user to support business decisions Making business decisions, which in turn generate more data that is collected, stored, and so on (restarting the process) Monitoring results to evaluate outcomes of the business decisions, which again provides more data to be collected, stored, and so on Predicting future behaviors and outcomes with a high degree of accuracy

Dashboards and business activity monitoring

Dashboards use web-based technologies to present key business performance indicators or information in a single integrated view, generally using graphics that are clear, concise, and easy to understand.

Some examples of KPIs are:

General. Year-to-year measurements of profit by line of business, same-store sales, product turnovers, product recalls, sales by promotion, and sales by employee Finance. Earnings per share, profit margin, revenue per employee, percentage of sales to account receivables, and assets to sales Human resources. Applicants to job openings, employee turnover, and employee longevity Education. Graduation rates, number of incoming freshmen, student retention rates, publication rates, and teaching evaluation scores

Online analytical processing (OLAP) is a BI style whose systems share three main characteristics:

Multidimensional data analysis techniques Advanced database support Easy-to-use end-user interfaces

OLAP tools

Online analytical processing provides multidimensional data analysis.

Portals

Portals provide a unified, single point of entry for information distribution. Portals are a web-based technology that use a web browser to integrate data from multiple sources into a single webpage. Many different types of BI functionality can be accessed through a portal.

Data store

The data store is optimized for decision support and is generally represented by a data warehouse or a data mart. The data is stored in structures that are optimized for data analysis and query speed.

Data warehouses (DW)

The data warehouse is the foundation of a BI infrastructure. Data is captured from the production system and placed in the DW on a near real-time basis. BIprovides company-wide integration of data and the capability to respond to business issues in a timely manner.

see figure 7.1

The general BI framework depicted in Figure 7.1 has six basic components that encompass the functionality required on most current-generation BI systems.

Conceptually, MDBMS end users visualize the stored data as a three-dimensional cube known as

a data cube.

Multidimensional online analytical processing (MOLAP)

extends OLAP functionality to multidimensional database management systems (MDBMSs).

Master data management (MDM)

is a collection of concepts, techniques, and processes for the proper identification, definition, and management of data elements within an organization. MDM's main goal is to provide a comprehensive and consistent definition of all data within an organization.

Data monitoring and alerting

This component allows real-time monitoring of business activities. The BI system will present the concise information in a single integrated view for the data analyst. This integrated view could include specific metrics about the system performance or activities, such as number of orders placed in the last four hours, number of customer complaints by product by month, and total revenue by region. Alerts can be placed on a given metric; once the value of a metric goes below or above a certain baseline, the system will perform a given action, such as emailing shop floor managers, presenting visual alerts, or starting an application.

Data analytics

This component performs data analysis and data-mining tasks using the data in the data store. This tool advises the user as to which data analysis tool to select and how to build a reliable business data model. Business models are generated by special algorithms that identify and enhance the understanding of business situations and problems. Data analysis can be either explanatory or predictive. Explanatory analysis uses the existing data in the data store to discover relationships and their types, and predictive analysis creates statistical models of the data that allow predictions of future values and events.

The most distinctive characteristic of modern OLAP tools is their capacity for multidimensional analysis, in which data is processed and viewed as part of a multidimensional structure.

This type of data analysis is particularly attractive to business decision makers because they tend to view business data as being related to other business data.

Governance

is a method or process of government.

Business intelligence (BI)1

is a term that describes a comprehensive, cohesive, and integrated set of tools and processes used to capture, collect, integrate, store, and analyze data with the purpose of generating and presenting information to support business decision making.

Relational versus Multidimensional OLAP

see table 7.6!!!!

Data analysis and reporting tools

These advanced tools are used to query multiple and diverse data sources to create integrated reports.

Data-mining tools

These tools provide advanced statistical analysis to uncover problems and opportunities hidden within business data.

Data visualization

These tools provide advanced visual analysis and techniques to enhance understanding and create additional insight of business data and its true meaning.