Here at the port, we use AD as our back-end user account and group store, like I'm sure many of you do. To relate user data in our SQL database to these accounts, we have a slimmed down user table that looks like this:
[GN_USER_ID] [int] IDENTITY (1, 1) NOT NULL ,
[GN_USER_AD_LOGIN] [varchar] (50) NOT NULL ,
[GN_USER_AD_GUID] uniqueidentifier ROWGUIDCOL NOT NULL
etc. We use the GN_USER_AD_GUID column to lookup the AD user in the store when we need user info, email, etc. This works great, and we have a bunch of utilities to get the AD user, etc. But, my boss just came in and now wants to do some reporting on this data.
I'd like to create a cube that contains a bunch of user data, but the problem is much of the data is embedded in the AD store, and not in SQL. It looks like I can use a the OLE DB Provider for Microsoft Directory Services is to create a linked server. So here's my question. Has anyone out there done this? I'm thinking that with Yukon, I'll be able to use our existing AD .NET classes and hopefully pull this AD data into the SQL world.
-Brendan