Forum

Power Query and Act...
 
Notifications
Clear all

Power Query and Active Directory....slow

2 Posts
1 Users
0 Reactions
542 Views
(@wilkies0106awhotmail-com)
Posts: 19
Eminent Member
Topic starter
 

Hi all,

I'm using Power Query to import a list of users (~2000) from Active Directory (Azure).

I am only keeping the initial displayName column, then the user and OrganizationalPerson tables.
I expand the user table to find the UserAccountControl number
These results are filtered to find active users (512).
I expand the manager table to find the manager of an active user.
I expand the OrganizationalPerson table to find department and office.

If I just sit and watch my results refresh, it seems to take a long time.

I don't appear to be able to run/find the Diagnostics tab in my version of Power Query, so I can't see the actual numbers..

I did split each expansion into it's own query - even worse. Scrapped that idea.

I've rearranged the order in which tables are expanded - this seems to have add the biggest effect in terms of speed to load the list of users. (Currently, it's not in the data model as I'm checking results.) FYI - expand OrganizationalPerson first, then manager
I've consolidated the same functions, where I can.
I've filtered my results as early as I can, I think.

To get the same results via PowerShell, doesn't take as long. Yet, I can't get those who will actually be using the report to run PowerShell every time.

Any other ideas how I can make it quicker.

Active Directory is made up of tables, yet I could not figure out how they are all joined together via IDs?
Which would make it quicker to pull the results needed?

Thanks
A

 
Posted : 17/10/2023 5:43 am
(@wilkies0106awhotmail-com)
Posts: 19
Eminent Member
Topic starter
 

I've got it down to ~30 - 45 seconds.
Which if you are only looking at the screen, seems to be a very long time.
The key appears to be the order I expand AD tables in, as noted above.

Thanks

A

 
Posted : 20/10/2023 3:20 pm
Share: