How to set determinants, to obtain correct results?

[jmsellner]

I have the following
   Fact A has keys to dimensions A,B,G,H.  I can have multiples of H, for A,B and G.
   Fact B has keys to dimensions A,B,C,D,E,F.  I can have multiples of F for A,B,C,D,E.

In Fact A I created determinants Grouped H - Attribute # of, grouped A, Grouped B and Grouped H.
In Fact B I created determinants Grouped C - Attribut # of, grouped A, grouped B, grouped D, grouped E and grouped F.

I want the queries between these 2 facts to use the A to join them together but it isn't working.


If I ask for the number of from fact A and Fact B by C, it is joining on the group B, which overstates the number of each fact.

If I ask for the number of from fact A and fact B by D, it is stitching values together based on D from Fact B and H from Fact A.

I have tried changing the determines by creating a unique key but that doesn't make a difference.  I have also tried, adding a determinant in dimension A and this didn't matter.


Suggestion on how to change this so it will work.

[MFGF]

Hi,

My take on this is that you shouldn't need determinants on your facts at all.  The determinants should go on each dimension, especially if they join to the facts at different granular levels (but this isn't clear from your post).  Do Fact A and Fact B join to dimensions A and B using the same dimensional keys or using different dimensional keys?  Dimensions D, E, F, G and H will be treated by the query engine as non-conformed dimensions when used, producing a correlated list rather than a contiguous result set, but the query engine is designed to handle this.

From your post, it's not clear whether you are expecting to achieve sane results when reporting off your two facts when not using any conformed dimensions (Dim A or Dim B) in the query.  If this is what you are doing, then things become less obvious, since there will be no clearly defined join path for the query engine to use between the two facts.  In this situation, it will default to using the first alphabetic relationship it can find to link the two facts.  To be more precise, if you write a report containing a measure from Fact A, a measure from Fact B and a description from Dimension C, the query engine knows how to link from Dimension C to Fact B, but from there has an ambiguous join path to Fact A (it could either use the link from Fact B to Dimension A to Fact A, or from Fact B to Dimension B to Fact A).  Which one it uses will depend on the names of the relationships - whichever is first alphabetically will be the default.

If you always want the ambiguous join to be done via Dimension A, make sure that the relationships from Dimension A to Fact A and Dimension A to Fact B are first alphabetically - rename them with aa at the beginning of each of the relationship names.

Once you have tried this, get back to us if you are still getting odd results - we may need to see the generated SQL to get a better understanding of what is going wrong.

Best regards,

MF.

[jmsellner]

Here are my joins
  Dimension A to Fact A on Report #
  Dimension A to Fact B on Report #
  Dimension B to Fact A on approval Date
  Dimension B to Fact B on approval Date

I change the relationship name between Dim A and Fact A and Dim A and Fact B, and this solved half my problem.  Thanks!  So I remove the determinats from Fact A and Fact B and Dim A and B. 

I am still having problems thou using other nonconforming dimensions off of Fact B.  The other nonconforming is creating a stitch query.  For example, if the using nonconforming dim D on Fact B, it stitches doing an rsum(dim d) and a rsum(key from fact a).

Jo Marie

[MFGF]

Hi Jo,

Creation of a stitch query is absolutely correct where multiple facts are included in the query - regardless of whether conformed or non-conformed dimensions are used.  The difference is that the conformed dimensions will appear within a COALESCE() function, whereas the non-conformed dimensions will not be coalesced.  What C8 will do in a situation where two facts and one non-conformed dimension are used in the query is to aggregate the numbers in the first fact based on its links to the dimension, and aggregate the numbers in the second fact overall, since it does not link to the dimension.  This is expected behaviour.

MF.

[jmsellner]

The problem is this stitch query doesn't have a coalesce() function on the conformed dimension.  The conformed diminesion isn't even being pulled into the query.

[MFGF]

Hi,

The conformed dimension will only be used if you bring an item from it into your query.

MF.

[cognos_users]

Hi MFGF,

What if the results generated with the stitch quey are incorrect? Recently I experienced similar issue ; one dim joined to two facts. So when created a report by pulling facts from the Fact Table1, FactTable2 and the dimension in single query, it resulted in COALESCE() and a full outer join with erroneous results. I had to create two separate queries in the report and then join them to avoid the issue. Is there another clean alternative??

Thanks.

[MFGF]

Hi,

One of the fundamental design briefs of the C8 query architecture (formerly ReportNet) was to allow non-technical users to write simple reports spanning multiple facts (possibly at different levels of granularity) via conformed dimensions, delivering correct results. Put simply, what you are describing should work (provided the data has been modelled correctly).  In what way were the results erroneous?  The Coalesce() function on the dimensional value and the full outer join sound correct to me - this is what you expect to find in a stitch query spanning multiple facts.  Were the numbers incorrectly aggregated?  If so, it points (possibly) to incorrect determinants - but without knowing more that's just a guess.

Best regards,

MF.