Cognos Analytics

 View Only
Expand all | Collapse all

XQE-PLN-0355 Filter join optimization between 'Query1' and 'Query2' is not applied because more than 10,000 values are needed."

  • 1.  XQE-PLN-0355 Filter join optimization between 'Query1' and 'Query2' is not applied because more than 10,000 values are needed."

    Posted Tue July 05, 2022 12:41 PM
    Edited by System Fri January 20, 2023 04:16 PM
    HI, after having created a data module with an excel and a package ,in a report query we join items coming from excel and a package.

    In the DQM filter type feature, we set IN operator and the report return this error.
    "
    XQE-PLN-0355 Filter join optimization between 'Query1' and 'Query2' is not applied
    because more than 10,000 values are needed."

    It is possible to override and modify some setting inside cognos configuration the limit of 10000 rows? we use very large excel list ,often more than 30000 rows.
    we have checked this note but nothing talks about this possibility.:
    https://www.ibm.com/docs/en/cognos-analytics/11.2.0?topic=components-xqe-pln-filter-join-optimization-error

    Thanks

    ------------------------------
    Leonard
    ------------------------------
    #CognosAnalyticswithWatson


  • 2.  RE: XQE-PLN-0355 Filter join optimization between 'Query1' and 'Query2' is not applied because more than 10,000 values are needed."

    Posted Tue July 05, 2022 08:10 PM

    I'd encourage you to review what you are trying to do.

    Large in-lists in general are not ideal for query execution in predicate evaluations, which per the link you read can result in memory exhaustion etc.



    ------------------------------
    NIGEL CAMPBELL
    ------------------------------



  • 3.  RE: XQE-PLN-0355 Filter join optimization between 'Query1' and 'Query2' is not applied because more than 10,000 values are needed."

    Posted Thu July 07, 2022 04:17 AM
    Hi Mr Campbell, thanks for your reply and kind suggestion.
    i would like to test this before saying NO to my customer. and this because they have massively used data module with this combination and all this cause a lot of stress on the Cognos server because the part coming from the database is very large.

    so i have suggested to use this optimization that seemed to resolve this resource degradation but...
    the between clause it's too dispersive and imprecise. and performance are not good comparing to ones when IN operator is used.
    this limit of 10000 rows i guess it could be a little expanded, if it could be possibile.

    better than making a local join on cognos side ,i prefer to put more stress on our DB2 that crunches a lot better those rows.

    So my ask: it's possible to configure this number on DQM filter join  optimizaztion?

    thanks

    ------------------------------
    Leonard
    ------------------------------