Konstantin Gorakine's BI Lingual Blog

Here, all data is freely accessible when sliced by any dimension (except Merit); in this case by Org Structure. You can see that Master department data contributes to the total and there is no restrictions applied to the data visibility. Results should change when we slice by Merit dimension:

Noticed the difference in the total? When we slice by the Merit dimension, we should not have access to the Master department data, since this data is highly sensitive for a user and must be hidden. Take a look at another picture:

Any time user is using Merit dimension, data is recalculated to reflect new values without master department figures.  

My solution consists of two almost identical cubes (which will source their data from the same relational database) and a .NET component. One cube with security implemented will be facing users answering the majority of the requests, whereas the second will be contacted via .NET stored proc only when request by the Merit dimension is submitted. The second cube will have no security restrictions and will stay hidden for the users.  The reason why we would need a second cube is simple:  infinite recursion. It’ll be clear soon.

First of all, we need to know when we slice by Merit department, so we apply our calculations to this dimension only. This easily can be achieved by using Scope statement in the cube script. My initial scope statement looked like this:

scope ([Measures].[Measure] *

       [Merit].[Merit].Members);

      

           if not ([Merit].[Merit].CurrentMember  is

                   [Merit].[Merit].[All Merits])

           then

       

               this = sum(NonEmpty([Org Structure].[Org Structure Key].Children,

                                      (StrToMember("[Logins].[Login].[" + userName + "]"),

                                       [Measures].[Login OrgStructure]

                                       )

                                   )

                          )              

           end if;           

end scope;

Here, I have my security filter added, so I thought that I would be able to obtain results from the same cube, but when SSAS is trying to resolve [Merit].[Merit].CurrentMember it is running the same scope statement eventually falling into infinite recursion. This is how I came up with an idea of having another cube. I would write .NET stored proc, obtain the tuple of the current cell by using ADOMDServer library, build dynamic MDX, and open ADOMDClient connection to the second cube to execute my query. This path needs to be run for every cell on the report, so when users want to drill down their results to the lover levels, execution time will linearly increase.

Here is my final scope statement with the call to the .NET proc added.

scope ([Measures].[Measure] *

       [Merit].[Merit].Members);

      

           if not ([Merit].[Merit].CurrentMember  is

                   [Merit].[Merit].[All Merits])

           then

       

               this = MeritSecurity.GetMerit(

                      "Provider=MSOLAP.4;Initial Catalog=hidden cube;Data Source=myserver100"

                                              )              

           end if;           

end scope;

Here is the .NET proc code and it’s well-commented

Imports Microsoft.AnalysisServices.AdomdServer

 

Public Class MeritSecurity

 

    Public Function getMerit(ByVal connectionString As String) As String

 

        Const COMMA As String = ","

 

        Dim selectTuple As String = String.Empty

        Dim whereSlicer As String = String.Empty

        Dim result As String = String.Empty

 

        Dim currentCellAddressTB As New TupleBuilder

        Dim currentCellAddressM As MemberCollection

 

        ‘get the current user name

        Dim userName As String = New Expression("Username()").Calculate(Nothing).ToString

        ‘build the sub-select slicer with the security filter

        whereSlicer = "(NonEmpty([Org Structure].[Org Structure Key].Children, " + _

                      "         (StrToMember(""[Logins].[Login].[" + userName + "]""), " + _

                      "                        [Measures].[Login OrgStructure])) ) on 0 from [hidden cube])"     

 

        ‘go over the current context and collect all current members into tuple bulder

        For Each currentDimension In Context.CurrentCube.Dimensions

            For Each currentHierarchy In currentDimension.Hierarchies

                currentCellAddressTB.Add(currentHierarchy.CurrentMember)

            Next

        Next

 

        ‘place all members into a member collection

        currentCellAddressM = currentCellAddressTB.ToTuple.Members

 

        ‘build select tuple for the current cell

        For Each currentMember In currentCellAddressM

            selectTuple += currentMember.UniqueName + COMMA

        Next

 

        ‘get rid of the last comma and close the tuple

        selectTuple = "(" + Left(selectTuple, selectTuple.Length – 1) + ")"

 

        ‘get all ADOMDClient objects to run query against the second cube

        Dim SSASconnection As New Microsoft.AnalysisServices.AdomdClient.AdomdConnection

        Dim SSAScommand As Microsoft.AnalysisServices.AdomdClient.AdomdCommand = + _

        SSASconnection.CreateCommand()

        Dim SSASDataReader As Microsoft.AnalysisServices.AdomdClient.AdomdDataReader

 

        SSASconnection.ConnectionString = connectionString

        SSAScommand.CommandType = CommandType.Text

        ‘build the final query

        SSAScommand.CommandText = "select " + selectTuple + " on 0 from " + whereSlicer

 

        SSASconnection.Open()

        ‘RUN IT FINALLY…

        SSASDataReader = SSAScommand.ExecuteReader()

 

        Try

            If SSASDataReader.Read() Then

                result = SSASDataReader.GetString(0)

            Else

                result = "no access"

            End If

        Catch ex As Exception

            result = String.Empty

        End Try

 

        SSASconnection.Close()

 

        Return result

 

    End Function

 

End Class

 

This will work, returning different results when data is sliced by different dimensions. As I’ve mentioned, this can and will impact query time dramatically, but client really wants this functionality to be somehow inplemented.

Any feedbacks will be appreciated