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 – Applying security to a Dimension

By Exforsys | on May 5, 2005 |
MSAS

When roles are assigned for cubes the user has access or no access to the cube. When roles are assigned to a dimension, the user gains only partial access to parts of the cube. The simplest method of restricting access to a dimension is to prevent access to all levels except the top level of the dimension. In the Cube role dialog box all users were given complete access to the sales cube. Let us restrict the access of users by permitting them to see only values broken out by fiscal years.

In the console tree right click the sales cube and click Manage roles. The cube role manager displays all the roles in the database with a check mark against the roles that apply to this cube.

Select All users role and click the ellipsis button in the restricted Dimensions column

The Edit a cube role dialog box appears. It shows all the dimensions in the cube. The default role is unrestricted. Click on Time and note that a drop down box appears in the Role column. Click on the drop down and select Restricted.

The dimension access can be set to fully restricted, unrestricted or custom. Let us click fully restricted and click Ok to change the dimension rule. Note that the restriction is displayed in the cube role manager dialog box.

If the user is logged in the role he can browse the data by clicking test role button. The time dimension continues to be available. Restricted access does not remove the dimension. If the user clicks on the drop down list of the dimension he will note that only the All time level is available. Restriction has effectively removed all other levels of the dimension, except the top level.

Users can be restricted from viewing certain members of a dimension. Click on the Product dimension in the Dimensions tab of the Cube role Editor. Click on the drop down list in the Rule column and select Custom.

Note that the custom settings column acquires a new ellipsis button. Clicking on this button will enable us to set new restrictions on the dimension.

Click Ok to return to the list of dimensions in the cube. Note that a custom security definition is complex and cannot be displayed in the grid for a role. The description that appears is only a reminder that there is a purpose for the custom definition. Browse the data.

Dimensions can be filtered further to include or exclude individual members from values that are visible to end users.

Click the ellipsis button in the Restricted dimensions column and select Product dimension and click the ellipsis button in the custom settings column. Leave the Select all members option selected and expand the All Product and Diary nodes. Clear the check boxes next to members. The select all option implies that all members which are not specifically excluded will be included.

The advanced tab displays the impact of the choices on the basic tab. Click the advanced tab.

Note that the Advanced tab contains MDX expressions. Selection of values based on the basic tab simply inserts MDX constant expressions into the boxes. The top two boxes can include any MDX expression that returns a valid level of the current dimension. The last two boxes includes MDX expressions that return a valid set for the current dimension. Click Ok to close the custom dialog box and browse the data. The bread category will not appear in the data displayed. However the totals appear wrong as it is calculated for all the values in the cube and hence does not match the values shown in the report. This can be overcome by selecting the enable option in the Common tab of this dialog box.

Now let us examine the Common tab in the Custom Dimension Security dialog box. Under visual totals let us click Enable.

Note that the default is not to show visual totals. Enabling visual totals determines whether displayed, aggregated, cell values are calculated according to only the viewable members, all of the dimensions members or an MDX expression. The totals will now be calculated only for the displayed values.

Often default members are created in a dimension for use as a filter in a dimension that does not have an explicit value selected. The default member for a dimension is placed within the All member level. This kind of definition enables the administrator give each group a default member of its own.

In the Cube Role manager for the sales cube select the All users role and click the ellipsis button in the Restricted dimensions column.

Select the time dimension and then select custom from the Rule drop down list. Then click the ellipsis button to open the Custom Dimension security dialog box. In the common tab select the check box for defining a default member. Note that an MDX expression has to be specified.

Click Ok three times in the screens that follow. The members function retrieves a set of all the members of the Calendar year level, while the Tail function forms a set that consists of only the last member from the level. The Item function extracts the first and the last member of that set. The resultant values are the last member of the Calendar Year level. Click the test role button to test the role.

Cell level security further restricts users from viewing certain cell values. Most often dimension level security is sufficient to ensure that the values of the cells in the restricted dimension are not visible. Yet when the cells need to be secured without removing members in a dimension then, cell level security is useful.

Right click the cube for which you want to apply cell level security and click manage roles. Expand all the levels and click Test role. Browse the data. Note that all the cells contain values. Close the Browser window and Click on the Cells column ellipsis button. The default Cell security policy value is unrestricted read. Select Advanced from the drop down list.

Select custom in the rule column and click on the ellipsis button in the custom settings column. Type a description in the description box. The cell security needs an MDX expression that can be calculated for each cell of a grid so that the value evaluated returns a false and nothing will be displayed to the end user.

Check the syntax by clicking the Check button if required. If the syntax is valid Analysis services says so. Close the message box and proceed.

The Read Contingent and Read/write permission for the role are automatically set to Fully restricted. Close the Edit cube dialog box and browse the data. The restricted cells will have #N/A against their value.

Cell level security is extremely important in write enabled cubes. Different groups of people may modify different cell values causing loss of integrity of data. To enable members to write to a cube the cube must be write enabled.

Analysis services gives a lot of importance to security. The cube role manager provides the administrator with flexibility for applying security. He can set permission at various levels beginning with the server itself and going down to the cell in a cube. Fine tuning of security can be done using MDX expressions.

In the next lesson we shall learn how to create and implement data mining models.

« « Tutorial 65: MSAS – Managing Cube Roles
MSAS – Introduction to Data Mining » »

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
  • MSAS : Designing Storage and Processing the Cube

    March 10, 2005 - 0 Comment
  • MSAS: Optimization Tuning Part 2

    April 14, 2005 - 0 Comment
  • MSAS : Office 2000 OLAP Components

    March 25, 2005 - 0 Comment
  • MSAS – Building a Virtual Cube

    April 24, 2005 - 0 Comment
  • Designing the Dimensional Model and Preparing the data for OLAP

    March 1, 2005 - 0 Comment
  • MSAS: Introducing Member Properties

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

    May 6, 2005 - 0 Comment
  • MSAS : Browsing the Cube

    March 10, 2005 - 0 Comment
  • MSAS – Introducing Dimension and Cube Processing

    April 17, 2005 - 0 Comment
  • Understanding Dimension Basics

    March 29, 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
  • 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
  • MSAS – Defining and Creating Auctions

    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
  • Tutorial 65: MSAS – Managing Cube Roles
  • MSAS – Understanding Database 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