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.

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