Cognos Analytics

 View Only

  • 1.  Many-to-many relationship

    Posted Thu February 13, 2020 03:27 PM
    I have two data sources with one common field, but unfortunately they don't have a one-to-many or one-to-one relationship.  I thought that maybe putting one of the queries into a crosstab would make it one-to-many.  But based on the results of the joined query, it is still reading the original source as 'many'.  I'm not sure if that really makes sense to anyone else.  I really just want it to function as a vlookup.  But the left-side of the vlookup has duplicates that I want it to consolidate and then doing the join.

    ------------------------------
    Mark Bruzek
    ------------------------------

    #CognosAnalyticswithWatson


  • 2.  RE: Many-to-many relationship

    Posted Thu February 13, 2020 05:21 PM
    Try with coalesce on all the data items you pull into the join query.

    Coalesce( [Q1].[DataItem],[Q2].[DataItem] )

    Else i am not sure i understand you q.

    ------------------------------
    Morten Frandsen
    ------------------------------

    Original Message


  • 3.  RE: Many-to-many relationship

    Posted Thu February 13, 2020 05:49 PM

    You could try to model bridge tables in your model.  You can model them in FM models and CA data modules. 

    https://www.ibm.com/support/knowledgecenter/SSEP7J_11.1.0/com.ibm.swg.ba.cognos.ug_fm.doc/c_dyn_query_bridge_tables.html

    https://www.ibm.com/support/knowledgecenter/SSEP7J_11.0.0/com.ibm.swg.ba.cognos.ug_fm.doc/t_dqm_brdg_tble.html



    ------------------------------
    IAN HENDERSON
    ------------------------------

    Original Message


  • 4.  RE: Many-to-many relationship

    Posted Thu February 13, 2020 08:38 PM
    Hi Mark,
    You can model this either using Data Modules or Framework Manager models, the principle is the same.
    I'll use the Data Module Terminology.
    The lookup-table in raw format say it's defined as LTRaw(col1, col2, code_col, description, col5, col6 etc).
    Select this table and from the context menu select New Table... then Create a View.
    Select only the code_col and description. Create the view.
    Let's call this table/view LTView.
    Select it and from the context menu select Properties... In that panel open up Advanced. Set Usage to Summary.
    The data in the grid should now reflect all the unique code,description combinations.
    I'm going by the fact that the code field is going to be unique.
    In case the code field is not unique
    - in the LTView, select the description field
    - in the properties, set usage=measure (even though it isn't) and set the aggregate=minimum.
    - refresh the grid view. That should now show a single row for each unique code field.
    - in CA 11.1.5 you can select the view and look at the generated SQL and see how it does the summarizing.

    The data-table in raw format say it is defined as DATARaw(col1, col2, code, col3, col4)
    Join from the data-table to the LTView set it as a N on the data-table side and a 0-1 on the LTView side.

    The Framework Manager version would be similar, instead of creating a new view you would create a Model Query Subject.
    It has a usage that can be set to summary.
    You will have to tinker with the aggregate settings to make a single record per code value.
    Joins can be made the same way.

    //Henk

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

    Original Message


  • 5.  RE: Many-to-many relationship

    Posted Tue February 25, 2020 02:49 PM
    Edited by System Fri January 20, 2023 04:09 PM
    The solution to my problem ended up being embarrassingly simple.  After enough toying around, I determined that my custom calculation fields did not have the proper detail/summary aggregation settings for the report.  Unfortunately, our internal IT department does not support Cognos as much as they should.

    ------------------------------
    Mark Bruzek
    ------------------------------

    Original Message