Hi all, i am using BOE XIR2, and i want to create an universe join multiple tables, and some of the tables have large data.
Sample:
Select XX
From Table1
left join Table2 on xx=xx
left join Table3 on xx=xx
and many more…
In the universe, should i import those tables and join them together OR i just create one derived table and put the SQL code inside (means only 1 table will created).
Which approach is better? I want to use the derived table is because i already have the script…
I have try the derived table method, and it run so slow until failed…
If you built a universe that would contain only one serving-all-purposes derived table then every single query created based on the universe would use the whole query of the derived table. So even if you wanted in a report few rows of a single column of 1 small table then a database would need to process the whole query that is behind the derived table. No no, this is not a good way developing the universe.
Your job as a universe designer is to allow the user to be able to retrieve the data as quickly as possible. What you might want to do is create a physical representation of that data - a materialized view. Derived tables fire off their SQL at query refresh time whereas materialized views fire off their SQL to refresh themselves in advance so that you are in effect issuing a select * from mat_view that is simply a read of a table rather than a firing of the complex sql statement. Your other option and one that should be considered for future flexibility is that you design the universe by importing all the tables. This will be better because you can use extra columns going forwards that you did not include in your materialized view.
I’d also check what indexing exists on the tables.
(BOB member since 2008-05-21)
(BOB member since 2003-11-27)
(BOB member since 2007-12-11)