Author Topic: FM Model: Cardinality Question  (Read 274 times)

Offline Cognos91

  • Senior Member
  • ****
  • Join Date: Oct 2007
  • Posts: 57
  • Forum Citizenship: +0/-0
FM Model: Cardinality Question
« on: 24 Jul 2017 10:36:20 am »
I have a question on setting up relationship between two fact tables using a conformed dimension.

The data is such that:
A given record from (Dim 1 + Fact 1) may or may not contain a record in (Dim 2 + Fact 2).
A given record from (Dim 2 + Fact 2) may or may not contain a record in (Dim 1 + Fact 1). This implies a full outer join.
The current FM, DQM model, does not allow for a full outer join.
Dim 2 is the conformed dimension between Fact 1 and Fact 2

Issue:
1) When Fact 1 is joined with Dimension 2 with (1.n to 0.1) cardinality, then data attributes (Attribute 1 or Attribute 2) from Dimension 2 cannot be completely used to filter out data from Fact 1.
2) When Fact 1 is joined with Dimension 1 with (1.1 to 0.1) caridnality, then it basically filters out data from Fact 1 (removes those records from Fact 2 + Dim 2 combo that do not have a relevant records in Fact 1 + Dim 1).

what could be the ideal way of connecting the two tables?
« Last Edit: 24 Jul 2017 10:45:04 am by Cognos91 »

Offline MFGF

  • Never knowingly correct
  • Super Moderator
  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 9,864
  • Forum Citizenship: +597/-9
  • Cognos Software Muppet
Re: FM Model: Cardinality Question
« Reply #1 on: 24 Jul 2017 11:08:05 am »
I have a question on setting up relationship between two fact tables using a conformed dimension.

The data is such that:
A given record from (Dim 1 + Fact 1) may or may not contain a record in (Dim 2 + Fact 2).
A given record from (Dim 2 + Fact 2) may or may not contain a record in (Dim 1 + Fact 1). This implies a full outer join.
The current FM, DQM model, does not allow for a full outer join.
Dim 2 is the conformed dimension between Fact 1 and Fact 2

Issue:
1) When Fact 1 is joined with Dimension 2 with (1.n to 0.1) cardinality, then data attributes (Attribute 1 or Attribute 2) from Dimension 2 cannot be completely used to filter out data from Fact 1.
2) When Fact 1 is joined with Dimension 1 with (1.1 to 0.1) caridnality, then it basically filters out data from Fact 1 (removes those records from Fact 2 + Dim 2 combo that do not have a relevant records in Fact 1 + Dim 1).

what could be the ideal way of connecting the two tables?

Hi,

Your dimensions should link to your facts using 1..1 <--> 1..n cardinalities (the n being at the fact end). If you have a conformed dimension and you bring in an attribute from that, facts from both fact tables will be grouped and summarised based on this attribute. Can you explain the statement "When Fact 1 is joined with Dimension 2 with (1.n to 0.1) cardinality, then data attributes (Attribute 1 or Attribute 2) from Dimension 2 cannot be completely used to filter out data from Fact 1." When you select attributes from Dimension 2, you should see measures from Fact 1 summarised based on these attributes. If you filter your query on attribute values from Dimension 2, you should see the summarised measure values from Fact 1 change - based on them now being the measures relating to the attribute values. Are you seeing something different?

If you have a non-conformed dimension, filtering on values from this dimension will affect only the fact values from the one fact table it links to. It has no relationship with the other fact, therefore how can it make sense to be able to filter the other fact based on values from this dimension? Can you explain?

MF.
Meep!

Offline Cognos91

  • Senior Member
  • ****
  • Join Date: Oct 2007
  • Posts: 57
  • Forum Citizenship: +0/-0
Re: FM Model: Cardinality Question
« Reply #2 on: 24 Aug 2017 01:28:57 pm »
MF,
Apologies for my tardy response. I hope to provide the details soon in my next reply.

 


       
Twittear