Logo

Navigation
  • Home
  • Services
    • ERP Solutions
    • Implementation Solutions
    • Support and Maintenance Solutions
    • Custom Solutions
    • Upgrade Solutions
    • Training and Mentoring
    • Web Solutions
    • Production Support
    • Architecture Designing
    • Independent Validation and Testing Services
    • Infrastructure Management
  • Expertise
    • Microsoft Development Expertise
    • Mobile Development
    • SQL Server Database and BI
    • SAP BI, SAP Hana, SAP BO
    • Oracle and BI
    • Oracle RAC
  • Technical Training
    • Learn Data Management
      • Business Intelligence
      • Data Mining
      • Data Modeling
      • Data Warehousing
      • Disaster Recovery
    • Learn Concepts
      • Application Development
      • Client Server
      • Cloud Computing Tutorials
      • Cluster Computing
      • CRM Tutorial
      • EDI Tutorials
      • ERP Tutorials
      • NLP
      • OOPS
      • Concepts
      • SOA Tutorial
      • Supply Chain
      • Technology Trends
      • UML
      • Virtualization
      • Web 2.0
    • Learn Java
      • JavaScript Tutorial
      • JSP Tutorials
      • J2EE
    • Learn Microsoft
      • MSAS
      • ASP.NET
      • ASP.NET 2.0
      • C Sharp
      • MS Project Training
      • Silverlight
      • SQL Server 2005
      • VB.NET 2005
    • Learn Networking
      • Networking
      • Wireless
    • Learn Oracle
      • Oracle 10g
      • PL/SQL
      • Oracle 11g Tutorials
      • Oracle 9i
      • Oracle Apps
    • Learn Programming
      • Ajax Tutorial
      • C Language
      • C++ Tutorials
      • CSS Tutorial
      • CSS3 Tutorial
      • JavaScript Tutorial
      • jQuery Tutorial
      • MainFrame
      • PHP Tutorial
      • VBScript Tutorial
      • XML Tutorial
    • Learn Software Testing
      • Software Testing Types
      • SQA
      • Testing
  • Career Training
    • Career Improvement
      • Career Articles
      • Certification Articles
      • Conflict Management
      • Core Skills
      • Decision Making
      • Entrepreneurship
      • Goal Setting
      • Life Skills
      • Performance Development
      • Personal Excellence
      • Personality Development
      • Problem Solving
      • Relationship Management
      • Self Confidence
      • Self Supervision
      • Social Networking
      • Strategic Planning
      • Time Management
    • Education Help
      • Career Tracks
      • Essay Writing
      • Internship Tips
      • Online Education
      • Scholarships
      • Student Loans
    • Managerial Skills
      • Business Communication
      • Business Networking
      • Facilitator Skills
      • Managing Change
      • Marketing Management
      • Meeting Management
      • Process Management
      • Project Management
      • Project Management Life Cycle
      • Project Management Process
      • Project Risk Management
      • Relationship Management
      • Task Management
      • Team Building
      • Virtual Team Management
    • Essential Life Skills
      • Anger Management
      • Anxiety Management
      • Attitude Development
      • Coaching and Mentoring
      • Emotional Intelligence
      • Stress Management
      • Positive Thinking
    • Communication Skills
      • Conversation Skills
      • Cross Culture Competence
      • English Vocabulary
      • Listening Skills
      • Public Speaking Skills
      • Questioning Skills
    • Soft Skills
      • Assertive Skills
      • Influence Skills
      • Leadership Skills
      • Memory Skills
      • People Skills
      • Presentation Skills
    • Finding a Job
      • Etiquette Tips
      • Group Discussions
      • HR Interviews
      • Interview Notes
      • Job Search Tips
      • Resume Tips
      • Sample Resumes
 

MSAS: The Storage Design Wizard

By Exforsys | on April 12, 2005 |
MSAS
The Storage Design Wizard helps the user set the storage options and design the aggregations for a cube. While choosing from the three available storage modes the user has to keep certain factors in mind.

ROLAP stores aggregations in a relational database and hence it does not make sense to select ROLAP as a storage mode for cubes if the aim is to have speed of performance in analysis. Aggregations in Relational databases are slow and bulky and it defeats the very purpose of creating the aggregations. However, if the user wants to look at the aggregations and understand how they work, then ROLAP is the best storage mode.

MOLAP and HOLAP aggregations are similar. The only difference lies in where the detail level values are stored. MOLAP consumes more space than HOLAP, because the former duplicates the data in the fact table. Queries that use the data will however be efficient. HOLAP on the other hand does not duplicate the data but the queries on the detail data will tend to be slower in comparison. However, processing of a HOLAP cube is faster than the processing of a MOLAP cube.

Storage Design Wizard

In the Analysis Manager tree pane, under the database that contains the cube for which the storage options is to be set and design aggregations, expand the Cubes folder.

Right-click the cube for which storage options is to be set and design aggregations, and then click Design Storage.

If the cube storage design has already been selected the user will be show the screen as under:

The user can replace the existing aggregations or add new aggregations to the existing ones. But he cannot change the storage mode in this screen. The next screen permits him to change the storage mode.

Significantly, a change in the storage mode will make no difference to the client application which only sees the cubes and not the storage types. The user should keep in mind that ROLAP is recommended when resources are not available for MOLAP or the users depend on some functionality of the underlying relational system. If query time is the deciding factor, MOLAP should be used. HOLAP should be used when both processing speed and disk space constraints are in operation.

On clicking Next the user is taken to the Set Aggregations screen. Aggregation design is perhaps the single most important factor that impacts on the times required for processing and querying the cube. Creating the right set of aggregations is a very complex problem and Analysis Services estimates usages statistically. The Storage Design Wizard tries various combinations of aggregations and selects a few specific aggregations as the best choices for the cube. The user has no control over which of the few will be selected. In the screen shot below 46 aggregations have been selected.

The options available in this screen are:

Estimated storage reaches is used to enter the amount of hard disk storage to allocate for storing the aggregation tables. The user can enter a maximum storage size in either megabytes (MB) or gigabytes (GB).

Until I click Stop is used to manually control the balance. Watch the Performance vs. Size graph to determine when the increase in performance levels off, even though storage continues to build.

The Wizard asks the user to specify the percentage amount of performance gain for your queries. This amount represents the percentage improvement between the maximum and minimum query times, as represented by the following formula. PercentGain = 100 * (QTimeMAX – QTimeTARGET) / (QTimeMAX – QTimeMIN). For example, if a query that is not optimized takes twenty-two seconds (QTimeMAX) to execute, and the best possible query performance with maximum aggregations is two seconds (QTimeMIN), specify a 75% desired performance gain to achieve a query time of seven seconds (QTimeTARGET).

Click the Performance Gain Reaches and type 20% as the target performance percentage. Then click Start. When the Next button becomes enabled, Click Next.

The Reset button can be clicked at any time to clear the aggregations.

On the Finish screen of the Storage Design Wizard, Click the Process Now option and Click Finish.

What ever the option selected, Analysis server stores the definition of the aggregations in the OLAP repository. The Storage Design Wizard designs aggregations but does not create them. The aggregations get created only when the cube is processed.

Close the Process Log window after the cube has been processed.

Note that for very large databases with many dimensions, levels, members designing aggregations can take a very long time. A very sophisticated algorithm is executed by Analysis Server behind the scenes. Navigation of hierarchies of a dimension, and combinations of aggregations are examined with the view to optimize performance with minimum storage space requirements. Different partitions of cubes can have different aggregations and will have to be separately designed.

« « MSAS: Analysis Server Cube Storage
QuickTest Professional 8.0 CBT Tutorial and Evaluation Copy » »

Author Description

Avatar

Editorial Team at Exforsys is a team of IT Consulting and Training team led by Chandra Vennapoosa.

Free Training

RSSSubscribe 401 Followers
  • Popular
  • Recent
  • MSAS : The Analysis Manager and Analysis Server

    March 18, 2005 - 0 Comment
  • MSAS – Merging Partitions

    April 19, 2005 - 0 Comment
  • MSAS: Working with Parent-Child Dimensions

    March 31, 2005 - 0 Comment
  • MSAS – Securing User Authentication

    May 2, 2005 - 0 Comment
  • MSAS : Preparing to Create the Cube

    March 7, 2005 - 0 Comment
  • MSAS: Analysis Server Cube Storage

    April 12, 2005 - 0 Comment
  • MSAS : The Wizards

    March 23, 2005 - 0 Comment
  • MSAS – Implementing Calculations Using MDX Part 1

    April 21, 2005 - 0 Comment
  • Introduction to Data Warehousing

    February 25, 2005 - 0 Comment
  • MSAS: Working with Levels and Hierarchies

    April 3, 2005 - 0 Comment
  • MSAS – Browsing the Dependency Network

    May 6, 2005 - 0 Comment
  • MSAS – Building a Relational Decision Tree Model

    May 6, 2005 - 0 Comment
  • MSAS – Introduction to Data Mining

    May 6, 2005 - 0 Comment
  • MSAS – Applying security to a Dimension

    May 5, 2005 - 0 Comment
  • Tutorial 65: MSAS – Managing Cube Roles

    May 5, 2005 - 0 Comment
  • MSAS – Understanding Database Roles

    May 5, 2005 - 0 Comment
  • MSAS – Securing User Authentication

    May 2, 2005 - 0 Comment
  • MSAS – Introducing Analysis Services Security

    May 2, 2005 - 0 Comment
  • MSAS – Writebacks

    April 28, 2005 - 0 Comment
  • MSAS – Defining and Creating Drillthrough

    April 26, 2005 - 0 Comment

Exforsys e-Newsletter

ebook
 

Related Articles

  • MSAS – Browsing the Dependency Network
  • MSAS – Building a Relational Decision Tree Model
  • MSAS – Introduction to Data Mining
  • MSAS – Applying security to a Dimension
  • Tutorial 65: MSAS – Managing Cube Roles

Latest Articles

  • Project Management Techniques
  • Product Development Best Practices
  • Importance of Quality Data Management
  • How to Maximize Quality Assurance
  • Utilizing Effective Quality Assurance Strategies
  • Sitemap
  • Privacy Policy
  • DMCA
  • Trademark Information
  • Contact Us
© 2022. All Rights Reserved.IT Training and Consulting
This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish.Accept Reject Read More
Privacy & Cookies Policy
Necessary Always Enabled