Hello, we're working on a Power BI solution that involves a composite model with Vertipaq tables and DirectQuery Dataverse tables. This entails Power BI querying Dataverse directly but first injecting those queries with literals based on the relationships in the composite model (or on other aspects of the DAX being used).
However, we've realized that there seems to be a limit on the size of the queries which Dataverse will accept. We get this error when using the standard Dataverse (CDS) connector:
"OLE DB or ODBC error: [DataSource.Error] Microsoft SQL: {"Message":"Too many literal values were passed in the query. The maximum number is 2100}"
Similarly, if we connect to the TDS endpoint using the SQL Server connector, we can produce this error when running a query:
"One or more errors occurred. {"Message":"Too many literal binary expressions in the query. The maximum number is 200"} The exception was raised by the IDbCommand interface."
This is a pretty significant limitation from a reporting POV. Injecting literals is the standard Power BI behavior for composite models. 2100 values is not much to work with, especially if you have dimension tables that are larger than that.
Questions:
- Is it possible to raise this limit of 2100 literals in queries that Dataverse will accept?
- Is there a way to circumvent the limit?
- Where is this limitation documented? We have not been able to find the location.
Many thanks
