Querying Active Directory terribly slow RRS feed

  • Question

  • I'm trying to use PQ to report on AD user objects, but the loading/refreshing can take hours.  We have ~38,000 objects in there, and it takes forever to pull down.  Is there a way to speed this up, other than just manually exporting the users and bringing them in as a CSV?  I'd like to create a report that users can refresh on their own.  For comparison, a manual export takes just a few minutes.

    I also tried with Power BI - it was a little faster - but still unreasonably slow.


    Tuesday, September 10, 2019 12:55 PM

All replies

  • Could you post the exact power query you are running (at least the AD related parts)?
    Thursday, September 26, 2019 9:33 PM
  • I figured out the cause, but I would still like to find a workaround.  I go to Data->Get Data From other sources->From Active Directory.  Then I choose the domain and then the user object.

    Just enumerating the users isn't bad, probably under a minute or two.  What slows it down is if I expand the user.manager column to pull the user's manager's displayName and mail.  What absolutely stalls the whole thing is if I then look at the user.manager.manager and try and pull their displayName and mail (get their boss's boss).  

    I get that each call is spawning another lookup, which then calls another to get their 2nd level boss - so I know why it slows down.  Unfortunately, I need that data.  We're trying to do various reports on user accounts and we need to group by manager and supervisor.  This also lets us easily mail whomever we need to mail (i.e. find all accounts in a group then email their management chain.)

    Any thoughts would be appreciated.

    Thanks, Rick

    Tuesday, October 1, 2019 10:11 PM
  • Basically, we need to try and avoid doing column expansions as much as possible.

    To do that we need to build a map of employee to manager.  This could be a new table (or a record with employee email for field name to be even faster) that you could create just by going just one level user.manager.

    Once you have the new "map" should be able to append columns to user that has the data you want in a relatively faster manner.

    You should also try putting Table.Buffer around the first table you get from AD.

    Wednesday, October 16, 2019 11:34 PM