Database Reference
In-Depth Information
The OLTP database
Typically, you create a BI solution when business users want to analyze, explore,
and report on their data in an easy and convenient way. The data itself may be
composed of thousands, millions, or even billions of rows, normally kept in a
relational database built to perform a specific business purpose. We refer to this
database as the
On Line Transactional Processing
(
OLTP
) database.
The OLTP database can be a legacy mainframe system, a CRM system, an ERP
system, a general ledger system, or any kind of database that a company uses in
order to manage their business.
Sometimes, the source OLTP may consist of simple flat files generated by processes
running on a host. In such a case, the OLTP is not a real database, but we can still
turn it into one by importing the flat files into a SQL Server database for example.
Therefore, regardless of the specific media used to store the OLTP, we will refer
to it as a database.
Some of the most important and common characteristics of an OLTP system are:
•
The OLTP system is normally a complex piece of software that handles
information and transactions. From our point of view, though, we can
think of it simply as a database.
We do not normally communicate in any way with the application that
manages and populates the data in the OLTP. Our job is that of exporting
data from the OLTP, cleaning it, integrating it with data from other sources,
and loading it into the data warehouse.
•
We cannot make any assumptions, such as a guarantee in the schema
structure over time, about the OLTP database's structure.
Somebody else has built the OLTP system and is probably currently
maintaining it, so its structure may change over time. We do not usually
have the option of changing anything in its structure anyway, so we have to
take the OLTP system "as is" even if we believe that it could be made better.
•
The OLTP may well contain data that does not conform to the general rules
of relational data modeling, such as foreign keys and constraints.
Normally in the OLTP system, you find historical data that is not correct.
This is almost always the case. A system that runs for years very often has
data that is incorrect and never will be corrected.
When building a BI solution we have to clean and fix this data, but normally
it would be too expensive and disruptive to do this for old data in the OLTP
system itself.
Search WWH ::
Custom Search