none
Maintaining Query Folding with AD RRS feed

  • Question

  • Hi guys

    My company returns multiple domains (split into country) when executing ActiveDirectory.Domains().

    If I write a function to query a single country for a user then the query will query fold and returns results in a few seconds, for example:

    let
        result= (string_sAMAccountName as text) =>

    let
    Source = ActiveDirectory.Domains("company.com"),
    company.com = Source{[Domain="us.company.com"]}[#"Object Categories"],
    user = company.com{[Category="user"]}[Objects],
    #"Expanded securityPrincipal" = Table.ExpandRecordColumn(user, "securityPrincipal", {"sAMAccountName"}, {"sAMAccountName"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded securityPrincipal",{"displayName", "user", "organizationalPerson", "person", "sAMAccountName"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([sAMAccountName] = string_sAMAccountName)),
    #"Expanded person" = Table.ExpandRecordColumn(#"Filtered Rows", "person", {"cn", "sn", "telephoneNumber"}, {"cn", "sn", "telephoneNumber"}),
    #"Expanded organizationalPerson" = Table.ExpandRecordColumn(#"Expanded person", "organizationalPerson", {"c", "co", "company"}, {"c", "co", "company"}),
    #"Expanded user" = Table.ExpandRecordColumn(#"Expanded organizationalPerson", "user", {"lastLogon"}, {"lastLogon"})
    in
    #"Expanded user"

    in result

    However, if I expand similar logic to all domains then the results do not query fold and results are returned after about 9 hours:

    let
        result= (string_sAMAccountName as text) =>

    let
        Source = ActiveDirectory.Domains("company.com"),
        #"Expanded Object Categories" = Table.ExpandTableColumn(Source, "Object Categories", {"Category", "Objects"}, {"Category", "Objects"}),
        #"Filtered Rows1" = Table.SelectRows(#"Expanded Object Categories", each [Category] = "user"),
        #"Expanded Objects" = Table.ExpandTableColumn(#"Filtered Rows1", "Objects", {"securityPrincipal", "user"}, {"securityPrincipal", "user"}),
        #"Expanded securityPrincipal" = Table.ExpandRecordColumn(#"Expanded Objects", "securityPrincipal", {"sAMAccountName"}, {"sAMAccountName"}),
        #"Filtered Rows" = Table.SelectRows(#"Expanded securityPrincipal", each ([sAMAccountName] = string_sAMAccountName ))
    in
        #"Filtered Rows"

    in result

    Is there a way to perform this while maintaining query folding?

    Thanks,
    Simon

    Tuesday, November 1, 2016 1:06 PM

Answers

  • This is arguably a flaw in the connector, but it doesn't currently have high-enough usage to justify the fairly large amount of work required to address the limitation. Instead, you can work around it by applying the transformations to the unexpanded value inside the table instead of expanding it and then applying the transformations. The result might look like this:

    let
        OneDomain = (domain as table, string_sAMAccountName as text) =>
            let
                user = domain{[Category="user"]}[Objects],
                #"Expanded securityPrincipal" = Table.ExpandRecordColumn(user, "securityPrincipal", {"sAMAccountName"}, {"sAMAccountName"}),
                #"Removed Other Columns" = Table.SelectColumns(#"Expanded securityPrincipal",{"displayName", "user", "organizationalPerson", "person", "sAMAccountName"}),
                #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([sAMAccountName] = string_sAMAccountName)),
                #"Expanded person" = Table.ExpandRecordColumn(#"Filtered Rows", "person", {"cn", "sn", "telephoneNumber"}, {"cn", "sn", "telephoneNumber"}),
                #"Expanded organizationalPerson" = Table.ExpandRecordColumn(#"Expanded person", "organizationalPerson", {"c", "co", "company"}, {"c", "co", "company"}),
                #"Expanded user" = Table.ExpandRecordColumn(#"Expanded organizationalPerson", "user", {"lastLogon"}, {"lastLogon"})
            in
                #"Expanded user",
        result = (string_sAMAccountName as text) =>
            let
                Source = ActiveDirectory.Domains("company.com"),
                DomainList = Source[#"Object Categories"],
                Combined = Table.Combine(List.Transform(DomainList, each OneDomain(_, string_sAMAccountName)))
            in
                Combined
    in
        result

    • Proposed as answer by Curt Hagenlocher Monday, November 7, 2016 5:07 PM
    • Marked as answer by Simon N Wednesday, November 9, 2016 12:58 PM
    Monday, November 7, 2016 5:06 PM

All replies

  • This is arguably a flaw in the connector, but it doesn't currently have high-enough usage to justify the fairly large amount of work required to address the limitation. Instead, you can work around it by applying the transformations to the unexpanded value inside the table instead of expanding it and then applying the transformations. The result might look like this:

    let
        OneDomain = (domain as table, string_sAMAccountName as text) =>
            let
                user = domain{[Category="user"]}[Objects],
                #"Expanded securityPrincipal" = Table.ExpandRecordColumn(user, "securityPrincipal", {"sAMAccountName"}, {"sAMAccountName"}),
                #"Removed Other Columns" = Table.SelectColumns(#"Expanded securityPrincipal",{"displayName", "user", "organizationalPerson", "person", "sAMAccountName"}),
                #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([sAMAccountName] = string_sAMAccountName)),
                #"Expanded person" = Table.ExpandRecordColumn(#"Filtered Rows", "person", {"cn", "sn", "telephoneNumber"}, {"cn", "sn", "telephoneNumber"}),
                #"Expanded organizationalPerson" = Table.ExpandRecordColumn(#"Expanded person", "organizationalPerson", {"c", "co", "company"}, {"c", "co", "company"}),
                #"Expanded user" = Table.ExpandRecordColumn(#"Expanded organizationalPerson", "user", {"lastLogon"}, {"lastLogon"})
            in
                #"Expanded user",
        result = (string_sAMAccountName as text) =>
            let
                Source = ActiveDirectory.Domains("company.com"),
                DomainList = Source[#"Object Categories"],
                Combined = Table.Combine(List.Transform(DomainList, each OneDomain(_, string_sAMAccountName)))
            in
                Combined
    in
        result

    • Proposed as answer by Curt Hagenlocher Monday, November 7, 2016 5:07 PM
    • Marked as answer by Simon N Wednesday, November 9, 2016 12:58 PM
    Monday, November 7, 2016 5:06 PM
  • Thanks Curt!
    Wednesday, November 9, 2016 1:00 PM