Written midterm: OLAP Systems

Microsoft OLAP System:

Data can be stored in Multidimentional structure (Cube) - managed by Microsoft Analysis Services Or in Tabular structure -managed by Microsoft SQL Server

OLAPs are:

Decision support software that allows the user to quickly analyze information that has been summarized into multidimensional views and hierarchies

OLAP stands for...

Online Analytic Processing Systems

Multidimensional Cube Structure for OLAP:

*Detail/leaf level values* - a value at an interaction of all of the dimensions --Stored in Analysis Services or Relational database where data marts are *Aggregate values* - a value formed by combining values from a given dimension or set of dimensions --Stored in Analysis Services or Relational database where data marts are

Examples/non-examples:

--Non-OLAP source—tables, flat files, Excel files, Power Pivot, etc. --OLAP source - such as cubes stored in SSAS—a OLAP solution from Microsoft.

OLAP Cube:

-A method of storing data in a multi-dimensional form, generally for reporting purposes -Data, or measures, are categorized by dimensions. -Data are often pre-summarized across dimensions to drastically improve query time over relational databases.

What is an OLAP?

-A system that stores aggregated information and enables users to quickly and easily retrieve information from data for analysis. -It lets users to work with data interactively: slicing and dicing data in different ways, drilling down into the data to see more details.

An OLAP is a Pre-calculated system: two ways to present data to uses:

-Cubes - Multidimentional model --Measures—numeric quantities representing some performances --Dimensions--categories --Attributes—additional columns in a dimension table --Hiearachies—two or more multilevels of a dimension --Contains detail/leaf values as well as aggregated values -Tables - Tabular model (data are organized in related tables) --Data not organized as measures, dimensions --Analysis Services directly work with tables that hold raw data

Advantages of OLAP Cubes:

-Efficient data storage -Fast data access -Easy data maintenance -Flexibility in data management

Advantage of OLAP Cubes in SSDT:

-rapid analysis of data -Display and sum large amounts of data -Data is still searchable to any data points --easy to roll up, slice, and dice data

OLAP Systems (SSAS)-Cubes:

1. centralized summarization of your data, accessible for different BI tools (SSDT, Excel PowerPivot, PowerView, etc.) 2. Easy to write query to get data from cubes (MDX)

When pivottable works with OLAP Databases,

the OLAP Databases perform calculation and summarization before the data are displayed