are views at database level better than derived tables? aren`t them almost the same thing?

[Former Member]

Do they both have the same advantages and disadvantages? Should I create views (in the database) to include them in my universe instead of derived tables? why or why not?

[former_member182521]

Hi Erika,

It is up to you to go for either View or derived table.

Derived tables InLine views that are created at Universe level aginst the views created at DB level.

The additional benefit of derived tables is to use @prompt syntax in a derived table which enables you to pass parameters to the report dynamically during run time.

You also need to consider the maintainence of business logic whether to keep it in Univese or in Database.

Our general approach would be

• Keep most of the logic in database side. This enables non-BOBJ users can also have access to the data logic.
• When your existing DB doesn't support simple reporting structure ,create a report using derived table for easier BO reporting. Make sure this as a temporary measure only. When you have finalised your universe design you should convert the derived table into an indexed physical table (datawarehouse) or a view (transaction system)

Also refer here.

http://scn.sap.com/thread/1705193

Hope this helps.

Regards,

Mani

[Former Member]

In the case I have a SQL that needs to use @prompts I understand I can use a derived table, but what if I had to materialize this derived table to a physycal view or table? How do I do? If I transform this SQL to a view how do I do with the prompts? If I drop them from the view the view will be fed with all the records, because it won't have the filters the  prompts were providing. Since the view will have all the records with no filters won't it be slower or just as slow as using a derived table with prompts?

[Former Member]

Hi Erika,

I think, a 'materialized' view would be slower in this case due to the reason mentioned by you. From maintainence point-of-view a physical table or a normal database-View would still be a preferred choice.

Thanks,

Prathamesh

[former_member182521]

Erika,

Yes, As view is going to deal with more number of rows, there could be a performance difference.

Utilization of the prompt is the advantage of derived tables against DB views. try consider using materialized view instead of views for improved performance. But again you have maintainence overhead as you need to manage the MV refresh as well.

It is suggested to transform the derived tables with prompts to a Materialized view or a Aggreagate table without prompt and make them as Aggregate aware at universe level to improve performance.

Hope am clear.

Regards,

Mani