Andyblg's Blog

July 20, 2009

OLAP Solution

Filed under: OLAP — andyblg @ 23:34
Tags: , , ,

Most businesses store data in relational data stores that are optimized for online transaction processing (OLTP) operations. OLTP databases are optimized for applications that need to insert and modify data as well as read data, and are generally normalized to reduce data duplication. This makes it easier to manage data modifications, because data values generally only need to be updated in one place. While it is possible to query an OLTP database to retrieve data for analysis, the normalized design of most relational databases means that the queries required to retrieve the data can be extremely complex, making it difficult for business users to perform their own analytical queries.

OLAP databases are generally denormalized to reduce the number of joins that must be performed when querying the data. This denormalization results in potential duplication of data, but OLAP databases store historical data that does not need to be modified, so this duplication of data is acceptable. Additionally, the data in an OLAP database is usually organized into facts (quantifiable business metrics) and dimensions (aspects of the business by which the facts can be aggregated).

Data warehouses and data marts

In most OLAP solutions, business data is extracted from the production online transaction processing (OLTP) systems, and other sources of business data, and loaded into a denormalized database, where it is optimized for analytical processing. In some scenarios, this denormalized database acts as a central repository for analytical data
from across the organization, in which case it is usually referred to as a data warehouse. Alternatively, an organization might contains a number of discrete departments, each with its own data sources and analysis needs, in which case multiple denormalized databases, called data marts, might exist.

OLAP cubes

Although the data warehouse or data marts can be queried to generate reports or to import data into analytical tools such as Microsoft Office Excel, most OLAP solutions include a multidimensional database that contains one or more cubes based on the OLAP data source. A cube stores the business dimensions and aggregated facts in a form that makes it easy for users to analyze the data by viewing the aggregated values where dimensions and facts intersect. This process is generally known as slicing and dicing the data.

© Microsoft Corporation

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: