Cognos outer join to support 0:n relationship showing joined column only once

[jackg_tor]

Hi,

I'm trying to get Cognos to do an outer join on some tables in different data sources and it seems to be missing data from columns.  Here are the (simplified) details:

I have the following tables:

Data source 1:
Defect
  id              primary key
  state         varchar

APARS
  id              primary key
  aparname   varchar

defect_apar  (to support n-n relationship of defects and apars)
  defectid     foreign key
  aparid        foreign key

Data source 2:
pmr_status
  pmrno        integer
  apar          matches format of APAR.aparname
  status       char

Each APAR can have 0-n PMRs, each PMR can have 0-1 APARS and those cardinalities are reflected in the Framework Manager model.

The report is a simple list table consisting of the following columns:

defect.id
defect.state
APARS.id
APARS.aparname
pmr_status.pmrno
pmr_status.status

Here is part of the output:

defect id         State        APAR id          APAR name          pmr no         pmr status
                                    idsdb00185114 IC61264              35716 C
idsdb00185146 Opened     idsdb00185148 IC61295              04803 C
                                    idsdb00185148 IC61295              56982 C
                                    idsdb00185233 IC61298              58337 C

The problem in this example is that defect 185146 corresponding to APAR 185148 is appearing in only the first row of that APAR instead of all the rows so it's not immediately evident that there are multiple pmrs for the single apar/defect.  Shouldn't an outer join (or any join) fill in values wherever possible?  More importantly, I'm actually not interested in APARs that have no corresponding defect so the third row above is hidden by a post-aggregation filter, obscuring the fact that there are multiple pmrs.

Does anyone have any ideas on how I might get this to behave the way I want?

Here is the generated Cognos SQL:

with
D as
    (select
           defect.ID  as  DEFECT_ID,
           defect."STATE"  as  STATE2,
           APARS.ID  as  APAR_ID,
           APARS.APARNAME  as  APARNAME,
           RSUM(1  at APARS.ID,APARS.APARNAME,defect.ID,defect."STATE"  for APARS.ID,APARS.APARNAME  order by APARS.ID asc,APARS.APARNAME asc,defect.ID asc,defect."STATE" asc  local)  as  sc
     from
           imitwh..REPORTDB.defect defect,
           imitwh..REPORTDB.APARS APARS,
           imitwh..REPORTDB.DEFECT_APARS DEFECT_APARS
     where
           (defect.ID = DEFECT_APARS.DEFECTID) and
           (APARS.ID = DEFECT_APARS.APARID)
     group by
           defect.ID,
           defect."STATE",
           APARS.ID,
           APARS.APARNAME
     order by
           APAR_ID asc,
           APARNAME asc,
           DEFECT_ID asc,
           STATE2 asc
    ),
D3 as
    (select
           APARS.ID  as  APAR_ID,
           APARS.APARNAME  as  APARNAME,
           PMR_STATUS.PMRNO  as  PMRNO,
           PMR_STATUS.STATUS  as  PMR_STATUS,
           RSUM(1  at APARS.ID,APARS.APARNAME,PMR_STATUS.PMRNO,PMR_STATUS.STATUS  for APARS.ID,APARS.APARNAME  order by APARS.ID asc,APARS.APARNAME asc,PMR_STATUS.PMRNO asc,PMR_STATUS.STATUS asc  local)  as  sc
     from
           imitwh..REPORTDB.APARS APARS
            full outer join
           servdb3..SERVDB.PMR_STATUS PMR_STATUS
            on (APARS.APARNAME = PMR_STATUS.APAR)
     where
           (PMR_STATUS.PRODID in ('0288BNS  ')) and
           (PMR_STATUS.APAR is not NULL) and
           (PMR_STATUS.APAR <> '')
     group by
           APARS.ID,
           APARS.APARNAME,
           PMR_STATUS.PMRNO,
           PMR_STATUS.STATUS
     order by
           APAR_ID asc,
           APARNAME asc,
           PMRNO asc,
           PMR_STATUS asc
    )
select
       D.DEFECT_ID  as  DEFECT_ID,
       D.STATE2  as  STATE2,
       coalesce(D.APAR_ID,D3.APAR_ID)  as  APAR_ID,
       coalesce(D.APARNAME,D3.APARNAME)  as  APARNAME,
       D3.PMRNO  as  PMRNO,
       D3.PMR_STATUS  as  PMR_STATUS
from
       D
        full outer join
       D3
        on (((D.APAR_ID = D3.APAR_ID) and (D.APARNAME = D3.APARNAME)) and (D.sc = D3.sc))
order by
       APAR_ID asc,
       DEFECT_ID asc,
       PMRNO asc

Thanks,
Jack Goldstein

[cognostechie]

This is a 'stitched' query (case of conformed dimension). The first two queries are running independently (D and D3) and then getting stitched together. In a conformed dimension, display of this type is possible.

I think the problem is there are no facts so Cognos is treating all the query subjects as dimensions resulting in treating it as conformed dimension, atleast in case of 'defect_apar' .
   

[technomorph]

I think the grouping is per design. If you check the determinants properties within the Framework model for the dimension containing the defect id, I suspect you'll find that it has the Group By option checked. This will have been configured to avoid double-counting of facts. If your model doesn't need to be modelled dimensionally, you could change your joins to 1..0 and 1..1 i.e. avoid using 1..n and 0..n. This will create standard joins and avoid the query engine going down the stitch-query route.

Cheers

[jackg_tor]

Thanks to both of you for your quick replies but I'm still stuck.

I am not familiar with conformed dimensions but googling the term, it does seem that my apar name is such a thing.  However, I don't understand why that's causing problems.  If you think it would help, I can add some fake facts but I don't understand what I can put in that will change the display of the columns I want.

As for changing the cardinality to 0-1 or 1-1, I forgot to mention that I had already tried that.  I believe it changes the Cognos join to an inner one and does fill in the rows as I asked for above but omits any APARs that have no associated pmrs and I really do want to include those.

Any more suggestions?

Thanks,
Jack Goldstein