If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

FM Model: Cardinality Question

Started by Cognos91, 24 Jul 2017 10:36:20 AM

Previous topic - Next topic

Cognos91

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?

MFGF

Quote from: Cognos91 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?

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!

Cognos91

MF,
Apologies for my tardy response. I hope to provide the details soon in my next reply.