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
