PDA

View Full Version : Problems with multi-table calculations



Marco Mantini
01-30-2009, 11:58 AM
Hi

i've two physical table:
a (id, name, price)
b (id, cost)

i'm creating the relate business view (with a.id=b.id relation ship) and a new business column : price*cost such as calculated formula of table a.
But when i'm tring to execute a query (via query browser) i'm obtain only
____________________________________
SELECT DISTINCT
A.price + B.cost AS COL0
FROM
A A
____________________________________

instead of
____________________________________
SELECT DISTINCT
A.price + B.cost AS COL0
FROM
A A, B B
____________________________________
I'm read http://wiki.pentaho.com/display/ServerDoc2x/02.+Pentaho+Metadata+Formulas but seems that multi-table calculations doesn't work....
Any idea?

---Marco

MattCasters
01-30-2009, 07:31 PM
What are the aggregation settings etc of those 2 business columns?

Marco Mantini
01-31-2009, 01:48 PM
Hi Matt
thanks for your reply

At this moment there aren't any aggregation or another setting on this column....but i've a question: wiki document says "...add a business column...." but i think that business column only come from physical column....I'm very very confused!

--Marco

MattCasters
02-02-2009, 10:00 AM
Example of a physical column in a physical table:

Table: D_DATE
Column: YEAR

Example of business columns derived from this physical column:

Invoice year
Turnover year
Delivery year
etc.Regardless of this, if you want to calculate with a column, you need to classify it as a fact and set the appropriate aggregation method. (SUM for example)

mario.abbati
02-02-2009, 12:41 PM
Hi Marco!
in my opinion your question can be split in two different problems:
1) how to derive two or more business columns from the same physical column?
It seems that the only way is to duplicate the business column with the + function and then change its name according to your requirements, but I'm not sure this is meaningful because the new business columns will point to the same physical column, i.e. to the same data.
2) how to define multitable formulas, i.e. formulas that involve business columns belonging to different business tables?
I created a new business column as in 1), for this new column I defined a product * formula between two business columns that belong to different business tables with a relationship between them. I included the new business column in a business view, but when I attempted to do a query in order to show the data the query did not work, i.e. the * product was not transformed into a join between the related physical tables.
It seems that the only way is to define a new fact in your physical layer (with the same product) and then reuse it as a business column in your business model, but I'm not sure of that.
Best regards
Mario Abbati

Marco Mantini
02-05-2009, 07:12 AM
I resolved with metadata engine 2.0XXX (despite wiki says about version post april 2008...)
:D