We have sales and credit information in separate fact tables that share
the same dimension tables. I have a measure that I would like to define
that will use measures from both tables. I define this object in
Designer, but when I go to use it in BusinessObjects I receive an
“Incompatible Object” message.
I could bring in all parts of the calculation
and then define a local variable to perform the calculation (letting
BusinessObjects generate multiple SQL statements to get each part of the
calculation). I was curious to see if anyone else out there had tried
to do calculations across multiple fact tables and if you had found any
tricks to do this in Designer (so that we would not have to define a
variable, but make it something that is available in the universe).
We have sales and credit information in separate fact tables …
I have a measure that I would like to define that will use
measures from both tables. I define this object in Designer,
but when I go to use it in BusinessObjects I receive an
“Incompatible Object” message. … I was curious to see
if anyone else out there had tried to do calculations across
multiple fact tables and if you had found any tricks to do this
in Designer …
Unfortunately, Dave, there is NO way currently to do what you discuss in
Business Objects. This is a particular nuisance in WebIntelligence, since
you can’t evey create variables there and get around the issue in that way.
I’ve requested the feature from Business Objects a few times to allow for
the concept of a “formula” which is built in the Universe in Designer, but
ultimately translates to a client/side variable.
This would solve the issue you discuss and also help to solve the issue of
including “percentage” values in the database. Currently if you create a
percentage formula in Designer, the value comes from the DB at the level of
granularity indicated by the query. If you then aggregate that number on
the client/side, the resulting aggregated number is invalid. If designer
instead brought down the base objects of the percentage and treated the
percentage as a client/side formula, the aggregated number would be
accurate.
Unfortunately, I’m unaware of any such plans to include this feature
currently. What this means is that about the only way to resolve this
sitiuation is either using variables in Business Objects, or creating an
summary table in the database containing the information from all of the
tables in question at a common level of granularity and basing the formula
off of that.
Select dim1, me1 from table1
and
Select dim2, me2 from table2
There is nothing in SQL that will let you do a calculation across multiple
statements.
The only way to do this would be to not have BO generate the multiple
statements. Unfortunately, you’d get the right calculation, but the wrong
result set.
We have sales and credit information in separate fact tables that share
the same dimension tables. I have a measure that I would like to define
that will use measures from both tables. I define this object in
Designer, but when I go to use it in BusinessObjects I receive an
“Incompatible Object” message.
I could bring in all parts of the calculation
and then define a local variable to perform the calculation (letting
BusinessObjects generate multiple SQL statements to get each part of the
calculation). I was curious to see if anyone else out there had tried
to do calculations across multiple fact tables and if you had found any
tricks to do this in Designer (so that we would not have to define a
variable, but make it something that is available in the universe).
You CAN do this in designer in certain cases. The answer is: Create a seperate
“SQL access path” in designer, which means, force BusObj to generate a seperate
query.
This can be done in the following way: create two alias tables for fact1 and
fact2, say, f1 and f2 in designer.
Then (depending on how they must be synchronized) EITHER link f1 to f2 (mostly has
to be an outer join) and one of them to your dimension Table (Dim)
OR f1 to Dim and f2 to Dim and fool BusinessObjects by false defining the
cardinalities in a way, that you get a third “Context” which will be used later
for generating a third SQL.
Then define your measure to be whatever and always have in mind, that using this
measure together with the others probably will get more rows than expected
depending on the definition of your 3d measure…
If you need more help, please contact me.
Walter
DI Walter Muellner
Delphi Software GmbH, Vivenotgasse 48, A-1120 Vienna / Austria
Tel: +43-1-8151456-12, Fax: +43-1-8151456-21
e-mail: w.muellner@delphi.at, WEB: http://www.delphi.at
