Chapter 03: databases and data warehouses: building business intelligence.

•Business intelligence ( BI )
Collective information- about customers , competitors, business partners – that gives you the ability to make effective , important and often strategic business decision.
The science of fact-based decision making. It’s a growing field of study , research and career opportunities that focuses on integrated use of it and statistical techniques to creat real-time , high-quality, fact-based business intelligence in support of decision making.
To obtain BI
We need : first to gather and organize data and information. / Then to have the right IT tools to define and analyze relationships within the collected information. / people ( knowledge workers) use IT tools to creat BI from information. This is what analytics is all about. They help you build and use BI
When working with IT tools ( databases , DBMSs , data warehouse and data-mining tools ) there’s two types of information processing:
1. Online transaction processing ( OLTP )
2. Online analytical processing ( OLAP )
•OLTP ( Online Transaction Processing)
Is the : 1. Gathering of input information;
2. Processing that information; and
3. Updating existing information to reflect the gathered and processed information.
•Operational database
Databases that support OLTP.
• OLAP ( Online Analytical Processing)
The manipulation of information to support decision making.
Data warehouse is a special form of database
That contains information gathered from many operational databases for the support of decision-making tasks.
A collection of information that you organize and access according to the logical structure of that information.
•Relational database model
Uses a series of logically related two-dimensional tables or files to store information in the form of a database
Describes each two-dimensional table or file in the relational model.
Relational databases are composed of two parts :
1) information- stored in a series of 2D tables, files , or relations.
2) logical structure of information.
When we create database
We first create Data dictionary
•Data dictionary
Contains the logical structure for the information
•Primary key
A field ( or group of fields in some cases ) that uniquely describes each record.
• Foreign key
A primary key of one file that appears in other file.
• Integrity constraints
Rules that help ensure the quality of the information
•Database management system ( DBMS )
Helps you specify the logical organization for a database and access and use the information within a database.
DBMS contains five important software components:
-DBMS engine.
– Data definition subsystem
– Data manipulation subsystem
– Application generation subsystem
-Data administration subsystem
•DBMS engine
Accepts logical requests from the various other DBMS subsystem, converts them into their physical equivalent and actually accesses the database and data dictionary as they exist on a storage device.
• Physical view
Deals with how information is physically arranged, stored and accessed on some type of storage device such as a hard disk.
• logical view
Focuses on how you ( as a knowledge worker ) arrange and and access information to meet your particular business needs.
• Data definition subsystem
Helps you create and maintain the data dictionary and define the structure of the files in a database
• Data manipulation subsystem
Helps you add, change , and delete information in a database and query it for valuable information. ( tools here include views , report generator, QBE and SQL ).
• View
A window from it you can see the contents of a database file , make changes, delete, sort information, or query for information.
• Report Generator
Helps you quickly define formats of reports and what information you want to see in a report.
•Query-by-example ( QBE ) tool
Helps you graphically design the answer to a question.
• Structured Query Language ( SQL )
A standardized fourth-generation language found in most DBMSs
The basic structure of an SQL statement
Select …
From ….
Where ….
• Application Generation Subsystem
Contains facilities to help you develop transaction-intensive application.
• Data administration subsystem
A DBMS helps you manage the overall database environment by providing ((facilities)) to do :
•Backup and recovery;
~ periodically backup information contained in a database.
~ restart or recover a database and its information in case of failure.
• Security management;
Controls who has access to what information and what type of access those people have.
• Query optimization; take queries from users and restructure them to minimize response times.
• reorganization; continually maintain statistics concerning how the DBMS engine physically accesses information.
• concurrency control; ensure the validity of database updates when multiple users attempt to access and change the same information.
• change management; allows you to assess the impact of proposed structural changes to a database.
• Data warehouse
A logical collection of information- gathered from many different operational databases- used to create business intelligence that supports business analysis activities and decision-making tasks
Data warehouses
Support OLAP
• Data mining tools
Software tools you use to query information in a data warehouse. These tools include:
• Query-and-reporting tools ; similar to QBE tools , SQL , and report generators in the typical database environment.
• Intelligent agents ; use various artificial intelligence tools to form the basis of information discovery and building business intelligence in OLAP.
• Multidimensional analysis ( MDA ) ; slice-and-dice techniques that allow you to view multidimensional information from different perspectives.
• Statistical tools ; help you apply various mathematical models to the information stored in a data warehouse to discover new information.
Data marts
Smaller data warehouses
• Data mart
A subset of a data warehouse in which only a focused portion of the data warehouse information is kept.
•Chief information officer ( CIO )
Responsible for overseeing an organization’s information resource.
• Data administration
Plans for , oversees the development of, and monitors the information resource.
• Database administration
Responsible for the more technical and operational aspects of managing the information contained in organizational databases.