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

Ray Kelly

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.

Joel

Hi @raykdogs, what you'll need is to create a UNION report. You can either do this in the report itself, in the repository as an opaque view or you can create a UNION view on your database and import this into your Rpd.

As you mentioned, the Year is what is common to all tables. All your tables have the same column set so you should be OK with a UNION report using any one of the methods described above. To create the Type column, you can add this in as an additional column in your DB view/Rpd Opaque view or as a derived colum in UNION report in OBIEE.

Gianni Ceresa

Hi,

These 3 tables must not be linked together. If you link them the result would be to have the sales of cars next to the sales of boats next to the sales for motorcycles.

Your case is more a multiple logical table sources using the "fragmentation" function of OBIEE to say that all the 3 tables contains sales.

This one is the solution from a model point of view.

From a pure report point of view if you don't mind to lose the functionality a UNION report remove you can do as Joel suggested.

Ray Kelly

Could you point me in the direction of some documentation or a tutorial?

Thanks.

Gianni Ceresa

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).

Ray Kelly

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

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.