Data module relationship type and cardinality

[vj857]

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.

[bus_pass_man]

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?

[vj857]

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.

[bus_pass_man]

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.

[vj857]

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.

[bus_pass_man]

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.

[vj857]

Thank you. I'll try and update you on the progress. :)