I’ve recently been working on a project which required KPI level security alongside the traditional row level security secured at a geography level. This would limit what financial data a user could see within a cube, without having to create multiple cubes or use perspectives (which would not actually secure the data).
To achieve this, I needed to populate a set of ‘KPI User/Role’ tables stored in Master Data Services (MDS) with a list of users who were stored in a particular AD group. I would need these tables updated on a regularly basis to grant/revoke access. We could then use these names along with the USERNAME() function in DAX to filter.
The Solution
One method to solve my problem would be by using SSIS. The package could be setup to run as part of a SQL Agent Job, either by a schedule or on demand. My list of users were stored in an AD group called LH_FIN.
To start with you will need to truncate and clear your MDS staging tables that you are about to populate. You can then use the data flow to process the majority of the logic, by creating a script component task. The purpose of this is to loop through Active Directory and pick up the user details that belong to the specified AD Group or set of AD groups if dealing with multiple roles.
A number of variables are defined which the task uses to complete the lookup.
- strLDAP – the LDAP directory on which to perform the lookup
- strDomain – the domain on which the AD group(s) belong
- strADPrefix – the AD group prefix from which to return user information about
- strADParent – the parent group which contains the AD groups which you are looking up (may not need to be used if only looking up a single AD group)
To extract users from multiple groups, make sure the prefix stored in the variable strADPrefix covers both groups. Once the rows are extracted it would then be a case of using SSIS to split the data accordingly on the AD Group Name.
The following code can be used in the script:
The first section sets up the objects required to interrogate the directory, and the fields we expect to return from the accounts – the most important of which is memberof which is used to check versus our AD Prefix. It also filters out items such as service accounts and disabled accounts to speed up the interrogation process.
Public Overrides Sub CreateNewOutputRows() Dim domain As String = Variables.strDomain Dim searchRoot As New DirectoryEntry(Variables.strLDAP, Nothing, Nothing, AuthenticationTypes.Secure) Dim dirSearch As New DirectorySearcher(searchRoot) dirSearch.SearchScope = SearchScope.Subtree 'LogonName, GroupsUserBelongsTo, Department, JobTitle, MailAddress, DisplayName dirSearch.PropertiesToLoad.Add("samaccountname") dirSearch.PropertiesToLoad.Add("memberof") dirSearch.PropertiesToLoad.Add("department") dirSearch.PropertiesToLoad.Add("title") dirSearch.PropertiesToLoad.Add("mail") dirSearch.PropertiesToLoad.Add("displayname") 'filter to user objects dirSearch.Filter = "(objectCategory=person)" 'filter to user objects dirSearch.Filter = "(objectClass=user)" 'filter out disabled accounts dirSearch.Filter = "(!userAccountControl:1.2.840.113556.1.4.803:=2)" 'filter out password never expires accounts, i.e. service accounts dirSearch.Filter = "(!userAccountControl:1.2.840.113556.1.4.803:=65536)" 'sets chunk size for retrieving items dirSearch.PageSize = 1000
The next section of code performs the search, and for any LDAP objects it finds within the filter set, returns the properties requested. These properties are then stored in key/value pairs.
Dim props As ResultPropertyCollection Dim values As ResultPropertyValueCollection Dim key As String Dim userAccountName As String Dim departmentHome As String Dim jobtitle As String Dim GroupName As String Dim email As String Dim displayName As String Dim groups As New ArrayList Using searchRoot 'Return all LDAP objects, LDAP://acl/CN=Tristan Robinson,OU=Employees,DC=ACL,DC=local 'CN = Common Name, OU = Organisational Unit, DC = Domain Component Using results As SearchResultCollection = dirSearch.FindAll() For Each result As SearchResult In results 'For each object return properties, i.e. displayname, memberof, etc props = result.Properties For Each entry As DictionaryEntry In props key = CType(entry.Key, String) 'For each property, inspect the property and record its value 'Logon Name If key = "samaccountname" Then values = CType(entry.Value, ResultPropertyValueCollection) userAccountName = CType(values.Item(0), String) End If 'Department If key = "department" Then values = CType(entry.Value, ResultPropertyValueCollection) departmentHome = CType(values.Item(0), String) End If 'Job Title If key = "title" Then values = CType(entry.Value, ResultPropertyValueCollection) jobtitle = CType(values.Item(0), String) End If 'E-Mail If key = "mail" Then values = CType(entry.Value, ResultPropertyValueCollection) email = CType(values.Item(0), String) End If 'Display Name If key = "displayname" Then values = CType(entry.Value, ResultPropertyValueCollection) displayName = CType(values.Item(0), String) End If 'Groups User Belongs To (array/collection) If key = "memberof" Then values = CType(entry.Value, ResultPropertyValueCollection) groups = GetGroups(values) End If Next
The final section filters the data into the output buffer if from the array list we’ve extracted above, we have matching strings from our original AD Prefix variable. It will then reset, and loop round for the next account.
'Export user details to buffer if it passes the logical test For Each item As String In groups 'Avoids computer accounts, i.e. ending with $ If userAccountName.EndsWith("$") = False And item.ToString.StartsWith(Variables.strADPrefix) Then 'And item.ToString <> (Variables.strADParent) Output0Buffer.AddRow() If String.IsNullOrEmpty(userAccountName) Then Output0Buffer.UserAccountName_IsNull = True Else Output0Buffer.UserAccountName = userAccountName End If If String.IsNullOrEmpty(domain) Then Output0Buffer.Domain_IsNull = True Else Output0Buffer.Domain = domain End If If String.IsNullOrEmpty(item.ToString) Then Output0Buffer.GroupName_IsNull = True Else Output0Buffer.GroupName = item.ToString End If If String.IsNullOrEmpty(jobtitle) Then Output0Buffer.JobTitle_IsNull = True Else Output0Buffer.JobTitle = jobtitle End If If String.IsNullOrEmpty(email) Then Output0Buffer.Email_IsNull = True Else Output0Buffer.Email = email End If If String.IsNullOrEmpty(displayName) Then Output0Buffer.DisplayName_IsNull = True Else Output0Buffer.DisplayName = displayName End If End If Next groups.Clear() userAccountName = "" departmentHome = "" jobtitle = "" GroupName = "" email = "" displayName = "" Next End Using End Using End Sub
I also required a function to split the list of groups a user belonged to and store them in another array list.
Private Function GetGroups(ByVal values As ResultPropertyValueCollection) As ArrayList Dim valueList As ArrayList = New ArrayList() For Each Item As Object In values Dim memberof As String = Item.ToString() Dim pairs As String() = memberof.Split(",".ToCharArray) Dim group As String() = pairs(0).Split("=".ToCharArray) valueList.Add(group(1)) Next Return valueList End Function End Class
Once a list of users has been extracted, you will need to do a lookup against the existing list and only stage those that are new. This can be achieved through a simple lookup component . You can then move the rows into the MDS staging table ready for the load into MDS.
After the data flow has processed successfully, the next stage is to sweep the records into MDS using one of the built in stored procedures in the product. The results of which can be seen in the MDS Import View on the MDS site.
The following entities can then be used to power the DAX:
- User (Username, Team, AD Login – populated from the script above)
- Role (Role – populated manually)
- User Role (User, Role – joins users to roles, populated manually)
- KPI (KPI – populated manually)
- KPI Role (Role, KPI – joins roles to KPIs, populated manually)
These are then processed to the DW by ETL, and a UserKPI view is written across the tables to provide an AD Login to KPI pair.
For filtering the measures in the cube, you can then apply the following piece of DAX to the original measure – Gross Sales in the example below:
Gross Sales:= IF( CONTAINS( CALCULATETABLE( 'UserKPI', UserKPI[KPIName] = "Gross Sales"), UserKPI[ADLogin], USERNAME() ), [Gross Sales (ACTUAL)] , BLANK () )
This concludes this blog post – hopefully this will be useful for anyone that wants to extract users from AD to control access / security within an application. Data could also be loaded direct into DB tables rather than MDS if required.
Please feel free to comment!
Pareto Charts in Power BI and the DAX behind them
The Pareto principle, commonly referred to as the 80/20 rule, is a concept of prioritisation.
Apr
Databricks: Cluster Configuration
Databricks, a cloud-based platform for data engineering, offers several tools that can be used to
Apr
AI Assistance in Microsoft Fabric
The exponential growth of Large Language Models (LLMs) couples with Microsoft’s close partnership with OpenAI
Apr
10 reasons why it’s worth the effort to understand the value of your data
“If leaders really want to create a data driven culture, the journey starts with them!
Apr
Content Safety in Azure AI Studio
Azure AI Content Safety is a solution designed to identify harmful content, whether generated by
Apr
Model Benchmarks in Azure AI Studio
In the constantly changing field of artificial intelligence (AI) and machine learning (ML), choosing the
Apr
Celebrating International Women’s Day: from Classroom to Code
As we celebrate International Women’s Day, I want to share my journey of breaking stereotypes
Mar
Pretty Power BI – Adding Pagination to Bar Charts
Good User Experience (UX) design is crucial in enabling stakeholders to maximise the insights that
Feb