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: Analysis Services Aggregations

By Exforsys | on April 13, 2005 |
MSAS
Pre-calculated summaries of data that improve query response time are called aggregations. OLAP technology organizes data into the multidimensional structures of cubes. The dimensions and their hierarchies define the queries that can be asked of the cubes.

Cells store the value at the intersection of dimension coordinates. Whenever a query is made on the data, the results have to be fetched from the various locations of cells on the multidimensional structure, affecting response time.  Aggregations consist of all the possible combinations of one level from each dimension in the cube. This makes query response time optimal.

However storage and processing time required for aggregations can be considerable. The storage requirements are defined by the number of dimensions, measures, levels and members in the dimension. The tradeoff is between storage requirements and the percentage of possible aggregations that are to be precalculated. If no aggregations are precalculated, storage only for the base data is required. Query response time will also be slow because base data will have to be worked upon each time a query is made. When aggregations are precalculated, other aggregations can quickly be computed from the existing aggregations. This impacts on query response time positively but on storage space negatively.

The Storage Design Wizard(see above)and the Usage Based Optimization Wizard (see below) enable the adjustment of aggregation design for a cube. The former provides options for specifying storage and percentage constraints to an algorithm that helps achieve a satisfactory tradeoff between query response time and storage requirements. The latter assists in aggregation design by analyzing queries that have been submitted by clients and refining the aggregation design accordingly.

Though aggregations are designed using the above said wizards, they get created only when the cube is processed. If the structure of the cube changes subsequent to creating the aggregation and processing the cube, the aggregations will have to be redesigned and the cube processed again.
In the object hierarchy aggregations are subordinate to a specific partition of a cube. If the cube contains only one partition, then the aggregations will be considered to be subordinate to the cube. It is because of this, that the above two wizards will require the selection of a partition if they are run on a multiple partition cube.

The Local cube partition’s aggregations are stored locally if the storage mode is MOLAP or HOLAP, in a subfolder of the Data folder of the Analysis server on which the partition is defined. If the partition was created on a remote Analysis server, then, the aggregations are stored remotely in MOLAP or HOLAP storage modes. In ROLAP storage mode, whether local or remote the partitions aggregations are stored in dedicated tables or indexed views in the database specified in the partition. We will learn more about how aggregations are stored in partitions in the lesson “Managing Partitions”

When programming with Decision Support Objects (DSO), the class type associated with aggregations is clsAggregation.

Managing aggregations in a Dimension

In situations where one or more users of a group need to have access to a dimension that is not required by other members of a group, Analysis services gives the user an option of controlling the way the aggregations of the dimension enter the pool of aggregations. The aggregation options Top Level Only and Bottom Level only can be set in such circumstances. Setting the Top Level only option results in effectively removing the dimension aggregations from the pool. As long as the user does not traverse the hierarchy of the dimension, the values of the dimension will be All Level. and responses for the users not requiring the dimension will be fast.

The Bottom Level only flag has the opposite effect. It prevents the aggregations for a dimension above the lowest level of detail. All users will be forced to pay a performance penalty even when they do not use the dimension.

Aggregation usage property can be set to Custom. This allows the user to disable or enable specific levels within the dimension. Manual control of dimension aggregations is not very efficient. Setting the usage control flags merely controls how levels from the dimension will enter the available pool for consideration by one of the Storage design wizards. Changing the setting has no effect until the wizard is run for designing new aggregations for a cube.

Usage Based optimization

Where the Storage Design Wizard selects aggregations from a pool of potential aggregations, the Usage_based Optimization Wizard uses usage patterns to rank aggregations from within the pool. The queries made by clients are used to define patterns and these patterns are used to select the aggregations from the pool.

Populate the Usage Log

Usage patterns are captured from the query log. The query log is an Access database named Msmdqlog.mdb located in the folder containing the Analysis Services executable files. The server logs one out of ten queries by default. The sampling frequency can be increased if the user needs to add more entries to the log. To change the sampling frequency the user has to change a property of the Analysis server and then the server has to be stopped and restarted.

To start the process right click the Analysis Server in the Analysis Manager and select Properties.  The following dialog box opens.  Click on the Logging Tab to display the following properties

Type 1 in the “Write To Log Once Per_____box. Remove old entries in the log by clicking on the Clear log… button. Confirm the clearing on the popup message dialog box.

Now go to the control panel and double click Services. 

Select MSSQLServerOLAPService in the service list.  Click stop service and then click start service

Switch to Analysis Manager. Right Click the Sales cube and Click Browse data. 

In the window that opens perform some queries by dragging and dropping some dimensions, members etc to add entries to the log. Each manipulation in the browser generates a query in the server. 

Close the browser window.

View Usage Analysis Reports

The reports generated for usage analysis help the user decide what adjustments need to be made to aggregations.

Right click the Sales cube, and click on Usage Analysis to start the Usage Analysis Wizard.

Select Query Run Time Table and Click Next

Notice that there are options for setting criteria.  Do not set any criteria and click Next.  The user can filter the report based on  the date of query, the number of times the query ran and how long it took to run the query or when the user executed it.

 Let us review the report.

To see the graph, the user will have to navigate back two screens and select the Query Response Graph Report and then click twice to see the graph.

Click Finish to close the Usage Optimization wizard.





 

 

 


« « QuickTest Professional 8.0 CBT Tutorial and Evaluation Copy
MSAS: Usage-Based Optimization » »

Author Description

Avatar

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

Free Training

RSSSubscribe 392 Followers
  • Popular
  • Recent
  • Data Warehouse database and OLTP database

    February 25, 2005 - 0 Comment
  • MSAS: Dimension Storage Modes and Levels

    April 3, 2005 - 0 Comment
  • Tutorial 65: MSAS – Managing Cube Roles

    May 5, 2005 - 0 Comment
  • MSAS : Building the Cube Part #1

    March 9, 2005 - 0 Comment
  • MSAS: Usage-Based Optimization

    April 13, 2005 - 0 Comment
  • MSAS : Meta data Repository

    March 23, 2005 - 0 Comment
  • MSAS – Introducing Solve Order

    April 21, 2005 - 0 Comment
  • Microsoft Analysis Services Training

    February 25, 2005 - 0 Comment
  • MSAS: Understanding Hierarchies

    April 3, 2005 - 0 Comment
  • MSAS – Applying security to a Dimension

    May 5, 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
© 2023. 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.AcceptReject Read More
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Non-necessary
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
SAVE & ACCEPT