Smart Columns - danger with query construction

Follow

Steve Hamilton

• October 30, 2019 16:07

Frustrating behaviours with smart columns

MooD 15 - Accepts a Source query that include recursion

MooD 15 - Rejects a query with recursion that is used in the fact builder

Mood 16 - Rejects a Source query that include recursion

I have not investigated the MooD 16 support for recursive queries in the fact builder.

This seems like a bug - deconstructing a recursive query is not ideal - is there any workaround?

 

1

• 

  Matthew Dodd

  • December 04, 2019 12:35

  Hi Steve, I am investigating this.

  There is a workaround which is to set the recursive query to be cached (I haven't tested this but it should work). Note you will then have to manage when to delete the cache via a Manage Cache Synchroniser.

  0

  Comment actions Permalink
• 

  Matthew Dodd

  • December 04, 2019 13:26
  • Edited

  The recursive query option has never been able to produce SQL. From memory what used to happen is that the source query was executed separately and then all the element IDs remembered. These IDs were then used as the source query for the aggregation. This is slow for large numbers of records and has been removed. I'm looking in to adding it back again and for the full rationale for its removal.

  The better solution is that recursive queries DO produce SQL but this goes beyond the scope of what we can do in a hotfix to build 57.

  We have also implemented "Find variant ancestors" and "Find variant descendants" for build 59+ which I imagine you'll make use of when we next release?

  0

  Comment actions Permalink
• 

  Matthew Dodd

  • December 05, 2019 17:16
  • Edited

  This will be fixed in 16 Build 57 hotfix 1 and is logged as issue 11890

  A workaround for Aggregation Matrices is to select a different Execution Method in the ribbon: it's mainly the fastest 'Simple Group By' that exhibits the problem.

   

  0

  Comment actions Permalink

Please sign in to leave a comment.