Defined Relationship in Framework manager ignored by new query subject

[annemieke]

The model I defined in Framework Manager has relationships between fact table and dimension tables. The relationship is not only defined on the key of the tables but also on an attribute field containing a specified value.  Previously, when I defined a query subject on the dimension the extra value came along when I looked into the preview of the query. Now after adding new dimension tables I am not able any more to use the relationship defined when I preview the query in the query subject. So first the query subject looked like this:

select
   T1.CON_NATURAL_KEY as CON_NATURAL_KEY
from
   (select
      FDM_OVEREENKOMST_INFO_CURRENT.CON_NATURAL_KEY as CON_NATURAL_KEY
   from
      B2BFDM..FDM.FDM_OVEREENKOMST_INFO FDM_OVEREENKOMST_INFO_CURRENT
   where
      (FDM_OVEREENKOMST_INFO_CURRENT.CON_LAST_IN_SERIES = 'Y')
   ) T1

And now it is not a nested query anymore but just a flat query without the extra relationship definition which was present in the where clause:

select
   test_overeenkomst_datasource.CON_NATURAL_KEY as CON_NATURAL_KEY
from
   B2BFDM..FDM.FDM_OVEREENKOMST_INFO test_overeenkomst_datasource


I am desperate !!! How can I force the framework to look at the relationship defined. Thanks for your response

[rockytopmark]

Not sure I completely understand what you are looking for here.  ???

Actually, I would prefer the 2nd SQL as it is requesting only the information your dataset requires, where as the first SQL is getting more than is required for the anticipated dataset.

By default, Cognos 8 will request only the columns that are needed to satisfy the requirements.  If you have a relationship, but columns from only 1 of those related tables is used in a Model Query Subject, I would expect the resulting SQL to NOT include the other table.  As soon as you add a column from the other table to the Query Subject, your SQL will indeed show the 2 tables and their defined relationship.

[annemieke]

Hi,

Thanks for your reply!! Sharing these problems is very helpful. You understood the situation correctly. Indeed i can live with the explanation that the query subject for the dimension picks up all records from the one table without looking at the relationship restriction to the fact table but the strange thing is that previously it did look at the restriction in the relationship (only the hardcoded filter in the relationship, not the join) , which i think is much more convenient. It seems that when I import a new table from the datasource now somehow a different setting is used compared to what I used earlier or maybe changed data in the database cause the difference when defining new tables in the model. Well I think i have to define real filters on the dimension tables now to have the same effect as before.  Thanks again. More tips welcome........

[blom0344]

By default, Cognos 8 will request only the columns that are needed to satisfy the requirements. 

That is very definately untrue for - in any case - version 8.1.
In our case we logged a case with Cognos that the SQL composed introduced joins even if no joins where needed to return the data requested.

We witness that behavior has changed in a number of ways since we upgraded to version 8.2.
Perhaps this could explain the behavior you are witnessing?

[rockytopmark]

Hmmm... then I would suggest your model has some Datasource SQL Query Subjects with these joins and/or their SQL mode has been changed to View.  Making the Query Subject simulate a View may force more columns than needed.  I would be curious to know how else would Cognos know to add columns and tables to the request?!?

Cognos 8 has always sent minimized SQL requests, as far as I know.  Changing to View will alter that.

[blom0344]

In version 8.1 we noticed that the minimized SQL option did not always work. I remember showing it to my course teacher during Fasttrack early this year. Quite possibly this has been fixed in 8.2..