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: Creating Custom Rollups

By Exforsys | on April 7, 2005 |
MSAS
To enable proper aggregation of values along a dimension each member of the dimension needs its own aggregation rule. These rules are provided by custom roll up. Custom rollup operators provide a simple way of controlling the process of rolling up a member to its parents values. Custom rollup operators assigned to a column during the process of creating a dimension. The rollup then, uses the contents of the column as custom rollup operator for each member and is used to evaluate the value of the member’s parents.



Custom rollups are enabled when the Unary Operators property of the level is set to True. The values of the Custom Rollup are stored in the Define Unary Operator Column dialog box.

Custom Rollup Operators and Custom Member formulas are very similar but the former is much simpler. Where Custom member formulas use Multidimensional Expressions to determine the method of roll up of members, the custom rollup operator uses simple math to determine how the value of the member affects the parent. The aggregation rule consists of a single character code –(+) for addition and (–) for subtraction,( *)asterisk for multiplication and( /) for division. (~) is used to prevent the members from aggregating at all. These codes are called unary operators because each value gets its own operator.

Custom rollup expressions of a previous level are overridden by custom rollup operators. However, custom member formulas of a preceding level can override the custom rollup members of a level.

Custom rollup operators are enabled both for shared and private dimensions. This can be done by setting the Unary Operators property in the properties pane of the Dimension editor or cube editor. Clicking the Edit button beside this property’s value displays the Define Unary Operator Column dialog box. This dialog box can be used to select or create a column to store the formulas. Once this is set, it is possible to select values for Unary_operator member property for a write enabled, shared, parent child dimension in the custom member formula pane of the Dimension Editor or Dimension Browser. If the dimension is not write enabled the Formulas cannot be inserted in Analysis manager. It must be noted that if a cube has a measure whose Aggregate function property is set to Distinct Count, adding a custom rollup operator or expression to a level will cause the structure of the cube to become invalid.

Custom Rollup Formulas and Custom Member Formulas

Custom Rollup formulas and Custom member formulas are Multidimensional Expressions. They determine the cube cell values associated with members. While a custom rollup formula applies to all members (except calculated members) a custom member applies to a single member. Calculated members are not stored in the dimension table and provide additional members to the dimension table unlike Custom rollup formulas and custom member formulas.

Aggregate functions associated with measures are overridden by custom rollup formulas and custom member formulas. For example a measure uses a Sum aggregate function of the following kind. 

2001: 2100 

         First quarter::500 

         Second quarter :500 

          Third quarter : 100 

          Fourth quarter:1000 

2002: 900 

        First quarter: 100 

        Second quarter: 300 

        Third quarter: 400 

       Fourth quarter: 100

If the custom rollup formula specified is Time.CurrentMember.LastChild the result would be 

2000:1000 

2001: 100

The values for the quarters would remain unchanged.

Custom member formulas perform similar operations but are restricted to single members. The value for the fourth quarter of 2001 member in the Time dimension can be supplied by the formula

Time.[fourth quarter] *1.5

To apply custom rollup formula to only some members of the level, the IIf and RollupChildren functions are used. The RollupChildren function can roll up the children specified, using unary operators specified in the function.

Custom member formulas override custom rollup formulas. Calculated members have to be resolved before custom rollup formulas and custom rollup members are resolved. If a cube has multiple custom rollup formulas and custom rollup members, then the formulas are resolved in the order in which the dimensions have been added to the cube. The order of the dimensions can be viewed and changed using the Cube Editor window.

To specify a custom rollup formula in any level except the All Level, the Custom Rollup Formula property of the level has to be used. To specify it for the All Level the All Member Formula property of the dimension has to be used.

Custom rollup formulas can be specified both in a shared and a private dimension. If a cube contains both a shared and private dimension with custom rollup formulas, then the custom rollup formula of a private dimension takes precedence over a shared dimension’s formula.

Custom member formulas in a level can be enabled using the Custom Members property in the properties pane of the Dimension Editor(shared dimension) or Cube Editor(private dimension). Clicking the ellipsis button beside this property value displays the Define Custom Member Column dialog box in which the formulas can be defined. It must be noted that the formulas can only be created in the custom member formula pane of the Dimension editor or browser only if the dimension is write enabled.

Creating a custom rollup operator for a shared dimension


Access the dimension that will contain the custom rollup operator by right-clicking the dimension, and then click Edit.

In Dimension Editor, in the tree pane, click the level for which to create the custom rollup operator.

If the properties pane is not expanded, expand it by clicking Properties beneath the tree pane.

In the properties pane, click the Advanced tab.

Click the value beside Unary Operators, and then click the edit (…) button.

In the Define Unary Operator Column dialog box, select the Enable Unary Operators check box to enable custom rollup operators for the level.

Create or select an existing column to store the custom rollup operators:


To create a new column in the dimension table, select Create a new column, and then in the New column name box, type the name of the new column.

A dimension table can have multiple columns (one per level) that store custom rollup operators. Therefore, it is recommended that the new column name identify the column that stores the members to which the custom rollup operators apply (that is, the column for the level selected in Step 2.) For example, if in Step 2 you selected the Store Country level, and its members are stored in the store_country column, in the New column name box, type:

store_country_custom_rollup_operator

Note If the dimension is not write-enabled, you must use a tool other than Dimension Editor or Analysis Manager to add values to the new column.

To select an existing column in the dimension table, select Use an existing column, and then in the Existing column box, select the column.

In the Define Unary Operator Column dialog box, click OK.

On the File menu, click Save.  After you perform this procedure once for a level, you do not need to repeat it as long as the column that stores the custom rollup operators remains in the dimension table.

(Optional.) To browse your custom rollup operators, click the Data tab, and then expand the dimension members pane. Custom rollup operators are indicated next to member names. If you created a new, unpopulated column in Step 7, the plus operator (+), which is the default rollup operator, will display beside the member names.

(Optional.) To edit custom rollup operators in a write-enabled parent-child dimension, select a dimension member from the dimension members pane, and then select an operator in the column beside UNARY_OPERATOR in the member properties pane.

After editing, click Save on the File menu to commit changes to the dimension table.
Note Before you can edit the write-enabled dimension, it must be included in a cube, and then the cube must be processed.




Creating a custom rollup operator for a private dimension

  1. Access the cube with the private dimension that will contain the custom rollup operator by right-clicking the cube, and then click Edit.
  2. In Cube Editor, in the tree pane, click the level for which to create the custom rollup operator.
  3. If the properties pane is not expanded, expand it by clicking Properties beneath the tree pane.
  4. In the properties pane, click the Advanced tab.
  5. Click the value beside Unary Operators, and then click the edit (…) button.
  6. In the Define Unary Operator Column dialog box, select the Enable Unary Operators check box to enable custom rollup operators for the level.

Create or select an existing column to store the custom rollup operators:

To create a new column in the dimension table, select Create a new column, and then in the New column name box, type the name of the new column.

A dimension table can have multiple columns (one per level) that store custom rollup operators. Therefore, it is recommended that the new column name identify the column that stores the members to which the custom rollup operators apply (that is, the column for the level selected in Step 2.) For example, if in Step 2 you selected the Store Country level, and its members are stored in the store_country column, in the New column name box, type:

store_country_custom_rollup_operator

Note If the dimension is not write-enabled, you must use a tool other than Dimension Editor or Analysis Manager to add values to the new column.

To select an existing column in the dimension table, select Use an existing column, and then in the Existing column box, select the column.

In the Define Unary Operator Column dialog box, click OK.

On the File menu, click Save.

After you perform this procedure once for a level, you do not need to repeat it as long as the column that stores the custom rollup operators remains in the dimension table.

(Optional.) To browse custom rollup operators stored in an existing column, click the Schema tab, right-click the dimension table containing the stored operator, and then click Browse Data. Custom rollup operators are indicated in the respective column indicated in Step 7 for the first 1000 rows of data.

(Optional.) To edit custom rollup operators of a writable dimension, right-click the dimension in the Cube Editor tree pane and click Browse to display Dimension Browser. In the Dimension Browser tree pane, select the dimension member that has the custom rollup operator that you want changed. Click the value next to UNARY_OPERATORS in the member properties pane to modify the value for the selected member.

Note If a dimension with a custom rollup operator is included in a cube that has a measure where the value of its Aggregate Function property is set to Distinct Count, an error will occur when the cube is saved. This is due to invalid cube structure.











« « MSAS: Creating a Time Dimension
MSAS: Introducing Member Properties » »

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 : 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 – Understanding Database Roles

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

    March 7, 2005 - 0 Comment
  • MSAS: Analysis Services Aggregations

    April 13, 2005 - 0 Comment
  • MSAS : Analysis services Tools for Extended Functionality

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

    April 21, 2005 - 0 Comment
  • Data Warehouse database and OLTP database

    February 25, 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