Maximum of 2100 parameters in dimension set filter

[sgiordy]

Maximum of 2100 parameters in dimension set filter

[JesperSchulz]

Update: This issue is being worked on.

[grobyns]

Hi,

Whether or not you will hit this SQL limit should depend on the filter you set. You may hit the limit with one filter and not with another. If you do hit the SQL limit there's unfortunately not much we can do at this time...

If you could share your repro, I can take a look if there's something else going wrong in this scenario.

br/Gert

[sgiordy]

Hi @grobyns

I'm sorry but you are wrong. Is not the user that sets 2100 parameters, but the Dimension Filter page!

and this is the result:

You need only 20K of G/L Entries and 30K of Dimension Set Entries to reproduce the issue or use a real customer database or use the stress test codeunit that I've attached.

Test.al.txt

Actually Dimension Filter is unusable in a real scenario:

I think this issue is partially related to #730 but anyway, in this scenario, Dimension Set Filter will never work. Maybe a new type of field such as "Filter on Related Table" can resolve the issue applying the WHERE clause in "Dimension Set Entry" table instead of passing thousand of parameters for "Dimension Set ID" in "G/L Entry".

S.

[grobyns]

Hi,

You misunderstood my reply. The filter set by the page is determined by what the user enters on the Dimension Filter page, this is what I meant. Depending on what the user enters the filter may or may not exceed the SQL limit.
Knowing whether it will or will not exceed the SQL limit is not simple. The page will construct a simple filter by concatenating all the dimension set id's that match what the user put in. This can be in the hundreds of thousands of Dimension Set ID's, the number or length really doesn't matter. The server will take this simple filter and optimize it. As a simple analogy, if you pass in '1|2|3|4|5' the server will turn it into '1..5'. This optimized filter is then passed to SQL and if that contains more than 2100 elements, SQL rejects it.

In your case, if you are lucky, the filter ends up having a lot of consecutive ranges and the server can simplify it down to something SQL can manage. If you are unlucky, then it cannot. Of course you can help by putting in more specific filters. If you put in a specific value for Category then your filter is much more likely to pass. If instead you put in a specific value for Customer you will be less likely to get a filter SQL can handle.

The Dimension Filter page is meant to be a quick ad-hoc way of getting some insights out of the dimensions. It is not meant to replace Analysis by Dimensions or PowerBI to do analysis and reporting. If this feature doesn't work for you, you'll need to rely on these other reporting mechanisms to get the data you need.

br/Gert

[sgiordy]

Hi @grobyns

I hope you consider a new application enhancement, such as query optimizer for pages with flowfield / factboxes. The issue is caused by query generator, a simple JOIN could bypass the SQL limit.

We cannot answer to the customer: "Try... if you are lucky, you get the response!"

It's not a problem to have unusable function inside NAV/365BC ... is a problem if you remove us the possibility to maintain indexes and flowfield (#730) ... so a case like this is critical.

Thank you.
S.

[JesperSchulz]

Fixing this turned out to require a larger effort. We have various efforts in motion, but it will take us some time to get to the bottom of this. I will close this issue, as we will not report back progress on the larger efforts here in this repo, which is about extensibility issues. Thank you for reporting this issue.