OLAP

OLAP is an acronym for online analytical processing. It is an approach to quickly provide the answer to complex analytical queries. It is part of broader business intelligence category which also includes Extract transform load, relational reporting and data mining. The typical applications of OLAP are in business reporting for sales, marketing, management reporting, business performance management (BPM), budgeting and forecasting, financial reporting and similar areas.

Databases configured for OLAP employ a Multidimensional database, allowing for complex analytical and ad-hoc queries with a rapid execution time. Nigel Pendse has suggested that an alternative and perhaps more descriptive term to describe the concept of OLAP is Fast Analysis of Shared Multidimensional Information (FASMI). They borrow aspects of navigational databases and hierarchical databases that are speedier than their relational kin.

=Functionality=

OLAP takes a snapshot of a relational database and restructures it into Multidimensional database data. The queries can then be run against this. It has been claimed that for complex queries OLAP can produce an answer in around 0.1% of the time for the same query on relational data.

An OLAP structure created from the operational data is called an OLAP cube. The cube is created from a star schema of tables. At the centre is the fact table which lists the core facts which make up the query. Numerous dimension tables are linked to the fact tables. These tables indicate how the aggregations of relational data can be analysed. The number of possible aggregations is determined by every possible manner in which the original data can be hierarchically linked.

For example a set of customers can be grouped by city, by district or by country; so with 50 cities, 8 districts and two countries there are three hierarchical levels with 60 members. These customers can be considered in relation to products; if there are 250 products with 20 categories, three families and three departments then there are 276 product members. With just these two dimensions there are 16,560 (276 * 60) possible aggregations. As the data considered increases the number of aggregations can quickly total tens of millions or more.

The calculation of the aggregations AND the base data combined make up an OLAP cube, which can potentially contain all the answers to every query which can be answered from the data (as in Gray, Bosworth, Layman, and Pirahesh, 1997.). Due to the potentially large number of aggregations to be calculated, often only a predetermined number are fully calculated while the remainder are solved on demand.

=Types of OLAP=

Beyond the basic concept there are three types of OLAP - Multidimensional OLAP (MOLAP), Relational OLAP (ROLAP), and Hybrid OLAP (HOLAP). MOLAP is the classic form of OLAP and is sometimes referred to as just OLAP. MOLAP uses a summary database that can be thought of as cubes or hypercubes of data. Unlike a relational database, this summary database has been optimised for speed of calculation. The dimensions of each cube are typically attributes such as time period, location, product or account code. The way that each dimension will be aggregrated is defined in advance by one or more hierarchies. ROLAP works directly with relational databases, the base data and the dimension tables are stored as relational tables and new tables are created to hold the aggregated information. Hybrid OLAP uses relational tables to hold base data and multi-dimensional tables to hold the speculative aggregations.

Each type has certain benefits, although there is disagreement about the specifics of the benefits between providers. MOLAP is better on smaller sets of data, it is faster to calculate the aggregations and return answers and does need less storage space. ROLAP is considered more scalable but is the slowest at pre-processing and query performance. HOLAP is between the two in all areas, but it can pre-process quickly and scale well. All types though are prone to database explosion. Database explosion is a phenomenom causing vast amount of storage space being used by OLAP databases when certain but frequent conditions are met: high number of dimensions, pre-calculated results and sparse multidimensional data. The difficulty in implementing OLAP comes in forming the queries, choosing the base data and developing the schema, as a result of which most modern OLAP products come with huge libraries of pre-configured queries. Another problem is in the base data - it must be complete and consistent.

=APIs and query languages=

Unlike relational databases - which had SQL as the standard query language, and wide-spread APIs such as ODBC, JDBC and OLEDB - there is no such unification in the OLAP world. The first real standard API was OLEDB for OLAP specification 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 in the OLAP world.

=Products=

The first product which performed OLAP queries was IRI s Express which was released in 1970 (and acquired by Oracle_Corporation in 1995). However, the term was not invented until 1993 when it was coined by Ted Codd, who has been described as the father of the relational database . But Codd s paper was financed by Arbor, the company which released its own OLAP product - Essbase (now owned by Hyperion) - a year earlier. As a result Codd s twelve laws of online analytical processing were explicit in their reference to Essbase.

Other well known OLAP products include Microsoft Analysis Services (previously called OLAP Services, part of Microsoft SQL Server), International Business Machines s DB2 Cube Views, SAP BW, Information Builders WebFOCUS, Applix, Essbase, Mondrian and products from SAS Institute, Brio, Business Objects_(company), Cognos, MicroStrategy, Sagent, Contour Components, Holos.

There are very few open source olap offerings, for ROLAP reporting there is the java based Mondrian and for MOLAP analysis there is [http://www.palo.net Palo]. Users interested in an open source stack may wish to look at [http://www.pentaho.org/ Pentaho].

Business performance management software plays a major role in the OLAP space.

=External links (non commercial)=

*[http://dev.hyperion.com/resource_library/white_papers/providing_olap_to_user_analysts.pdf Providing OLAP to User-Analysts: An IT Mandate, the Paper by Codd first describing OLAP] *[http://freedatawarehouse.com/tutorials/dmtutorial/Dimensional%20Modeling%20Tutorial.aspx Dimensional Modeling and OLAP Tutorial] *[http://www.daniel-lemire.com/OLAP/index.html Data Warehousing and OLAP: A Research-Oriented Bibliography] *[http://www.olapreport.com/ OLAP Report: In depth overview of all commercial OLAP products] *[http://www.mosha.com/msolap Microsoft OLAP information] *[http://mondrian.sourceforge.net/ Mondrian Java R-OLAP server] *[http://openi.sourceforge.net/ OpenI] *[http://opensourceolap.org/ Palo™ (Memory based OLAP with Excel-Interface)] *[http://www.nongnu.org/lemur/ Lemur OLAP C++ Library] (Experimental GPL library)

=External links (commercial)=

*[http://www.contourcomponents.com ContourCube OLAP components]