When and what situation we prefer derived table

[Former Member]

Hi All,

I have a doubt. I know Derived table is like view in universe (universe design tool or information design tool) and we create it by using SELECT statement.

But when we prefer Derived table instead of normal tables or aggregate tables.

Can any one plz explain this. If possible explain any situation where we prefer derived tables instead normal .

Thanks,

Poorna

[Former Member · Accepted solution]

Derived tables are never a preference. They are a last resort when you have no access to the database. You'd always rather have access to aggregate tables, tables, materialised view, views then derived tables, in that order.

Why? One word - performance. The database administrator will not be aware of derived tables and will therefore have no obligation to tune them.

Another consideration is a database change. A change to a dependent table can also be overlooked in impact analysis by a DBA, who will know if a view or materialised view is impacted by a table change. They are non-standard bits of code that are there as a last resort, typically when you get a pre-canned universe from a third party or have zero access to the database to put a more robust solution in place. Use them sparingly and ideally aim to have none in your universe.

[former_member182521]

Derived tables are inline views,do not contain any data and everything is calculated on the fly during query execution.The major benefit is that we can make use of @prompt syntax in a derived table.

Derived tables should be created only when you want to have a small dataset and it should be converted to either to a database table or a materialized view as dataset grows.