Author Topic: Data module relationship type and cardinality  (Read 530 times)

Offline vijay857

  • Full Member
  • ***
  • Join Date: May 2020
  • Posts: 5
  • Forum Citizenship: +0/-0
Data module relationship type and cardinality
« on: 06 May 2020 04:21:49 pm »
Could you suggest me the correct relationship type and cardinality for the following requirement?

I have 2 tables.
Table 1) Task info --> it includes project name, task name, is Billable. Assignee name
Table 2) Work Entries--> Date, Project name, task name, resource name, Hours worked

The final requirement is to: Develop a dashboard with each resource total no. of hours worked, Billable hours and non-billable hours.

PS: I'm new to data modeling. I have tried 1 to Many cardinalities and inner join/right outer join relationship type. And, set the Usage as Measure and Aggregation to Total for 'Hours Worked'.
Matched columns on Project name, Task name, assignee name.

Test reports are working perfectly when aggregation of 'Hours worked' set to 'None'. But when calculating Billable hours its resulting in duplicate values.

Please suggest. Thank you.

Offline bus_pass_man

  • Statesman
  • ******
  • Join Date: May 2008
  • Posts: 435
  • Forum Citizenship: +43/-0
Re: Data module relationship type and cardinality
« Reply #1 on: 06 May 2020 07:16:58 pm »
When you are determining the cardinality of a relationship between two tables you are asking yourself, what is the relationship between the entities in one table and the other?

How many work entities are there for any task info? Or is it how many task infos exist for any work entity? I think from what you say for any work entity there can be many task infos.

Is it possible that a task can have more than one work entity as well? i.e. is this a bridge table scenario?


What happens if you set the usage of hours worked to attribute?  Is it really a measure or an attribute?


Offline vijay857

  • Full Member
  • ***
  • Join Date: May 2020
  • Posts: 5
  • Forum Citizenship: +0/-0
Re: Data module relationship type and cardinality
« Reply #2 on: 07 May 2020 01:09:02 am »
Here, each task will have zero to Many work entries. one or more resources might work for each task and create work entries.
Hours worked is a measure used for identifying the total number of hours each resource worked for a task on any given day.
I would be happy to provide if any further information needed.
 Thank you.

Offline bus_pass_man

  • Statesman
  • ******
  • Join Date: May 2008
  • Posts: 435
  • Forum Citizenship: +43/-0
Re: Data module relationship type and cardinality
« Reply #3 on: 07 May 2020 07:22:07 am »
Quote
Here, each task will have zero to Many work entries. one or more resources might work for each task and create work entries.

So it's a bridge table scenario.

Offline vijay857

  • Full Member
  • ***
  • Join Date: May 2020
  • Posts: 5
  • Forum Citizenship: +0/-0
Re: Data module relationship type and cardinality
« Reply #4 on: 07 May 2020 07:45:06 am »
Yes. Could you suggest the best approach for creating a data module using a bridge table relationship? or any source/reference would also be of great help. Thank you.

Offline bus_pass_man

  • Statesman
  • ******
  • Join Date: May 2008
  • Posts: 435
  • Forum Citizenship: +43/-0
Re: Data module relationship type and cardinality
« Reply #5 on: 07 May 2020 12:23:08 pm »
https://www.ibm.com/support/knowledgecenter/SSEP7J_11.1.0/com.ibm.swg.ba.cognos.ug_fm.doc/c_dyn_query_bridge_tables.html


The UI is different but what you need to do is the same.  Like FM, modules have an option to identify a table as bridge table.

Offline vijay857

  • Full Member
  • ***
  • Join Date: May 2020
  • Posts: 5
  • Forum Citizenship: +0/-0
Re: Data module relationship type and cardinality
« Reply #6 on: 07 May 2020 12:46:59 pm »
Thank you. I'll try and update you on the progress. :)