BI - Link Table in Physical Layer when no Linkable Column Exists
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.
Answers
-
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.
0 -
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.
0 -
Could you point me in the direction of some documentation or a tutorial?
Thanks.
0 -
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).
0 -
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
0 -
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.
0
