Online Analytical Processing (OLAP) / Online Transactional Processing (OLTP)

What

OLAP databases facilitate business-intelligence queries.
OLAP is a database technology that has been optimized for querying and reporting, instead of processing transactions.
The source data for OLAP is Online Transactional Processing (OLTP) databases that are commonly stored in data warehouses.
OLAP data is derived from this historical data, and aggregated into structures that permit sophisticated analysis.
OLAP data is a sophisticated technology that uses multidimensional structures to provide rapid access to data for analysis. It is organized hierarchically and stored in cubes instead of tables.
OLAP databases contain two basic types of data:
- measures, which are numeric data, the quantities and averages that you use to make informed business decisions, and
- dimensions, which are the categories that you use to organize these measures.
OLAP databases help organize data by many levels of detail, using the same categories that you are familiar with to analyze the data.

A data structure that aggregates the measures by the levels and hierarchies of each of the dimensions that you want to analyze.
Cubes combine several dimensions, such as time, geography, and product lines, with summarized data, such as sales or inventory figures.
Cubes are not “cubes” in the strictly mathematical sense because they do not necessarily have equal sides. However, they are an apt metaphor for a complex concept.

A set of values in a cube that are based on a column in the cube’s fact table and that are usually numeric values.
Measures are the central values in the cube that are preprocessed, aggregated, and analyzed.
Common examples include sales, profits, revenues, and costs.

An item in a hierarchy representing one or more occurrences of data. A member can be either unique or nonunique.
For example, 2007 and 2008 represent unique members in the year level of a time dimension, whereas January represents nonunique members in the month level because there can be more than one January in the time dimension if it contains data for more than one year.

A member of a dimension whose value is calculated at run time by using an expression.
Calculated member values may be derived from other members’ values.
For example, a calculated member, Profit, can be determined by subtracting the value of the member, Costs, from the value of the member, Sales.

A set of one or more organized hierarchies of levels in a cube that a user understands and uses as the base for data analysis.
For example, a geography dimension might include levels for Country/Region, State/Province, and City.
Or, a time dimension might include a hierarchy with levels for year, quarter, month, and day.
In a PivotTable report or PivotChart report, each hierarchy becomes a set of fields that you can expand and collapse to reveal lower or higher levels.

A logical tree structure that organizes the members of a dimension such that each member has one parent member and zero or more child members.
A child is a member in the next lower level in a hierarchy that is directly related to the current member.
- For example, in a Time hierarchy containing the levels Quarter, Month, and Day, January is a child of Qtr1.
A parent is a member in the next higher level in a hierarchy that is directly related to the current member. The parent value is usually a consolidation of the values of all of its children.
- For example, in a Time hierarchy that contains the levels Quarter, Month, and Day, Qtr1 is the parent of January.

Within a hierarchy, data can be organized into lower and higher levels of detail, such as Year, Quarter, Month, and Day levels in a Time hierarchy.