Tristan Robinson

Tristan Robinson's Blog

Extracting Users from AD using SSIS

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.

image image

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!

Modelling Survey Style Data

In a recent project, one of the business areas which required modelling was that of survey data. This can often be quite tricky, due to the fact the data is not so quantitative in nature as other business areas such as sales.

How do you go about calculating measures against this type of data? In this blog I will go about explaining a relatively simple model you can use to achieve this goal.

 

Master Data Services

To aid with modelling I used Master Data Services (MDS) to help map the questions and responses from the surveys.

The idea behind using MDS is that regardless of the questions asked, whether it be in one format or another, English or Spanish, you can map them to a set of master questions. Usually these will align closely to your measures/KPIs and should be relatively finite in number so that the mapping process is feasible for a business user. In my business case, the master set of questions revolved around items such as quality, price, and promotion of products. For responses, I needed to map these to a flag which indicated we wanted the response to contribute towards a measure and ultimately a KPI.

I first created the following entities in MDS.

  • Survey (holds the survey name, a unique identifier for the survey, and in my case I also created a master survey lookup to group the surveys together)
  • Source Question  (holds the distinct set of questions assigned to each survey, along with identifying codes, and question orders - each question would also be mapped to a master question) 
  • Source Response (holds a set of response options for each question, along with identifying codes)
  • Master Question (holds the master set of questions and joins to the KPIs)
  • KPI (holds a list of KPIs that you need to address by aggregating response data)
  • Response of Interest (holds a list of responses that are regarded as positive / contributing towards the KPI when answered by the question)
  • Response of Interest Mapping (allows the user to map the response options available on each question to a response of interest)

In terms of the response of interest, I was only interested in responses where the answer from the survey was “Yes” so this was all that was required here. However for more complex response options, the model can provide the scalability required. For instance, if you were looking for an answer between 7-10 and the survey had been answered with a 7, 8, 9, or 10 – each of these could be mapped to 7-10 without having to create responses of interest for all particular combinations. This scales well and can cover scenarios for instance where the answer should be between 700 to 1000 in the same way.

I also created a Master Question and Response of Interest value for N/A. This way, only the blanks on the mapping entities required populating and the user was never unsure whether a blank represented a question/response that was not of interest, or something that required mapping still.

All the entities above apart from Master Question, KPI, and Response of Interest were populated automatically from ETL with a SQL script used to extract the contents of those entities from source. The other 3 entities were populated manually by a business user. I also locked the entities / columns that the user shouldn’t be changing by using read-only permissions.

Some examples of the manually populated tables can be seen below:

clip_image0024_thumb1

clip_image002_thumb1

 

Data Warehouse

For modelling the tables in the data warehouse, I created a separate dimension for each of the Response, Question, Survey, and KPI entities, and a single Fact to capture the responses of interest against these dimensions.

The majority of dimension lookups were straight forward along with the response of interest measure which can be seen below:

SELECT 
SU.Code AS SurveyId, 
SR.Name AS ResponseName, 
1 AS ResponseOfInterest 
FROM mdm.PL_ResponseOfInterestMapping RM 
INNER JOIN mdm.PL_SourceResponse SR
ON RM.SourceResponse_Id = SR.Id AND RM.Survey_Id = SR.Survey_Id 
INNER JOIN mdm.PL_Survey SU 
ON RM.Survey_Id = SU.Id 
WHERE 
RM.ResponseOfInterest_Code IS NOT NULL AND RM.ResponseOfInterest_Name <> 'N/A'

During our ETL runs for the fact we also checked for responses that had not been yet mapped – and did not pull these through.

If you then have a cube sat on top of your DW, you can then write measures across the fact to count the number of responses of interest. An example of which can be seen here:

Price Activation Standard:=
COUNTROWS(
    FILTER(
        CALCULATETABLE('Outlet Survey','KPI'[Sales Driver] = "Price"),
        'Outlet Survey'[IsResponseOfInterest] = 1
    )
)

This was then checked against a Target fact table to calculate the compliance and the KPI was essentially an aggregation of the compliance across geography.

 

Summary

Overall, the model has proved very popular with the business. It’s easy to understand and gives the business control over which responses to count towards the KPI – without having to hard code values into the ETL which had been seen in previous solutions. It can also be extended easily be adding new KPIs and mapping them to new master questions without having to change any ETL. From a development perspective it also means that nothing should go into the DW as Unknown for a Dimension since the SQL to populate MDS, can also be used for the DW and therefore should always match.

If you have any questions, please feel to ask them in the comments.

Upgrading your BI Solution (2012 to 2014)

In a recent project I was asked to upgrade a BI solution from SQL Server 2012 to 2014.  The aim of which was to consolidate a number of database sources onto a single version of SQL Server. This blog looks at the steps required in order to achieve the upgrade process, along with issues I hit along the way.

 

Where do I start?

In order to develop on SQL Server 2014, you’ll need to update your development tools! This involves downloading the following applications.

Application

Comments

Link

SSDT BI for VS 2013

Allows you to modify and deploy SSIS, SSAS, SSRS files. I would then suggest downloading Update 5 from within the application. This resolved some source control issues for me.

https://www.microsoft.com/en-gb/download/details.aspx?id=42313

SSDT Database Designer for VS 2013

Allows you to modify and deploy SQL related files.

https://msdn.microsoft.com/en-us/dn864412

Team Explorer for VS 2013

Sometimes this is bundled in together with SSDT but not the case for 2013.

https://www.microsoft.com/en-gb/download/details.aspx?id=40776

BIDS Helper for 2014

This is a must have for any solution with a tabular cube. It allows the user more functionality to customise the cube and organise measures.

https://bidshelper.codeplex.com/

 

You’ll also need to update any custom components within the solution. For me, this was the 2014 Attunity drivers (3.0) to connect to our Oracle sources and some custom Adatis Framework components, both will need GACing. Remember that if you develop using 32 bit components you may need to download the equivalent 64 bit components once your solution is deployed to a server.

Once you have the necessary tools locally, you can start the upgrade process. Open up the solution in your new environment.

 

The Solution Upgrade Process

The first thing I noticed was that during the upgrade I lost all my SSIS components layout and comments when opening up the packages in VS 2013 that were developed in VS 2012. The code was still in the package file, but did not seem to do anything. I’m not sure this happens for everyone as the information I found to resolve the issue was not particularly well documented around the internet. Its possible that upgrading to Update 5 beforehand may resolve this issue.  To fix the the problem, I had to open up the packages code and search for the line that says version=“sql11”. This needs to be changed to “sql12”. The next time you open the package, everything should appear again. I also found that if you open up the package first in 2013, the application will automatically change this to sql12 however the comments/layout did not seem to appear. You also won’t be able to amend the file at this point so will need to roll back if using version control such as TFS.

Another issue I ran into was that even after I ran this process, a few developers were unable to see the comments still. This is due to the fact they were running the dark themed Visual Studio which did not invert the text colour on the comments therefore making them appear hidden. Again, probably another bug with the base version of SSDT I downloaded off the Microsoft website before upgrading to Update 5.

At this point you can start to run the packages through the Upgrade Package Wizard (if you right click on the SSIS packages folder in the project) .

upgrade      upgrade2

If packages are not appearing in the wizard, then the application is not recognising the packages as coming from a previous version of SQL Server. Its possible that even after running through the wizard, it reports the packages as having failed the upgrade. It’s fairly safe to ignore this, and is usually the result of custom components or similar.

Depending upon how many packages you need to upgrade you are now faced with the unfortunate task of replacing any of the custom components in those packages, with the new ones you downloaded to work with SQL Server 2014. In my case, this was the custom Adatis components in the Control Flow (Initiate Task/Terminate Task – remember to set LoadStatusType to Success on the Terminate properties), Data Flow (Clean – Data Cleansing/Standardise/Validate Columns), and Event Handlers (Row Throughput/Terminate Task on Error – remember to set LoadStatusType to Failure on the Terminate properties).

In some cases, components before the upgrade display a different icon than afterwards so its easy to tell what version you have. When this is not the case, you’ll have to check the package code.

BeforeReplaceComponents     AfterReplaceComponents

Following this, I then modified any connection strings I had within the solution to point to our new server. If you are not changing servers during your upgrade, just skip this step.

The next step is to update the DB projects to 2014 via the properties of the project. Very simple.

image

The final step needed within the solution is to upgrade the tabular model (if you have one). Firstly, make sure the model is open and then via the model properties, select the SQL Server 2014 compatibility level. Note – that if the model is not open, then this field will display blank.

image

 

Master Data Services
As part of my upgrade, I also had to upgrade our Master Data Services (MDS) to 2014. One of the pre-requirements for this is to have an IIS Web Server installed. If you’re provided a fresh clean server like I was then you’ll need to install this. This can be done through the Server Manager. Make sure the following features are added.

Web Server

  • Common HTTP Features
  • Static Content
  • Default Document
  • Directory Browsing
  • HTTP Errors
  • DO NOT INSTALL WebDAV Publishing

Application Development

  • ASP.NET 3.5/4.5
  • .NET Extensibility 3.5/4.5
  • ISAPI Extensions
  • ISAPI Filters

Health and Diagnostics

  • HTTP Logging
  • Request Monitor

Security

  • Windows Authentication
  • Request Filtering

Performance

  • Static Content Compression

Management Tools

  • IIS Management Console
  • .NET Framework 3.0 Features
  • WCF Activation
  • HTTP Activation
  • Non-HTTP Activation

Windows Process Activation Service

  • Process Model
  • .NET Environment
  • Configuration APIs

The MDS configuration tool is quite good as it identifies if you are missing any pre-reqs. Once you have the pre-requisites for MDS installed as well as MDS 2014 itself, you can begin the upgrade process – which comes in 2 parts, the database, and the web site

Unfortunately, model deployment packages can be used only in the edition of SQL Server they were created in. You cannot deploy model deployment packages created in SQL Server 2008 R2/SQL Server 2012 to SQL Server 2014. Therefore you’ll need to take a database-level backup of your MDS database and restore to the new 2014 instance. In my case, I also needed to give our service account that was running SQL Server security access to the folder in which the backup was stored as it wasn’t able to see it.

Once you have successfully restored the previous MDS database to the new instance, you will need to load up the Master Data Services Configuration tool. To start with click on Database Configuration. If you then select the database you have just restored, it will suggest the database is not compatible with the current version of MDS and requires upgrading. Click on the Upgrade Database button and you should now have a compatible 2014 MDS database. I also had to re-set up the MDS mail profile at this point as this does not transfer across with the database.

The next step is to configure a new MDS website which can be done via the Web Configuration button. Firstly, select the default website that is created when you install IIS. There is no need to create anything separate – in fact this caused me more hassle than it was worth. You can then go about creating the site which requires you to specify an alias, and the application pool information (name and service account information).

Create WebApp

You are then required to associate both the database and web site together. At this point, you should be able to load up the new MDS web site and explore your old model.

One issue I ran into that stopped me loading into MDS at this point was that the service account you specify above will then need to be added to a local security group called “Log on as a batch job”. This can be done via the Computer Management application, then Local Users and Groups, Groups, Log on a batch job.

I also had some minor issues when loading MDS where the page was malformed and I was unable to see the database. I resolved this by providing the service account access to the web config of the web site directly.

 

Deploying & Testing

At this point, all the necessary requirements should be in place for you to deploy and test the upgrade. Deploy the solution as you would normally.

Your first step once your at this point is to add a new SSISDB Catalog to your new instance.  If like me you did this via the old 2012 Management Studio, you’ll run into some errors potentially. Instead, load up the new 2014 Management Studio and you should be fine.

Depending on how your solution is setup, you’ll need to re-confirm any environment variables or script them out again and then configure the SSISDB projects to use them.

Then you will need to re-setup any SQL Agent Jobs you had on the previous instance, again this can be scripted across – just make sure you change the connection details.

Following this, make sure you GAC any components required on the new server similar to what you did locally.

Lastly, you will also need to add your SSAS Service Account into the new SSAS instance.

You can then run the solution out as per usual and check your execution status reports for any irregularities.

 

Summary

Hopefully this covers everything you need to upgrade your solution. Whether its from 2008/2012 to 2014 or 2014 to 2016, the steps should be roughly the same, you might just run into slightly different issues. Generally the process is fairly straightforward, the hardest part was keeping track of the number of steps required and then ticking them off as you do them. If you need to do this for both development and production servers, I’d advise running the steps side by side so they are set up in a similar fashion. Please let me know in the comments below if you’ve had any similar experiences / issues.