Konstantin Gorakine's BI Lingual Blog

Just another WordPress.com site

One Cube, Different Dimensions, Different Data

with 3 comments

Just started my new gig in NYC and already got some rather unusual requirements related to the cube data security. Client has a small cube with one secured dimension (call it Org Structure) and one special dimension (call it Merit) which when sliced by, should behave slightly different from the rest of dimensions.

A user can have access to one or several departments. No matter how many departments user has access to; one department is always a Master department for him/her.  User has access to data for all departments when sliced by any dimension except Merit, and data for all departments except Master when sliced by Merit dimension. Let me put that requirement in Excel for better visualization.

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

Advertisements

Written by Konstantin Gorakine

January 28, 2010 at 5:31 pm

Posted in SSAS

3 Responses

Subscribe to comments with RSS.

  1. Did you try specifying your scope as follows, getting the Merit level from the Merit hierarchy in the Merit dimension?scope ([Measures].[Measure] * [Merit].[Merit].[Merit].Members);This should get all of the [Merit] members, except for the "All Merit" member meaning that you could remove the IF test entirely from your code.

    Darren

    February 14, 2010 at 7:23 pm

  2. Darren – I tried this approach, but somehow, calculations didn\’t produced right results, so I left it as isThanks for a comment

    Konstantin

    February 16, 2010 at 5:16 pm

  3. Here are this and some other articles on Analysis Services Custom Security:

    http://ssas-wiki.com/w/Articles#Custom_Security

    Sam Kane

    March 24, 2011 at 4:24 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: