OLAP Cubes

An OLAP cube is a data structure that overcomes limitations of relational databases by providing rapid analysis of data. OLAP cubes can display and sum large amounts of data while also providing users with searchable access to any data points so that the data can be rolled up, sliced, and diced as needed to handle the widest variety of questions that are relevant to a user’s area of interest.

• An OLAP cube is a technology that stores data in an optimized way to provide a quick response to various types of complex queries by using dimensions and measures. 
• Most cubes store pre-aggregates of the measures with its special storage structure to provide quick response to queries.
• SSRS Reports and Excel Power Pivot is used as front end for Reporting and data analysis with SSAS (SQL Server Analysis Services) OLAP Cube.
• SSAS (SQL Server Analysis Services) is Microsoft BI Tool for creating Online Analytical Processing and data mining functionality.
• BIDS (Business Intelligence Development Studio) provides environment for developing your OLAP Cube and Deploy on SQL Server.
• BIDS (Business Intelligence Development Studio) comes with Microsoft SQL Server 2005, 2008 (e.g. Developer, Enterprise Edition) .
• We have to choose OLAP Cube when performance is a key factor, the key decision makers of the company can ask for statistics from the data anytime from your huge database.
• We can perform various types of analysis on data stored in Cube, it is also possible to create data mining structure on this data which can be helpful in forecasting, prediction.

 Diversities between OLTP and OLAP

Online Transaction Processing (OLTP)	Online Analytical Processing (OLAP)
Designed to support Daily DML Operations of your application	Designed to hold historical data for analyses and forecast business needs
Holds daily Latest Transactional Data related to your application	Data is consistent up to the last update that occurred in your Cube
Data stored in normalized format	Data stored in denormalized format
Databases size is usually around 100 MB to 100 GB	Databases size is usually around 100 GB to a few TB
Used by normal users	Used by users who are associated with the decision making process, e.g., Managers, CEO.
CPU, RAM, HDD space requirement is less.	CPU, RAM, HDD space requirement is higher.
Query response may be slower if the amount of data is very large, it can impact the reporting performance.	Query Response is quicker, management can do Trend analysis on their data easily and generate quicker reports.
T-SQL language used for query	MDX is used for querying on OLAP Cube

Creating a OLAP Cube