Skip to main content

SSAS 2008R2 Storage Modes

In SQL Server Analysis Services 2008, we have three storage mode options available to us: Relational Online Analytical Processing (ROLAP), Multidimensional Online Analytical Processing (MOLAP) and Hybrid Online Analytical Processing (HOLAP). 

Relational Online Analytical Processing (ROLAP)

The ROLAP storage mode allows the detail data and aggregations to be stored in the relational database. We have to be very careful on the Database design if we are using the ROLAP as the cube queries data directly from the Database and may cause performance issue if the Database design is not proper.

Benifits
Since the data is kept in the relational database instead of on the OLAP server, we can view the
data in almost real time. Also, since the data is kept in the relational database, it allows for much larger amounts of data, which can mean better scalability. Low latency.

Problems
- Poor query performance as all the data stays in the Database.
- We need to have permanent connection of the cube with the Database so the data can be pulled out from there.

Multidimensional Online Analytical Processing (MOLAP)

With MOLAP storage, the data and aggregations are stored in a multidimensional format, compressed and optimized for performance. Here data is aggregated and stored in the Cube.

Benifits  
- Query Performance will be much faster as aggregates are pre-calculated and stored in the Cube.

Problems 
- Large size data returning queries can cause problem as everything is on memory.
- We need to process the cubes to get the latest updated data.
- Additional amount of data is wasted as one copy of the fact and dimension data resides in the Database and another in the Cube.

Hybrid Online Analytical Processing (HOLAP)

HOLAP is a combination of MOLAP and ROLAP. HOLAP stores the detail data in the relational database but stores the aggregations in multidimensional format. Because of this, the aggregations will need to be processed when changes are occur.

Performance: not as slow as ROLAP, but not as fast as MOLAP. If, however, you were only querying aggregated data or using a cached query, query performance would be similar to MOLAP. But when we need to get that detail data, performance is closer to ROLAP 

Benifits 
- Cube  size is smaller than the MOLAP.
- Query time is faster than ROLAP.
- Process time is smaller than MOLAP.

Problems 
- Query performance can go down if more detailed data required.
 
   

Comments

Post a Comment

Popular posts from this blog

DataZen Syllabus

INTRODUCTION TO DATAZEN PRODUCT ELEMENTS ARCHITECTURE DATAZEN ENTERPRISE SERVER INTRODUCTION SERVER ARCHITECTURE INSTALLATION SECURITY CONTROL PANEL WEB VIEWER SERVER ADMINISTRATION CREATING AND PUBLISHING DASHBOARDS CONNECTING TO DATASOURCES DESIGNER CONFIGURING NAVIGATOR CONFIGURING VISUALIZATION  PUBLISHING DASHBOARD WORKING WITH MAP  WORKING WITH DRILL THROUGH DASHBOARDS
67 comments
Read more

PowerBI Interview Questions and Answers

Power BI Interview Questions – General Questions 1). What is self-service business intelligence? Ans: Self-Service Business Intelligence (SSBI) is an approach to data analytics that enables business users to filter, segment, and, analyse their data, without the in-depth technical knowledge in statistical analysis, business intelligence (BI). SSBI has made it easier for end users to access their data and create various visuals to get better business insights. Anybody who has basic understanding of the data can create reports to build intuitive and shareable dashboards. 2). What are the parts of Microsoft self-service business intelligence solution? Ans: Microsoft has two parts for Self-Service BI  Excel BI Toolkit – It allows users to create interactive report by importing data from different sources and model data according to report requirement.  Power BI – It is the online solution that enables you to share the interactive reports and queries that you have created using ...
7 comments
Read more

MS BI Syllabus

Microsoft Business Intelligence Course Syllabus SSRS – SQL Server Reporting Services  Getting Started 1. Understanding Reporting (Authoring,Management,Delivery) 2. Installing Reporting (Native Mode, SharePoint Integration mode) 3. Building your first report  Authoring Reports 1. Developing Basic Reports (RDL,wizard,designer,datasource,dataset,formatting) 2. Working with expressions (expression to calculate value, Agg functions, exp for objects) 3. Organizing Data (Data Regions, Table, Matrix, Chart, List) 4. Advance Report (Parameter, drill down, drill through, links, 5. Report Model (Data Source, Data Source View, Model , Report Builder 3.0)  Managing Report ( Report Manager) 1. Managing Content (deploying report, folders, linked reports, datasources, value etc) 2. Managing Security (Item Level , Site navigation, localhost – sql) 3. Managing Server Config (Config Manager, Report Manager, Report Server DB)  Delivering Report 1. Accessing Report (Viewing...
30 comments
Read more