What is the best approach of these 2 methods in BV design?
We have 100 reports(for ex), each report has got 6 to 8 joins of its own.
And many tables are used accross the reports.
For few reports b/w the tables there is just equi join.
And for other reports the same tables with left/right outer joins are used.
In these scenarios, when i am designing a Business View manager 'Data Foundation' which of the following approaches would be better and efficient?
Option 1: Insert and Create joins b/w tables as per the requirement of each report. And there by acheive each report with respective BE and BV.
By using this approach, I might end up having same table for 5 to 6 times in the data foundation.
Option 2: To create a database layer in the data foundation as per the joins in the main database and report requirements. And then use this same set of tables for all the reports.
By this approach, each table is inserted only once in data foundation but i am afraid that this might lead ro wrong results because: For example, there are 3 tables, customer, customer_contact and order.
In various reports these tables are linked in various combinations (only 1st 2 tables, only 2nd 2 tables, only 1st and 2nd tables, all the 3 tables).
Please suggest, which approach would be better and hassle free...