Dundas Data Visualization Support Site
Dundas Support Site Home  |  Contact Us  |  Dundas Data Visualization  
Contact Us via Email
Home

Restricting Access To Data In OLAP Services

 

Q. How can I restrict access to certain data in OLAP Services?

A. Analysis Services provides an extensive and secure way of controlling access to data. It is also possible to selectively hide particular elements of the schema through code using the Dundas OLAP Services API.



Table of Contents

  1. In SQL Server Analysis Services
  2. In Dundas OLAP Services
  3. Programmatically creating, deleting, and selecting SSAS Security Roles





In SQL Server Analysis Services

In your Analysis Services data cube, set up roles and user access rights to data however you like. (See SQL Server's documentation for details: "Granting User Access".) Keep in mind that Analysis Services uses only Windows Authentication to grant access to users logging in.

In ASP.NET, the ASP.NET identity then determines what can be accessed. Here are two ways to work with this:

  • You can set rights to the ASP.NET user account itself (by default, the ASPNET or Network Service local account.)
  • Or, you can set up impersonation so that ASP.NET runs with a different identity. Optionally, you can use credentials supplied by the individual user to connect to the database.

For a general overview of connecting to Analysis Services, including security, see our article here.



In Dundas OLAP Services

It is also possible to programmatically set the Visible property on individual dimensions, hierarchies, levels and members in OLAP Services. If you are using ASP.NET, upgrade to version 5.5 for this ability.

  1. First, we must capture the OlapManager.DataSchemaChanged event at design-time or an equivalent. If you are using the OlapChart with a separate OlapManager component, go to the OlapManager's Properties window in the designer and double-click the DataSchemaChanged event to create an event handler.

    If you are using an OlapClient, you can set an event handler for the DataSchemaChanged event in code:
    • In Windows Forms, insert the following code at the beginning of your Form_Load event
    • In ASP.NET, go to the OlapClient's Properties window in the designer and capture the Init event.
    [C#]
    // In Form_Load or OlapClient_Init:
    OlapClient1.OlapManager.DataSchemaChanged += new Dundas.Olap.Manager.OlapManager.DataSchemaChangedEventHandler(OlapManager_DataSchemaChanged);
    [VB.NET]
    ' In Form_Load or OlapClient_Init:
    AddHandler OlapClient1.OlapManager.DataSchemaChanged, AddressOf OlapManager_DataSchemaChanged
  2. During the DataSchemaChanged event, we can execute code to set whatever we want to be invisible. Import the Dundas.Olap.Data namespace using the "using" keyword in C# or "Imports" in VB.NET. Here is a simple example, where the member named "WA" in the Customer dimension is hidden:
    [C#]
    protected void OlapManager_DataSchemaChanged(object sender, EventArgs e)
    {
        CubeDataSchema schema = OlapClient1.OlapManager.GetDataSchema();
        schema.FindMemberByName("WA", false).Visible = false;
    }
    [VB.NET]
    Protected  Sub OlapManager_DataSchemaChanged(ByVal sender As Object, ByVal e As EventArgs)
        Dim schema As CubeDataSchema =  OlapClient1.OlapManager.GetDataSchema() 
        schema.FindMemberByName("WA", False).Visible = False
    End Sub

    By disabling a member's Visible property, it cannot be seen when drilling down or choosing members in popup dialogs, and its data is not included in the data for hierarchy levels above it.


Dimensions, hierarchies, levels and members

  • Each dimension can have multiple hierarchies, although often only one. The graph above shows the hierarchy for a "Location" dimension.
  • Each row in the graph represents a level in the hierarchy: "Location", "Continent" and "Country."
  • Each level contains members: the Location level contains a single member "Location", the Continent level contains "North America" and "Europe", and the Country level contains members "CAN", "USA", "FRA", and "ITL." Members in lower hierarchy levels have parent members, e.g., "CAN" and "USA" have "North America" as their parent member.
  • Measures are used in combination with dimensions to describe data. For example, when combined with the Location dimension representing the Europe, the measure Sales Amount produces data representing the sales amount for Europe.
  • For more conceptual information about OLAP data, see "Basics of Multidimensional Data" in the OLAP Services documentation.

Here is a more complex example that sets invisible a measure, dimension, levels and members. This code should be used in the DataSchemaChanged event:

[C#]
CubeDataSchema schema = OlapClient1.OlapManager.GetDataSchema();

Measure warehouseCostMeasure = schema.Measures.FindByName("Warehouse Cost");
Dimension warehouseDimension = schema.Dimensions.FindByName("Warehouse");

// Set Warehouse Cost measure invisible
if (warehouseCostMeasure != null)
{
    warehouseCostMeasure.Visible = false;
}
// Set Warehouse dimension invisible
if (warehouseDimension != null)
{
    warehouseDimension.Visible = false;
}

foreach (Dimension dimension in schema.Dimensions)
{
    foreach (Hierarchy hierarchy in dimension.Hierarchies)
    {
        foreach(Level level in hierarchy.Levels)
        {
            // Hide all "Country" levels
            if (level.Name.Contains("Country"))
            {
                level.Visible = false;
            }
            
            // Hide members for all states except Oregon
            if(level.Name.Contains("State"))
            {
                foreach (Member member in level.Members)
                {
                    if (member.Name != "OR")
                    {
                        member.Visible = false;
                    }
                }
            }
        }
    }
}
[VB.NET]
Dim schema As CubeDataSchema = OlapClient1.OlapManager.GetDataSchema() 
 
Dim warehouseCostMeasure As Measure = schema.Measures.FindByName("Warehouse Cost") 
Dim warehouseDimension As Dimension = schema.Dimensions.FindByName("Warehouse") 
 
' Set Warehouse Cost measure invisible
If Not warehouseCostMeasure Is Nothing Then
    warehouseCostMeasure.Visible = False
End If
' Set Warehouse dimension invisible
If Not warehouseDimension Is Nothing Then
    warehouseDimension.Visible = False
End If
 
For Each dimension As Dimension In schema.Dimensions
    For Each hierarchy As Hierarchy In dimension.Hierarchies
        For Each level As Level In hierarchy.Levels
            ' Hide all "Country" levels
            If level.Name.Contains("Country") Then
                level.Visible = False
            End If
 
            ' Hide members for all states except Oregon
            If level.Name.Contains("State") Then
                For Each member As Member In level.Members
                    If member.Name <> "OR" Then
                        member.Visible = False
                    End If
                Next
            End If
        Next
    Next
Next


Programmatically creating, deleting, and selecting SSAS Security Roles

One of the easiest ways to restrict access to specific cube data/schema is by using SSAS Security Roles. In the above examples, you can use runtime code to programmatically hide elements, however it is only hiding elements from the schema and not hiding them from the actual underlying data.

To hide all related data for a particular dimension, member, or attribute for example, you need to create roles that contain access and denied permissions for particular piece of elements within a cube. Although you can create roles manually through the SQL Server Management Studio, you can also programmatically create (or delete) roles.

Included at the end of this article in the Additional Downloads section is a Visual Studio 2008 sample project that allows you to programatically create, delete and use roles.

The code is based on this article in MSDN: Programming AMO Security Objects

Below are sample screenshots showing the sample application in action.


Fig. 1 - On initial load, the report shows Internet Sales Amounts by Customer by Date. To demonstrate the role's effectiveness a role will be created to prevent access to only the Ontario member within the
[Customer].[State-Province] attribute.


Fig. 2 - A role called Ontario is created for the unique member [Customer].[State-Province].&[ON][CA] within the
State-Province attribute within the Customer dimension. Notice that with the Ontario role selected, the report does not show all the other data within the Customer or Date dimensions for any of the other members, levels or attributes.


Fig. 3 - After the role is created, you can see the role's properties in SQL Server Management Studio. In particular the permission settings under Dimension Data.


Fig. 4 - The Ontario is deleted. Report back to showing all data under the Canada member (within Customer).


Fig. 5 - Dundas Olap control connected using both the Ontario and Alberta roles.

The sample project demonstrates how to create a role with only single member access permission and connect using it, however in a real world situation you may want to have a single role contain multiple access or denied permissions. As well, you may want the Dundas Olap control to connect to multiple roles at a given time.

To add multiple members permission to a single role, sepearate each cube element (eg. member) with commas.
  { [Customer].[City].&[Mississauga]&[ON], [Customer].[City].&[Toronto]&[ON] } 

Likewise, to use multiple roles when connecting to an SSAS server, you can comma separate your roles in the connection string
  string connectionString = @"Provider=MSOLAP;
                              Data Source=localhost;
                              Initial Catalog=Adventure Works DW; 
                              Roles=Ontario, Alberta";
 

Additional Downloads:

OlapRoleWebSample.zip
PoorExcellent