Saturday, June 11, 2011

Using the SharePoint 2010 User Profiles to Implement Dimensional Level Security in SQL Server Analysis Services 2008


Building security into a SQL Server Analysis Service cubes to limit a users view of the data so they only see “their own” data is a typical client requirement.   For example,  a manufacturing company may want the sales representatives in the East region to see only East region data while sales representatives in the West region should only see the West region data.  Senior managers and the Accounting staff will need to see all data for all regions.  SSAS can support this functionality using the techniques described in  Implementing User-Specific Security in SSAS  by Bryan C. Smith and Dynamic Security in SSAS Cubes by Azaz Rasool. 

SSAS relies on the Windows ID being passed using the Username function which returns “MyDomain\UserId” in order to identify the user.    Often times the user ID in the source system application or ERP are not synchronized with the Windows Active Directory ID, which is what gets passed to SSAS.  This is an issue because while we can usually get a dataset from the source system by user id that shows what regions or products that should be secured by user, this doesn’t necessarily match with what will be passed to SSAS.   For example, my Windows ID maybe “Manchester\LNani” but my user ID in the ERP system may be “LuisNani” 

Often times the solution is to simply build a mapping table to map the Windows Active Directory ID to the user ID in the source system application or ERP.   This is not hard, but some kind of interface needs to be created and some sort of maintenance process needs to be put in place for this to be a long term solution.   Typically nobody is really excited about spending budget, time, and effort to build a one off maintenance web application and then implement a process around it, but  unless that is done the mapping tables becoming neglected and outdated.   This ultimately compromises the user specific security in our SSAS cubes.

An alternate approach would be to use SharePoint 2010 as the repository.  A customized field can be added to all SharePoint user profiles (ERP_ID in my example below).  The advantages are now there is a place were the Windows Directory ID is mapped to the users source system application or ERP ID and the organization can take advantage of the user profile maintenances pages to add, update, and delete the customized field.  No new pages or application needs to be developed.   Additionally, an organization can simply add the population of the field as part of the normal process of creating a new SharePoint user profile.   In some organizations this process is pushed down to the SharePoint administrator at the business unit/department level and is not an IT responsibility.

To use the SharePoint user profile data it needs to be extracted by  a C# or program using the object model that Microsoft provides.  I decided on a program mainly because it was more familiar within the organization. 

The first step is to create the destination table in SQL Server with the DDL script.

CREATE TABLE [dbo].[SharePointUserProfile](

      [FirstName] [varchar](100) NULL,

      [LastName] [varchar](100) NULL,

      [Title] [varchar](100) NULL,

      [WorkEmail] [varchar](100) NULL,

      [AccountName] [varchar](100) NULL,

      [ERP_ID] [varchar](100) NULL,

      [CreatedBy] [varchar](128) NULL,

      [CreatedDate] [datetime] NULL





ALTER TABLE [dbo].[SharePointUserProfile] ADD  CONSTRAINT [DF_SharePointUserProfile_CreatedBy]  DEFAULT (suser_sname()) FOR [CreatedBy]



ALTER TABLE [dbo].[SharePointUserProfile] ADD  CONSTRAINT [DF_SharePointUserProfile_CreatedDate]  DEFAULT (getdate()) FOR [CreatedDate]


Then a program can be executed on the SharePoint 2010 server to extract the user profile data and write the output to your staging database.   The user profile data is now available to your ETL process and can be used in your datamart and your SSAS cube.  The program as written accepts 3 parameters for the SharePoint 2010 site, SQL Server, and database name.

Module Module1



    Sub Main()

        ' Retrieving User Profile Properties in SharePoint 2010


        Dim strArgs() As String

        Dim strSite As String

        Dim strDatabaseServer As String

        Dim strDatabaseName As String


        strArgs = Split(Command$, " ")

        strSite = strArgs(0)

        strDatabaseServer = strArgs(1)

        strDatabaseName = strArgs(2)


        Dim site As SPSite = New SPSite(strSite)

        Dim serviceContext As SPServiceContext = SPServiceContext.GetContext(site)


        'initialize user profile config manager object

        Dim upm As New UserProfileManager(serviceContext)


        Dim con As New SqlConnection

        Dim cmd As New SqlCommand


        con.ConnectionString = "Data Source=" + strDatabaseServer + ";Initial Catalog=" + strDatabaseName + ";Trusted_Connection=yes"


        cmd.Connection = con 'Sets the Connection to use with the SQL Command


        cmd.CommandText = "TRUNCATE TABLE SharePointUserProfile"

        cmd.ExecuteNonQuery() 'Executes SQL Commands Non-Querys only


        For Each p As UserProfile In upm


            ' Get profile properties

            Dim FirstNameProp As UserProfileValueCollection = p.Item("FirstName")

            Dim LastNameProp As UserProfileValueCollection = p.Item("LastName")

            Dim TitleProp As UserProfileValueCollection = p.Item("Title")

            Dim WorkEmailProp As UserProfileValueCollection = p.Item("WorkEmail")

            Dim AccountNameProp As UserProfileValueCollection = p.Item("AccountName")

            Dim ERP_IDProp As UserProfileValueCollection = p.Item("ERP_ID")


            ' Get string values from the properties

            Dim FirstName As String = FirstNameProp.Value

            Dim LastName As String = LastNameProp.Value

            Dim Title As String = TitleProp.Value

            Dim WorkEmail As String = WorkEmailProp.Value

            Dim AccountName As String = AccountNameProp.Value

            Dim ERP_ID As String = ERP_IDProp.Value


            cmd.CommandText = "INSERT INTO SharePointUserProfile " + _

                              "(FirstName" + _

                              ", LastName " + _

                              ", Title" + _

                              ", WorkEmail" + _

                              ", AccountName" + _

                              ", ERP_ID ) " + _

                              " VALUES ( " + "'" + FirstName + "' ," + _

                                          "'" + LastName + "' ," + _

                                          "'" + Title + "' ," + _

                                          "'" + WorkEmail + "' ," + _

                                          "'" + AccountName + "' , " + _

                                          "'" + ERP_ID + "' )"


            cmd.ExecuteNonQuery() 'Executes SQL Commands Non-Querys only




        con.Close() 'Close the connection 


    End Sub


End Module


There is another technique and set of objects that can be used to pull the data from a SharePoint web service.  The advantage of this is that the code would not necessary have to run on the SharePoint server, which may not be allowed in some environments.

Development using the SharePoint 2010 user profile object model is best done on a server with SharePoint 2010 installed.  This will ensure you have all the necessary DLLs available.

No comments:

Post a Comment