Universe design on Database Views Vs. on Tables

[Former Member]

Hi,

I am builidng a universe over oracle database. The warehouse has both tables and views. Some of the reports we will be creating are based on the tables for which views are created. It would be my call to to choose the reporting too (webi or crystal).

I want to know what will be the best approach. Whether creating universe which has database views affect the report performance?(As it will add additional layer. Universe > View > Actual table) If yes, then will it be feasible to go for Crystal Reports instead of webi?

Regards,

Chetashri

[Former Member]

Hi,

Can you please share the steps of this particular process(creating reports from views created from oracle database)

i would be really grateful to you.

[Former Member]

There are two points which you have to think.

1. Either crystal or Webi

a) Generally, we prefer Crystal report when Business users want to have pixel perfect reporting. Enterprise reports such as generla ledger, transactions and so on we prefer Crystal Report.

Cyrstal report has lot of options which helps us to create reporting which is impossible in other tools as possible in Crystal.

b) WebI reports very flexible and easy to create reports. Not as flexible as compared to Cyrstal. But most of the reporting capabilities are available in WebI. Mostly, first I try to build the report in webI. If the tool is not sufficient or capable of creating that report, I go for Crystal.

But WebI reports are preferrable.

2. Peformance with Table or View

There is no big difference now between view and table when view refers only single table because of optimization strategies available in oracle database.

But when a view refers mutlple tables in its definition, there is little edge for view with respect to performance because View optimizations calculated by oracle in advance.

But you could use Tables or views vice versa if there is less data.

Steps for creating reports either from views or tables are same. Instead of view you could use tables and Instead of tables you could use views.

Hope this helps

Regards

Gowtham

[Former Member]

In regard to WebI or Crystal, some people might give you the "textbook" definition that WebI is for adhoc reporting and Crystal is formatted reporting. But that line is beginning to blur and it is no longer true.

The other factors that you need to consider are: Crystal has a higher learning curve as compare to WebI. Crystal reports are normally developed by IT developers while WebI reports are normally developed by business analysts or power users. Also Crystal license fee tends to be higher than WebI in general (which can be a deal-breaker).

Whether you choose Crystal or WebI for report development, you should still go through the semantic layer which is the universe.

Hope this helps.

[Former Member]

Hi,

If the views are giving you data from more than one table then please go for views.

as we know if we have created the derived table and we also have the DB view for that result its always better to replace that derived table by the view as the load will be passed to DB server from BO server.

And also if you have any option to have these views as materialized views than go for it it will improve your report performance.

Cheers,

Ravichnadra K

[Former Member]

Views based on single tables should perform exactly as the single table would. In the "old days" complex views (those built on multiple tables) could present a performance challenge because the optimizer was not able to see through the view to the underlying tables. That should not be the case so much anymore, and certainly not on views that are based on individual tables.

As stated by a prior response, pick the report tool for the job based on the report requirements. The universe structure has nothing to do with one reporting tool being better than another.

[former_member207342]

Hi

generally Views are preferred compared to table.

1. You can control data access

2. If something changes in physical table, you just need to modify view definition. no impact on Universe.

I don't find any performance bottleneck when views are used.

Crystal or Webi.

Your decision should be based on requirement not based on views/tables.

--Kuldeep