An OLTP and OLAP designed database serve two different functions and therefore, logically, need to be built with different design objectives.  

An OLTP database is designed for real-time business operations.  These databases are used to take in data instantaneously, update data throughout the day and purge data generally on a daily or weekly basis.  These databases are designed for writing data.  If they are used for reporting, they generally provide a snapshot of the business process it supports.

An OLAP database is designed for storing and reporting on data.  These databases assimilate the data from the OLTP databases.  Typically the data is transformed by applying business rules and historical data is maintained.  Data does not typically get updated once it’s been loaded.   These databases are designed for efficient analytics.  Their primary purpose is to support the Business Intelligence system.

There are numerous theories for designing both types of databases but there are some best practices that are generally followed for OLAP design.  While an OLTP system is highly normalized and contains a plethora of tables, a data warehouse will generally have fewer tables and will be less normalized.  The star and snowflake schemas are two types of design schemas for OLAP database design.  A typical data warehouse will fall somewhere between the two; it won’t be fully normalized yet also not fully de-normalized.   Transactional systems will generally show a very low granular view of data, for example at the transaction level.  The OLAP system is optimized to provide aggregated data over a period of time, like a month or a quarter.  More sophisticated BI systems will deliver predictive information after applying calculations to the summary data.  Where data in the OLTP system uses natural keys and mixes dimensional and factual data in the same table, an OLAP system willcontain separate tables for dimensions and facts and will employ surrogate primary keys.

You can see that these two systems are quite different.  It’s generally considered good practice to design and build a data warehouse specifically to support your Business Intelligence initiatives.  The option of going directly to your OLTP systems for BI will likely cause your reports to be frustratingly slow to return results while they simultaneously slow down the business process they support.  

Below is a table that compares the differences side by side.

I enjoy telling people I’ve just met that I am in “Business Intelligence.”   I watch their face as they try to decipher the term.    They know about the Central Intelligence Agency, they understand Military Intelligence, and of course they know James Bond, but they have never heard of “Business Intelligence.”  I think they envision me running around office buildings in stiletto heels flashing my BI badge and acting cool while I sniff out wrong doers. 

In reality, Business Intelligence is probably the most powerful tool a business can have.   It utilizes applications, technologies, practices and processes in order to provide the information necessary to make competent, fact-based decisions in a timely manner.  In short, it is the thumb on the company’s pulse.   The term Business Intelligence is an umbrella term that includes other umbrella terms; most notably:  Business Analytics, Data Mining, Decision Support, and Enterprise Reporting.  In my opinion this over arching definition can cause confusion.

Frequently, at the core of BI is a Data Warehouse or Data Mart.  These relational databases contain either multiple subject data (data warehouse) or single-subject data (data mart) optimized for data storage (as opposed to supporting a transactional application).  The subjects depend on the business and are typically Sales, Marketing or Production.  A reporting application gathers data from the data base and distributes it through reports, dashboards or scorecards.  Distribution can be any number of venues:  a Web or OLAP application to view and manipulate reports, via an email address, blackberry or printed and manually distributed.
  
A huge value of having a BI program is that the decision makers in the company are receiving the data they need in a timely manner.   I’ve helped companies with legacy systems where they don’t see fiscal year-end data until several months after the end of the fiscal year.  The BI program delivers fiscal year-end data and analysis the day after the year ends or closes.    Fraud prevention obviously benefits from receiving timely results.  By setting thresholds of a normal transaction, when those thresholds are exceeded then the information is delivered immediately and action can be taken. 

Everyone in your organization can benefit from a BI program.   It’s clear to see how Executives benefit by making business-wide decisions with a concise Dashboard, for example, but a very popular trend in the BI industry right now is what’s called Operational Business Intelligence.   This addresses daily operations and delivers reports in real-time or near real-time to all levels of employees to improve business performance.  Information Builders has a very good flash graphic that depicts who uses and benefits from BI. 

In a future blog, I will discuss the steps needed to implement a BI program.   I hope the information I provided here has helped you understand why you’d want to implement BI.  Please comment if you have any questions.