What is Business Intelligence?

Business intelligence is a broad set of applications, technologies and knowledge for gathering and analyzing data for the purpose of helping users make better business decisions.
The main challenge of Business Intelligence is to gather and serve organized information regarding all relevant factors that drive the business and enable end-users to access that knowledge easily and efficiently and in effect maximize the success of an organization.
Business intelligence produces analysis and provides in depth knowledge about performance indicators such as company's customers, competitors, business counterparts, economic environment and internal operations to help making effective and good quality business decisions.

From a technical standpoint, the most important areas that Business Intelligence (BI) covers are:
DW - Data warehousing - architecture, modeling, managing, processing
ETL process and data integration
Reporting, Information visualization and Dashboards
OLAP - Online Analytical Processing and multidimensional analysis
Data cleansing and data quality management
Performance management
Data mining, statistical analysis, forecasting
MIS - Management Information Systems
CRM - Customer Relationship Management

Etl Tools Info portal

ETL-Tools.Info portal provides information about different business intelligence tools and datawarehousing solutions, with a main focus on ETL process and tools. On our pages you will find both general articles with high-level information on various Business Intelligence applications and architectures, as well as technical documents, with a low-level description of the presented solutions and detailed tutorials.
A great attention is paid to the Datastage ETL tool and we provide a number of Datastage examples, Datastage tutorials, best practices and resolved problems with real-life examples.
There is also a wide range of information on a rapidly growing Open Source Business Intelligence market (OSBI), with emphasis on applications from the Pentaho BI family, including a Pentaho tutorial.
We also provide a SAS Guide with tutorial, which illustrates the vision of SAS on Business Intelligence, Data Warehousing and ETL process.
We have recently added the ETL case study (ETL and data warehousing course) section which represents a set of business cases, each of which illustrates a typical data warehousing problem followed by sample implementations. We analyze the cases thoroughly and propose the most efficient and appropriate approach to solving that problems by showing sample ETL process designs and DW architectures.
Microsoft users may be very interested in exploring our Excel BI crosstabs section with FAQ and sample solutions.

Data Warehouse – Data Model

Dimensional Modeling Techniques
What is Dimensional Modeling?

• Dimensional Modeling is a logical design technique that seeks to present the data in a standard framework that is intuitive and allows for high performance access.

Strengths of Dimensional Modeling

• Predictable, standard framework   (facts, dimensions)
• Gracefully extendable
• Standard Approaches to Standard Problems
• Easy management of aggregates

Most Important Terminology – Data Warehouse
STAR Schema

- A database design that stores a central fact table surrounded by multiple dimension tables
- Star schema represents a compromise between the fully normalized model and the denormalize
- OLAP Characteristics
- d model

Fact table
-   Contains Keys to dimensions, and measures
-   Measures are typically described as the performance measures of the business
-   Usually numerical, additive and represent counts, currency amounts, percentages or ratios
-    Examples of measures are transaction amounts, balance, count of approved and declined transactions

Dimension table
-    An entity by which the business views the measures (facts)
-   Dimensions are groupings of similar data into a larger category
-   Dimensions may be hierarchical in nature, like Time – days into months, months into quarters
What is OLAP?

• OLAP is an On-line Analytical processing technology which creates new business information from existing data , through a rich set of business transformations and numerical calculations.

- Involves drilling down to lower levels
- Involves roll-ups to higher levels of summarization

ETL Tools

• Informatica
• Data Integrator
• Data Stage
• MS SQL-Server DTS
• Ab Initio
• Data Junction

TOOLS for Front End Analysis (BI tools)

• SAP BusinessObjects
• Cognos
• Web Focus
• Brio
• Hyperion
• Micro Strategy

What is Business Intelligence?

• Business Intelligence is a broad category of application and technologies for:

–         gathering,
–         storing,
–         providing access and
–         analyzing data

to help enterprise users make better, faster  business decisions.
Why Business Intelligence?
–   By definition, the moment any given business is operating, it begins generating data. Some obvious examples are banking, sales, production data, etc.
–  In addition there also exists large volumes of data which are important to the business but not directly generated by business operations. Examples are market data, competitive data, tenders and proposal etc.
–   As such, none of the above described information can be used in its raw form by corporate management to make decisions although the information is critical in helping make those business decisions.
–   Therein lies the necessity for Business Intelligence.
–   BI technologies help bring decision-makers the Data in a form they can quickly digest and apply to their decision making.
–    BI turns Data into Information for people making decisions in a company.
Business Intelligence applications include the activities of:
–    Decision support systems,
–    Query and reporting,
–   Online analytical processing (OLAP),
–    Statistical analysis, Forecasting, and
–    Data Visualization

BI Benefits
–        Make better decisions by turning enterprise data into real information
–        Gain a competitive advantage by getting timely, flexible, sophisticated analysis of corporate data

DATA WAREHOUSING ARCHITECTURE

An analysis and reporting system that covers all the areas an organization requires to support its business decisions at an enterprise level.
Reporting Pressures
Relieve reporting pressure on transactional databases.
Restructure Data
Restructure data to speed up data analysis and reporting capabilities.
Reduce Complexity
Reduce the user complexity associated with generating new reports
Clean Data
Create a repository of “clean data” that does not require wholesale changes to the transactional systems or business processes.
Multiple Source Analysis
Allow easier reporting across multiple transactional systems and external data sources.
Historic Analysis
To provide a data source supporting a longer span of time than can be reasonable supported on the transactional systems.

Data Warehousing Concepts

The IBM Cognos tool, Decision Stream or by it's latest version name Data Manager, is one of the ETL development tools available to support data delivery. Data Manager has all of the functionality needed to extract the data from the source system, create the necessary programming to transform the data, and deliver it to the warehouse.

Extract the source data

Once the BI analyst has worked with the business user to determine the requirements necessary, an ETL programmer begins by writing a SQL statement against the source system(s) to gather all the required elements. It is a best practice to simply pull the data from the source system without attempting to make any changes to it. In the extract process, the best performance will be from a simple select statement. The Decision Stream tool calls this the data stream.

Transformations of extracted data

The temporary work table has all the data elements required to support the transformation step. The transformation step is the "meat and potatoes" of building a structured data warehouse. All of the elements extracted from the source system(s) are used in this step. The Decision Stream tool has derivations, dimensional lookups, and functions, to provide the programmer with all the tools needed to transform the data to meet the business requirements. The tool calls this the transformation model.

A derivation is a calculation type of operation. An example would be adding 19000000 to dates that are in the CYYMMDD format or multiplying the quantity by the unit price to get a total gross price. There are many supported calculation types within a Decision Stream derivation. This include but are not limited to date math, type conversion, contol structures such as if..then..else, and boolean operators.

Dimensional lookups are used to create a link to the necessary control files. An example would be using the transaction date and the transaction currency to include the active foreign exchange rate for the transaction. Dimensional lookups often use the actual value of the key data to bring back the surrogate identification of the record containing the key data in the master or control files.

A function does everything else. Often a function is used at the beginning of the process to get literal or variable data used within the process or at the end to validate the transformations were successful. An example of this would be to go get the beginning and ending date ranges used in the current process, then update those date ranges at the end of the transformation. Generating the total extracted against the total transformed is another common function.

Load the final delivery table(s)

Lastly the delivery module provides the method for the data to be delivered to a table or multiple tables. While this seems like an afterthought, it is important to structure the columns so that the most used columns are at the beginning of the delivery. It is also a best practice to avoid delivering transition data. A field that is only used as a source (the date in CYYMMDD format) should not be delivered to the final table.

Utilizing the IBM Cognos Decision Stream tool gives the ETL developer all the necessary tools to gather, manipulate, and deliver the data needed to meet the reporting needs of the business.

Sources:
IBM Cognos Decision Stream

Ralph Kimball