Cognos Analytics

Expand all | Collapse all

Data Module query with clause

  • 1.  Data Module query with clause

    Posted Mon April 13, 2020 04:17 PM
    Hi All,

    Is there any way to avoid "With Clause" for query generated from Data Module similar to FM and Report Studio?

    I am using CA 11.1.3.

    Thanks for any inputs. 


    ------------------------------
    Buddhi Gupta
    ------------------------------


  • 2.  RE: Data Module query with clause

    Posted Fri April 17, 2020 12:34 AM

    Hi Buddhi,
    There is no UI for controlling that aspect.
    Is there a specific problem that is caused by this?

    // Henk



    ------------------------------
    HENK CAZEMIER
    ------------------------------



  • 3.  RE: Data Module query with clause

    Posted Fri April 17, 2020 08:40 AM
    I am seeing it always get all columns in with clause for underlying tables instead of just getting columns and joins I need for reporting. This has performance impact when underlying table have more columns. 


    ------------------------------
    Buddhi Gupta
    ------------------------------



  • 4.  RE: Data Module query with clause

    Posted Wed April 22, 2020 05:34 PM

    Hi Buddhi,
    Sorry for the delayed response.
    Whether the with-clause governor is turned on or not doesn't make a difference with respect to the length of the projection list.
    In Data Modules you can control this with the recently introduced advanced property: Item List that is part of the table.properties, it has the settings: automatic, all, used. Setting it to 'used' should give you a reduced list when that table is used in another query. You will not see any effect in the query that are used to populate the grid in Modeling UI for this table. When you create a view on this table and are only using a subset of the columns then there will be a difference in the generated sql. We recently found an issue in this feature, which causes incorrect sql generation. The root cause isn't quite clear, so please test when using this feature just to make sure it works properly. Leaving it set to the default hasn't cause any issues.
    // Henk



    ------------------------------
    HENK CAZEMIER
    ------------------------------



  • 5.  RE: Data Module query with clause

    Posted Fri April 24, 2020 11:02 AM
    Thanks @HENK CAZEMIER!  Once we upgrade to latest release of CA 11.1.x, I will check this out. ​

    ------------------------------
    Buddhi Gupta
    ------------------------------