Microsoft Office Tutorials and References
In Depth Information
Good enough? Still, these OLTP databases are not meant for running analyses. Suppose you have a primary table
consisting of information for one row in four different normalized tables, each having eight rows of information—the
complexity is 1x4x8x8. But what if you’re talking about a million rows? Imagine what might happen to the
performance of this query!
■ the data source for your analysis need not be Oltp. it can be an excel file, a text file, a web service, or
information stored in some other format.
We must emphasize that we are not saying that OLTP doesn’t support analysis. All we are saying is that OLTP
databases are not designed for complex analysis. What you need for that is a nonrelational and nonlive database
where such analysis can be freely run on data to support business intelligence.
To tune your database to the way you need to run analyses on it, you need to do some kind of cleaning and
rearranging of data, which can be done via a process known as Extract, Transform, and Load (ETL). That simply
means data is extracted from the OLTP databases (or any other data sources), transformed or cleaned, and loaded into
a new structure. Then what? What comes next?
The next question to be asked, even if you have ETL, is to what system should the data be extracted, transformed,
and loaded? The answer: It depends! As you’ll see, the answer to lots of things database-related is “It depends!”
Online Analytical Processing System
To analyze your data, what you need is a mechanism that lets you drill down, run an analysis, and understand the
data. Such results can provide tremendous benefits in making key decisions. Moreover, they give you a window that
might display the data in a brand-new way. We already mentioned that the mechanism to pull the intelligence from
your data is BI, but the system to facilitate and drive this mechanism is the OLAP structure, the Online Analytical
The key term in the name is analytical. OLAP systems are read-only (though there can be exceptions) and are
specifically meant for analytical purposes, which facilitates most of the needs of BI. When we say a read-only
database , it’s essentially a backup copy of the real-time OLTP database or, more likely, a partial copy of an entire
In contrast with OLTP, OLAP information is considered historical, which means that though there might be
batch additions to the data, it is not considered up-to-the-second data. Data is completely isolated and is meant for
performing various tasks, such as drill down/up, forecasting, and answering questions like “What are my top five
products,” “Why is a Product A not doing good in Region B.” and so on. Information is stored in fewer tables, and
queries perform much faster because they involve fewer joins.
Olap systems relax normalization rules by not following the third normal form.
Table 1-1 compares OLTP and OLAP systems.
Search JabSto ::