BI - Link Table in Physical Layer when no Linkable Column Exists

Question

Using OBIEE 11g.
In the BI Admin Tool, how can I relate a table with 3 other tables, when there is no column to link?
Here is the scenario:

tbl_car_sales
year  salesperson  sale_price
2012  John Doe     16000
2013  Sally Brown  19000

tbl_boat_sales
year  salesperson  sale_price
2012  Buster Brown 42000
2013  Chuck Brown  58000

tbl_motorcycle_sales
year  salesperson sale_price
2012  Lucy Brown   8700
2013  Sandy Brown  10300

tbl_profit
type  year  profit
car   2012  500
car   2013  600
boat  2012  1200
boat  2013  1400
bike  2012  200
bike  2013  300

What I need is to have the sum of the sale price and the sum of the profit in the same report, grouped by year.
I cannot figure out how to relate the tables in the Physical layer, since the first 3 tables do not have a field to link to.
If tbl_car_sales had a "type" column that was hard-coded to 'car' for each record, that would work but that doesn't exist.

My end result should look like the following:

type year  sale_price  profit
car  2012  16000       500
car  2013  19000       600
boat 2012  42000       1200
boat 2013  58000       1400
bike 2012  8700        200
bike 2013  10300       300

Any help would be appreciated.

Gianni Ceresa · Answer

Ok, so the answer above about using 3 logical tables with fragmentation still apply for the sum of sales part.

And you then add a new logical table source for your profit fixing the content level to say it doesn't exist as a detail per sales person but just at the top "kind of sales" (car, boat etc.) level.

Ray Kelly · Answer

Nothing too complicated.

I only want the sum of sales for the year and the profit for the year.  So, one line per year per vehicle type.

i.e.

type year  sale_price  profit

car  2012  16000       500

car  2013  19000       600

boat 2012  42000       1200

boat 2013  58000       1400

bike 2012  8700        200

bike 2013  10300       300

Gianni Ceresa · Answer

Well, I don't have anything come to my mind about that.

But your example is simple: your fragmentation condition is based on car, boat, bike.

And in your 3 tables you don't seems to have these values, so what you can do is to create a logical column for that and you assign to it a fixed (hardcode) value for each one of the 3 logical tables source. And you use this new logical column as condition.

But wait !!!

I guess we are all wrong and didn't understand clearly your case:

You have 4 tables and not 3 : TBL_CAR_SALES, TBL_BOAT_SALES, TBL_MOTORCYCLE_SALES and TBL_PROFIT

Your problem is actually that the granularity isn't really the same as TBL_*_SALES are at a year - sales person level and your TBL_PROFIT table is at a year - kind of sales level. So what happen if you have 2 sales persons selling cars in 2012? In your final report you want to see the sum of sales prices of both of them next to the single row of profit coming from TBL_PROFIT ?

It's possible as well, just require to be done in a different way ... (it becomes more a content level thing than a union of 3 tables).