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 – Introducing Solve Order

By Exforsys | on April 21, 2005 |
MSAS
Before understanding what is solve order it is essential to understand the concept of Pass order. Solve order and pass order together determine the manner in which a cube is resolved in Analysis services.

Pass order

Every cube calculated as the result of a multidimensional Expression Query goes through a number of stages of calculations. Analysis server makes a complete pass of the calculations applicable for a stage and the stage is referred to as a calculation pass. The number of passes required to compute fully all the cells of a cube are referred to as the calculation pass depth of a cube. A cube will have at least two calculation passes with at least one to retrieve the data in the cube. The ordinal position of the pass begins at zero and is referred to as pass 0. All calculated members and custom members are calculated on pass 0. The precedence within this calculation is established by the Solve Order of each calculated member.

If a cube has custom rollup formulas or custom rollup operators another calculation pass is made to handle computations for such calculations. The calculations begin with calculation pass 1 and all other calculations succeeding them are determined by calculated cell definitions. The calculation pass number cannot be changed for custom rollup formulas or custom rollup operators. The formula precedence is handled by solve order.

A cube without calculated cells will have at least two calculation passes. The calculated cells will specify the last calculation pass on which the calculated cell definition is calculated and the number of passes with which the calculated cell definition was used. This provides the user with an ability to create cubes that use two or more calculation passes.

Calculation passes are stored in memory to facilitate reference to previous pass values while calculating formulas. This allows for complex calculations such as speculative analysis.

Analysis server determines the number of passes required to compute the value of a cube by evaluating all the custom members, custom rollups, calculated members and calculated cells. Evaluation begins at the highest calculation pass and ends at the lowest calculation pass. This is determined by the CALCULATION_PASS_NUMBER property. The order is reverse when calculating the calculation passes. Each calculation pass is treated as a nested calculation. The lowest calculation pass is usually the most nested.

When calculations overlap in multiple calculations cubes, the solve order of the overlapping calculations is used to resolve the precedence of solution. It is also applied within each calculation pass where multiple calculations are involved.

Solve Order

Solve order determines the order in which dimensions, members, calculated members, custom rollups and calculated cells are evaluated and the order in which they are calculated. The solve order proceeds from the highest to the lowest level. The highest level is evaluated first. This is similar to other nested operations which proceed from the outermost to the innermost nested levels. The order of calculation, however, is reversed. The lowest nested level is calculated first and then the higher levels until the highest level is reached.


Measures occupy a different status. Though they are treated like dimensions, they are evaluated last and calculated first for solve order purposes.

The solve order for calculated members and calculated cells can be changed using the Cube Editor. The solve order property in the Advanced Tab of the properties pane allows the user set the solve order for the calculated cells and members. MDX uses the SOLVE_ORDER member property to create or change the solve order of calculated members and cells.

Solve order impacts upon the results generated by the calculations of dimensions and members. For instance two dimensions with different solve orders would produce different results at the point of interaction depending on the way in which the solve order is applied. For instance is Dimension 2 has a higher solve order when compared to dimension 1 then the solve order adopted would be Dimension 2 first and Dimension 1 next. The calculation of values in Dimension 1 will be done first and the values supplied will be used to calculate the values in Dimension 2. If Dimension1 has a higher solve order, the values in Dimension 2 will be first calculated and the result will be applied for calculating the values in Dimension 1.

The range of Solve order values are -8181 to 65535. It is useful to set positive integers while setting solve orders because solve order for a pass can become unpredictable if the values are used for calculations. For instance, if the custom rollup formula has a negative value, the calculated cells definition is calculated before the custom rollup formula and this causes errors.

When multiple calculations have the same solve order the order of precedence is as under:

  • Calculated cells
  • Custom rollup formulas
  • Custom and calculated members
  • All other calculations

Calculated cells take precedence over all other calculations in the case of solve order conflict. If multiple calculations occur within the same category, the declaration order of the calculation is used.

The pass order together with the solve order can make for very complex issues. This is further complicated when cubes contain a number of dimensions, calculated members, custom rollup formulas and calculated cells. When MDX evaluates a MDX query, everything is given a pass for determining the solve order values. When a query containing calculated members is executed against a cube containing calculated members both solve orders are evaluated as if the query were a part of the cube and the query is executed in the context of the cube. This is made more complex by the fact that the solve order of the dimensions of a cube cannot be reviewed. Moreover the solve order for dimensions of a cube can be changed using the cube editor.

In this lesson we have looked at various methods in which calculations are defined in cubes. We have also briefly examined pass order and solve order and their usage in the calculations in cubes. In the next lesson we shall study Virtual cubes and their features.

« « MSAS – Implementing Calculations Using MDX Part 2
SDLC : Software Development Life Cycle » »

Author Description

Avatar

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

Free Training

RSSSubscribe 394 Followers
  • Popular
  • Recent
  • 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 – Merging Partitions

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

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

    April 3, 2005 - 0 Comment
  • MSAS – Understanding Database Roles

    May 5, 2005 - 0 Comment
  • MSAS : Setting up the Database in Analysis Server

    March 7, 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