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 VB.net program using the object model that Microsoft provides. I decided on a VB.net 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
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SharePointUserProfile] ADD CONSTRAINT [DF_SharePointUserProfile_CreatedBy] DEFAULT (suser_sname()) FOR [CreatedBy]
GO
ALTER TABLE [dbo].[SharePointUserProfile] ADD CONSTRAINT [DF_SharePointUserProfile_CreatedDate] DEFAULT (getdate()) FOR [CreatedDate]
GO
Then a VB.net 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"
con.Open()
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
Next
con.Close() 'Close the connection
End Sub
End Module
Notes:
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.