Torsten - great question, I have seen apps where the IN clause approaches 10000 entries which is really ugly. I will check with our dev folks, but in terms of general rdbms best practice, what would you suggest as a better mechanism?
I think you have a table (fact table) in dbms and one or more tables in qlikview, in your case 2 tables
can you join the 2 tables in dbms? no, because calendar is a qlikview table; so you can only send a where clause to the dbms for selecting data from fact table; where Date in (.....) is the simpler solution. Perhaps a between would be better but it is more difficult to generate.
Don't know if using a single table (Calendar + DirectDiscoveryTable) in dbms can be useful or if using two tables in dbms and a view for querying them in Qlikview can be useful.
IMHO if you can stay on Qlikview side (I mean time to reload Qlik doc, hardware to support your big fact table, etc....) don't use direct query.
Another thing that could be usefut (from online help)
Native data-source syntax
By design, the DIRECT QUERY statement is data-source neutral for data sources that support SQL. For that reason, the same DIRECT QUERY statement can be used for different SQL databases without change. Direct Discovery generates database-appropriate queries as needed.
Native data-source syntax can be used when the user knows the database to be queried and wants to exploit database-specific extensions to SQL. Native data-source syntax is supported:
As field expressions in DIMENSION and MEASURE clauses
You're right in the first test the Calendar was a QlikView-Table. But we are able to put the Date-Dimensions into the fact table. But if we do this with every dimension we got fact-tables with many columns. We will do some tests and i will post an update when i got results.