Author Topic: Missing Foreign Key when creating a join in my Data Module  (Read 463 times)

Offline bswenson

  • Associate
  • **
  • Join Date: Sep 2016
  • Posts: 4
  • Forum Citizenship: +0/-0
I am trying to create my first Data Module.  I got the list of tables in my schema.  I added the 6 tables required.  I was able to create relationships between all of them but 2.  The foreign key I need does not display in the drop down on the Join screen.  I can see the foreign key i need if I expand the table on the left side of the Data Module screen.  I can see the foreign key i need on the join screen when it shows a couple rows of data from the table, but it does not show in the drop down for the join.  The drop down seems to be filtered somehow.  Anyone else have this issue?

In the attachment, FK STAFFfk Persid is the key i need but its not in the drop down.

Thanks!

Bill

Offline bus_pass_man

  • Community Leader
  • *****
  • Join Date: May 2008
  • Posts: 221
  • Forum Citizenship: +28/-0
Re: Missing Foreign Key when creating a join in my Data Module
« Reply #1 on: 26 Sep 2016 11:57:15 am »
What's the usage of  FK STAFFfk Persid ?  I'm guessing that it's measure. 

The list will exclude measures. Only identifiers and attributes will show up.  The former will be bold font.  The latter won't. 

Try setting the usage to identifier.


Offline bswenson

  • Associate
  • **
  • Join Date: Sep 2016
  • Posts: 4
  • Forum Citizenship: +0/-0
Re: Missing Foreign Key when creating a join in my Data Module
« Reply #2 on: 26 Sep 2016 12:36:32 pm »
Yes!  That was it.  Thank you Bus Pass Man.

Will Cognos remember that it is an Identifier instead of a measure for future Data Modules?

Offline bus_pass_man

  • Community Leader
  • *****
  • Join Date: May 2008
  • Posts: 221
  • Forum Citizenship: +28/-0
Re: Missing Foreign Key when creating a join in my Data Module
« Reply #3 on: 27 Sep 2016 06:39:41 pm »
No.  That metadata is coming from your data base and, because the column doesn't have PK/FK defined for it, it is getting assigned a usage of measure.  It's just like FM and Cube Designer.  If you imported your data base into them you would see similar results.  In general columns which participate in RI (either as pk or fk) will be marked as identifiers.  Columns which are text or datetime are attributes.  Numeric data type columns are the tricky ones.   Are they measures?  Are they attributes? Or are they identifiers without pk/fk defined in the underlying data base, which I think was your case.

Any module created using your module will have the usage which you have assigned to the query item.

After importing metadata you ought to inspect the results to confirm that every query item has been assigned a usage which conforms with your understanding of role of its source column.  You ought to correct any miss-assignment. 




 



           
Twittear