Cognos Analytics

Cognos Analytics

Connect, learn, and share with thousands of IBM Cognos Analytics users! 

 View Only
  • 1.  Data Module - Setting View Item List to Used Ignores Join

    Posted Thu September 03, 2020 08:59 PM
    Hello,

    I created a view within data module that references two joined tables. There are quite a few columns in the view, so I decided it would be best to set property item list = used. The problem I observe is joins are being eliminated when applying that property. Joins are only enforced when including all items (or set to automatic).

    It's reminding me of the minimized SQL on in Framework Manager. So the way I figured to get around the missing join is to add a filter condition to the view that is table1.join_key = table2.join_key. This will enforce the join while being able to use the data module version of minimized SQL.

    My question is: is this a suitable practice? Or perhaps there is another best practice you could suggest? 


    Thanks,
    Jeremy

    ------------------------------
    Jeremy Aude
    Channell Commercial Corporation
    Rockwall TX
    ------------------------------

    #CognosAnalyticswithWatson


  • 2.  RE: Data Module - Setting View Item List to Used Ignores Join

    Posted Fri September 04, 2020 12:32 AM

    Hi Jeremy,
    Instead of creating a <view>, you could create a <joined view>, you'll have to enter the relationship between the tables once more, because a <joined view> owns its relationship. The advantage would be that you wouldn't have the extra filter definition laying around and the generated sql wouldn't have duplicated info in the ON-clause of the join and the WHERE clause of the query.
    // Henk



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



  • 3.  RE: Data Module - Setting View Item List to Used Ignores Join

    Posted Fri September 04, 2020 06:01 AM
    Edited by System Admin Fri January 20, 2023 04:35 PM
    Ahh that makes sense! Thank you so much Henk!

    ------------------------------
    Jeremy Aude

    ------------------------------