OLAP is part of the broader
category of business
intelligence, which also encompasses relational
database, report writing and data
applications of OLAP include business
reporting for sales, marketing,
management reporting, business process management (BPM), budgeting and forecasting, financial
reporting and similar areas, with new applications coming
up, such as agriculture. The
term OLAP was
created as a slight modification of the traditional database term OLTP (Online
consists of three basic analytical operations: consolidation (roll-up),
drill-down, and slicing and dicing.[
databases, which had SQL as the standard query language, and
widespread APIs such as ODBC, JDBC and OLEDB, there was no such unification in the OLAP world
for a long time. The first real standard API was OLE DB for OLAPspecification
from Microsoft which
appeared in 1997 and introduced the MDX query language. Several OLAP vendors – both
server and client – adopted it. In 2001 Microsoft and Hyperion announced the XML for Analysis specification,
which was endorsed by most of the OLAP vendors. Since this also used MDX as a
query language, MDX became the de facto standard. Since
September-2011 LINQ can
be used to query SSAS OLAP cubes from Microsoft .NET
Analytical Processing), has the mission of extracting data for decision making
support process. It is a relatively new technology and in the data warehouse
realm warrants strong attention. Here, according to Gill & Rao, are a few
points that uniquely define OLAP:
- It presents a multidimensional,
logical view of the data in the data warehouse. The view is independent of
the way the data is stored.
- It always involves interactive
query and analysis of the data. The interaction is usually multiple
passes, involving drilling down into successively lower levels of detail
data or roll-ups to higher levels of summarization and aggregation.
- It offers analytical modeling
capabilities, including a calculation engine for deriving ratios,
variances, etc., involving measurements or numerical data across many
- It supports functional models for
forecasting , trend analysis and statistical analysis.
- It retrieves and displays data in
2D or 3D cross-tabs, charts & graphs with easy pivoting of axis (to
see what “pivoting” does, try this feature on a Microsoft Excel
spreadsheet ). Pivoting is key because business users need to analyze the
data from different perspectives; and the analysis of one perspective
leads to business questions to be examined from another perspective.
- It responds to queries quickly,
so the analysis process is not interrupted and the information is not
- It has a multi-dimensional data store
engine, which stores data in arrays. These arrays are a logical
representation of the business dimensions.
In fewer words,
OLAP multiplies the power of the on-line database power-user.
data is stored in a multidimensional database.
Whereas a relational database can be thought of as two-dimensional,
a multidimensional database considers each data attribute (such as product,
geographic sales region, and time period) as a separate “dimension.”
OLAP software can locate the intersection of dimensions (all products sold in
the Eastern region above a certain price during a certain time period) and
display them. Attributes such
as time periods can be broken down into subattributes.
can be used for data mining or the discovery of previously
undiscerned relationships between data items. An OLAP database does not need to
be as large as a data warehouse,
since not all transactional data is needed for trend analysis. Using Open
Database Connectivity (ODBC), data can be
imported from existing relational databases to create a multidimensional
database for OLAP.
leading OLAP products are Hyperion Solution’s Essbase and Oracle’s Express
Server. OLAP products are typically designed for multiple-user environments,
with the cost of thesoftware based
on the number of users.
for Online Analytical Processing, a category of software tools that
provides analysis of data stored in a database.
OLAP tools enable users to analyze different dimensions of multidimensional
data. For example, it provides time series and trend analysis views. OLAP often
is used in data mining.
The chief component of OLAP is the OLAP server,
which sits between a clientand
management systems (DBMS). The OLAP server understands how data is
organized in the database and has special functions for analyzing the data.
There are OLAP servers available for nearly all the major database systems.
of computer processing in which the computer
responds immediately touser requests. Each request is considered
to be a transaction. Automatic
teller machines for banks are an example of transaction processing.
The opposite of transaction processing is batch processing,
in which a batch of requests is stored and then executed all at one time. Transaction
processing requires interaction with a user, whereas batch processing can take
place without a user being present.
New in SQL Server 2014, In-Memory OLTP
is a memory optimized OLTP database engine for SQL Server. (REF)
Used for Online Transactional Processing (OLTP) but can be used
for other purposes such as Data Warehousing. This records the data from the
user for history.
The tables and joins are complex since they are normalized (for
RDMS). This is done to reduce redundant data and to save storage space.
Entity – Relational modeling techniques are used for RDMS database
Optimized for write operation.
Performance is low for analysis queries.
Used for Online Analytical Processing (OLAP). This reads the
historical data for the Users for business decisions.
The Tables and joins are simple since they are de-normalized. This
is done to reduce the response time for analytical queries.
Data – Modeling techniques are used for the Data Warehouse design.
Optimized for read operations.
High performance for analytical queries.
Is usually a
important to note as well that Data Warehouses could be sourced zero to many