Tag: Filtering

Slicing overlapping subgroups of data with a synthetic “Filter Table”

I saw a post from Marco Russo (Twitter) showing a trick to use a slicer to filter across multiple columns at once in Power BI with some DAX and a new dynamic filter table. I’ve done something similar in the past to use a slicer not to slice data directly, but to change a view in the data or change filtering criteria indirectly – I wanted to share an example here to show a slightly different use case than Marco’s for a similar technique.

I want to create a slicer that switches the dataset between different subsets of data, but with overlap – like just my sales, sales for my group, and sales for the company. This is normally accomplished with a calculated column for the group the data is in, along with a slicer where if you want to see everybody, you have to select all three values like this:

But what if you want to create slicer options that aren’t exclusive? Where selecting “My Department” includes you too? Here’s how you do it!

In my example, I’ll need a fake sales and person table, so I’ll just use the “Enter Data” interface to create them:

Creating a sales table
Creating a user table (just a list from the sales table)

At this point, we could just join them on name and add some slicers, but here’s where we get more advanced by creating the filter table. The names repeat, but they show once for each slicer option they belong with, so whatever you select, you’ll see the relevant people:

Creating the filter table with all the slicer options in it

Once you have those three tables, you need to link them up – note the two-way relationship between the filter table and the person table. Without that, filtering on the last table won’t flow through to the sales table and our slicer won’t work:

Create the relationship and make sure the filter-user link to bi-directional

That’s all it takes – now you can create a slicer on the “Filter” column in the “Team Filter” table, and you’ll get whatever non-exclusive subset of the data you’re looking for!

I’m looking at only Bob’s sales
I’ve added Sally’s sales, but can still see Bob
Now I’m looking at everybody!

I made the slicer single-select in this example, which I do to avoid confusion – while this still works if you leave it a normal multi-select, it can lead to some confusion since users will expect the options to be exclusive and if they’re already got “Everybody” selected, neither of the other two options will do anything 🙂

If you want it, grab the PBIX file here.

I hope this is helpful to somebody – if you have any suggestions or other use cases for this, please add them below!

Querying Active Directory from SQL Server

SQL Server provides some pretty flexible integration with Active Directory through the ADSI Linked Server provider, something that’s present by default when you install SQL Server. If you’ve never used it before, it allows you to connect to a domain controller and query AD the same way you’d query any other linked server. For example, it gives you the option to:

  • Identify when logins to SQL Servers or databases that support financial applications exist, but have no matching AD account (either direct integrated logins, or if SQL logins or rows in a “User” table have been set up to match the AD login)
  • Kick off alerts to provision the user in various systems based on their AD group membership
  • Automatically trigger an action when a new account appears in active directory (for example, we auto-provision security badges and send an email alert to our head of security to assign the appropriate rights)

While much of this could also be done from Powershell as well, we use the SQL Server Agent to manage many of our scheduled job (because it’s so handy to have the agent remotely accessible), as well as sometimes just needing data from AD in a query. To support a number of processes we have in place, we run a synchronization job every so often throughout the day that pulls about two dozen fields for all users and synchronizes them into a table if anything has changed.

Setting up the linked server itself is pretty straightforward (courtesy of http://community.spiceworks.com/how_to/show/27494-create-a-sql-linked-server-to-adsi):

  1. Create the linked server itself
  2. Set the security context (if you want to query AD as something other than the SQL Server Service account – by default, all domain users can do this and it’s only required if the domain is remote or if, for some reason, your SQL Service account’s AD rights have been restricted, like if you’re running as “LOCAL SERVICE”)
  3. Enable OPENQUERY (Ad Hoc Distributed Queries)

You’ll notice that setting up the linked server itself doesn’t actually specify where Active Directory is located or what domain/forest you’ll be querying – that’s actually done in the query itself. In each query, you’ll need to specify the FQDN (Fully-qualified domain name) of the domain (or OU) of the domain you’re querying. For example, we’d get all users from a domain by issuing the following query (in this example, “ADLinkedServerName” is the linked server we just created, and our domain is “corp.mycompany.local”):

SELECT EmployeeNumber, Name AS FullName, givenName as FirstName, sn as LastName,
L AS Location, samAccountName as ADAccount
FROM OPENQUERY(ADLinkedServerName,'SELECT Name, L, givenName, sn,
EmployeeNumber, EmployeeID,samAccountName,createtimestamp
FROM ''LDAP://OU=Users,DC=corp,DC=mycompany,DC=local''
WHERE objectClass =''user''') ad

This query will search that OU (“Users”, in this case) and everything below it, so changing the FROM to “LDAP://DC=corp,DC=mycompany,DC=local” would fetch the entire directory (for all the “user” objects), regardless of what folder they appeared it – if your directory puts users in another OU (like “Associates”, for example), you should adjust the query accordingly.

For column names, you can pull any AD properties at all that you’re looking for – even custom ones that aren’t part of a standard AD configuration. To get an easy list of AD properties to choose from, I like using ADSIEDIT (part of Microsoft’s Remote Server Administration Tools – download RSAT for Windows 7 or RSAT for Windows 8.1) – just drill down all the way down to an object, like a user, right click on them and select “Properties”, and you can see a list of all the properties on that account. If you’ve got Domain Admin rights, this tool can be used to modify these values too, but for querying, you only need to be a domain user or somebody who has rights to browse AD. Make a note of the names of particular properties that you’re interested in – also note that AD queries are case-sensitive, so you’ll need to note the casing of these properties as well.

One potential gotcha that I’ve run into is that maximum result size that AD will return in a single query can be set as part of domain policy – by default it’s 1000 records at once, and can be configured by setting or adjusting the “PageSize” property on your domain controllers (see https://support.microsoft.com/kb/315071/en-us). Also, there’s a “MaxResultSetSize” property as well that’s set to 256KB by default, but I’ve never hit it – unless you’re pulling every single property back, you’d likely hit the PageSize row limit before you hit the ResultSize byte limit, but remember that both are there. If you do hit the AD result count limit, it will return the rows up to the limit, but then execution stops with a kind of cryptic error:

Msg 7330, Level 16, State 2, Line 2
Cannot fetch a row from OLE DB provider "ADsDSOObject" for linked server "YOURDOMAIN".

If your domain is larger than the PageSize limit, you’ll need to cut your query into multiple return sets of data so you don’t exceed the limit on any single query. Since our domain contains about 2400 users, we were able to do it in two queries, broken up like this:

SELECT samAccountName
  FROM OPENQUERY(ADLinkedServerName,'SELECT samAccountName
                                       FROM ''LDAP://OU=Users,DC=corp,DC=mycompany,DC=local''
                                      WHERE objectClass =''user''
                                        AND givenName<''L''') as c
UNION ALL
SELECT samAccountName
  FROM OPENQUERY(ADLinkedServerName,'SELECT samAccountName
                                       FROM ''LDAP://OU=Users,DC=corp,DC=mycompany,DC=local''
                                      WHERE objectClass =''user''
                                        AND givenName>=''L''') as c

By dividing the names on L, this cut the directory roughly in half – if yours was larger, you could divide it by querying each OU separately, or by looping through letters of the alphabet, or whatever makes sense in your setting. You could even do something dynamic like pull as many records as you can, then grab the value from the last record you pulled and use it as the baseline to pull the next set as far as you can, and then repeat until you run out of records. Linked servers don’t allow you to dynamically assemble your query at run-time – it has to be hard-coded in the query – but there are some ways around that (like building your OPENQUERY as a string and then executing it via sp_executesql, for example).

Now that you have your AD records stored in a temp table, you can identify new/changed records and merge them into a SQL table you already have ready using an INSERT/UPDATE/DELETE or MERGE statement, or possibly trigger notifications or some other business process.

I hope this is helpful – if you’d like some more detail, please leave a comment and I’m happy to elaborate where it’s necessary!

Write permission error when inserting over linked server

I spent some time troubleshoot permissions over a linked server recently before finding out the the cause of my error wasn’t permissions-related at all. I was attempting to perform an insert on a remote table, and was getting the following error:

Msg 7344, Level 16, State 1, Line 2
The OLE DB provider “SQLNCLI10” for linked server “RemoteServer” could not INSERT INTO table “[RemoteServer].[RemoteDB].[dbo].[IdentInsertTest]” because of column “ID”. The user did not have permission to write to the column.

After some time attempting to isolate the missing permissions, I realized that it was actually a disguised error message. I was trying to insert a value into an identity column, but rather than the standard error message I expected to see in that case, I got a generic “You don’t have permission” message, leading to some wasted time troubleshooting.

To recreate the issue, you can follow these steps:

-- Create a test table
CREATE TABLE IdentInsertTest (
	ID INT IDENTITY(1,1),
	SomeValue VARCHAR(10)
)

-- This insert will succeed
INSERT INTO IdentInsertTest (SomeValue)
SELECT 'Some Value'

-- Will fail with IDENTITY_INSERT error
INSERT INTO IdentInsertTest (ID, SomeValue)
SELECT 10, 'Some Value'

The second statement will fail with the standard error message:

Cannot insert explicit value for identity column in table ‘IdentInsertTest’ when IDENTITY_INSERT is set to OFF.

Now, connect to another server and set up a linked server to the other instance, and then try these statements again:

-- This remote insert will succeed
INSERT INTO LinkedServer.RemoteDB.dbo.IdentInsertTest (SomeValue)
SELECT 'Some Value'

-- Will fail with a permissions error
INSERT INTO LinkedServer.RemoteDB.dbo.IdentInsertTest (ID, SomeValue)
SELECT 10, 'Some Value'

If I’d realized what I was doing, it would have saved me some troubleshooting time! The moral here is that if your statement fails over a linked server, ensure your user account is set up correctly and then test it locally – you may get a more accurate error message!