Complex measure

Hi,

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?

Thanks in advance.

-Nii Saka.


niisaka :us: (BOB member since 2004-05-28)

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.


Steve Krandel :us: (BOB member since 2002-06-25)

What about the new derived function coming in 6.5? Will that help or hw will that work?

Thanks


Charles Killam :us: (BOB member since 2003-04-24)

The easy way to solve it:
Create a new context involve then link of the two fact table!


fatball :cn: (BOB member since 2004-06-27)

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.


Andreas :de: (BOB member since 2002-06-20)

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

Paul Shovlar :uk: (BOB member since 2002-09-05)

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.


Ashokkumar (BOB member since 2004-05-20)

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!


fatball :cn: (BOB member since 2004-06-27)