Microsoft Office Tutorials and References
In Depth Information
What kind of system could provide the means to answer these questions? A comprehensive business intelligence
system is a powerful mechanism for digging into, analyzing, and reporting on your data.
Business intelligence is all about decisions made effectively with accurate information in a timely manner.
Data mostly has a trend or a paradigm. When you’re looking at the data, you might begin to wonder, “What if....”
To answer this question, you need the business intelligence mechanism. Understanding the basics of BI or
datawarehouse modeling helps you achieve accurate results.
Every industry, organization, enterprise, firm, or even individual has information stored in some format in
databases or files somewhere. Sometimes this data will just be read, and sometimes it needs to be modified and
provide instant results. In such cases, one significant factor is the size of the data. Databases that yield instant results
by adding, editing, or deleting information deal with transactional 1 data. Such information needs a quick turnaround
from the applications. In such cases, users seek or provide information via the UI or another source, and the result of
any subsequent read, publish, edit, or even delete must happen instantly. Transaction results must also be delivered
instantly, with low latency. A system that can deliver such instant results usually is based on the model called Online
Transaction Processing, or just OLTP .
OLTP vs. OLAP
Online Transaction Processing (OLTP) systems are more suitable for handling transactional data and optimized for
performance during Read/Write operations specifically for a faster response. On the other hand, Online Analytical
Processing (OLAP) systems are read-only (though there can be exceptions) and are specifically meant for analytical
purposes. This section explores these two systems in more detail.
Online Transaction Processing System
Data in the OLTP model is relational, and it is normalized according to database standards—such as the third or
fourth normal form. Normalization involves splitting large tables into smaller tables to minimize redundancy and
dependency in data. For example, instead of storing an employee’s department details in the employee table itself,
it would be better to store the same information in a department table and link it to the employee table.
An important factor in the OLTP model is that data doesn’t repeat in any fashion; hence, it is arranged into more
than one table. In this way, transactions involve fewer tables and columns, thus increasing performance. There are
fewer indexes and more joins in this model, and the tables will hold the key information.
Figure 1-2 shows a basic OLTP system.
1 Data related to day-to-day transactions, expected to change on a frequent basis is referred to as transactional data . Examples
include employee payroll data, purchase orders, procurements, and so on. Transactional data is created, updated, and deleted via a
sequence of logically related, indivisible operations called transactions .
Search JabSto ::