Error using data from Different fact tables

[MAJLI]

hi,
can any one tell me how to solve this problem

the problem is : how can I take data from 2 different Query subjects (created by fact tables).
for example :
Contract table is related with Contract Balance ,
Contract Table is related with Contract interest link ,
Contract interest link is related with Contract Interest
If we try to do a calculation field from 2 different Fact table that are not related for example :
Contract Balance +Contract interest we take the message below :
The For-clause in expression:’ total or count’ is illogical because contain multiple facts.

thx

 

[KingCrab]

A calculation where? In a tab set? If so you need to match the co,umn names from both result sets and set one of them to null so you can union it

 

[KingCrab]

You already posted something very similar just a couple messages down from here.. I guess you didnt like those answers?

 

[MAJLI]

for example in the same list :from Contract i take 'Contranct_id' from Contract Balance i take 'Amount' for this conctrat_id ,and from Contract Interest i take the interest rate for the same date with the condition that the Unid from Contract.unid= Contract Balance.Contract_unid
and Contract.unid=Contract Interest.Contract_unid
when i run the report the output is ok .
for every contract_id i take the amount and the rate for a specific date.
then i create a calculation field 'test'=interest rate*Amount for any contract_Id.
in this case for every contract_id i take the 'Test' result.
but when i try to do total to the Test calculated field from Aggregate functions i take the message :The For-clause in expression:’ total or count’ is illogical because contain multiple facts.

thx

 

[Vivarto]

OK, What you have here is a product feature - not a bug!

For some obscure, but probably quite logical reason, performing a calculation involving facts from two different fact tables joined through conformed dimensions will not work. I came across the same problem when getting a volume from one fact table and a price from the other and tried multiplying one by the other.

I cannot comment you your particular problem but we came to the conclusion that in our case the thinking behind the FM model was flawed. We thought we had two fact table but we actually had one, the volumes. The price table looked like a fact table but we came to the conclusion that it was actually a lookup table with a lot of dimension keys. This caused quite a bit of controversy within the team as the purists wanted just facts and dimensions whereas the pragmatists were prepared to consider lookups as a valid alternative. I had a foot in both camps, my heart is purist but my head is pragmatist.

Treating the prices as a 'lookup' and not a fact allowed us to join it directly to the volumes fact and so perform the calculations. I think we wriggled around the problem but I can imagine many situations were there really are two fact tables and the tool won't allow the calculations. I think this has something to do with the full outer joins and coalesce statements generated when joining two facts through conformed dimensions though it must be subtle as I would have thought that putting all measures in if-null-then-zero constructs would avoid the problem.

Bottom line - need to go back and review the model knowing the limitations of the tool