Anyone had the experience of trying to build a measure whose source columns come from two different fact tables?
I’m trying to define a measure as:
sum(table1.col_a * table2.col_b)
where table1 and table2 are facts on a star schema model in different belonging in their respective contexts, and no direct join is defined between them.
Although the definition parses fine, BusObj complains of incompatible objects, when I build a query with this object.
Save for defining col_a and col_b in the same fact, is there another way of making this happen?
If the tables are in different fact tables, then they are really in different contexts. An individual object cannot not come from 2 tables that are not part of the same context.
The problem is that usually the 2 fact tables were separated via contexts to avoid/resolve chasm/fan traps. Just putting all joins into one context will not solve the issue.
Nii, you don’t mention what database you are using, I was wondering if you could define a funcition , in the database to lookup the value that you want and call this function fronm Business Objects along the lines of
**select statement**
Select Table1.col_a,MyFunction(linkingparameter) from
Table1
**pseudo function definition**
Myfunction(Linkingparameter)
Select col_b
from Table2
where Linkingparameter = Table2.somecolumn
return col_b
If this is a requirement specific to a particular report and if there is a way to link the two fact tables, why not create 2 data providers and link them at the report level and do the calculation at the report level.
In special time. we have to get the measure from the 2 fact table. And if we put the all joins into one context. it will spend too much time to get the resualt!