Online analytical processing (OLAP) overview
OLAP (online analytical processing) is a technology that uses multidimensional structures to provide rapid access to data for analysis. OLAP enables analysts, managers, and executives to gain insight into data through fast, consistent, interactive access to a wide variety of possible views of information. OLAP transforms raw data so that it reflects the real dimensionality of the enterprise as understood by the user.
Multidimensional views are inherently representative of an actual business model. Rarely is a business model limited to fewer than three dimensions. Managers typically look at financial data by scenario (for example, actual versus budget), organization, line items, and time; and at sales data by product, geography, channel, and time.
How does OLAP organize its data?
OLAP organizes data into cubes, dimensions, and measures. A cube is a set of data that is organized and summarized into a multidimensional structure defined by a set of dimensions and measures.
A dimension is an organized hierarchy of categories (levels) that describe data in the cube. These categories typically describe a similar set of members upon which the user wants to base an analysis. (You can often recognize a dimension by the use of the word by, such as sales by city or sales by year). For example, a geography dimension might include levels for Country, Region, State or Province, and City. A time dimension may contain the levels Year, Month, and Day.
A measure is a summation of values in the cube and is usually numeric. Measures are the central values that are aggregated and analyzed.
How does OLAP store its data?
The source
data for OLAP is commonly stored in an OLAP database. An OLAP database
is a database specifically structured for query and analysis. An OLAP
database typically contains information that answers questions about
past events. A typical query submitted to an OLAP database is: What
was the total revenue for the eastern region in the third quarter?
Limitations imposed by Microsoft SQL Server
SQL Server limits datetime columns to values equal to or greater than 1/1/1753; therefore, dates between 1/1/1601 and 1/1/1753 are stored as a fixed value of 1/1/1753 UTC.