Opaque versus DB View

Robert Angel

Hi,

For a dimension if there is a choice between a DB view, imported as a physical table source, or an opaque view, is there any difference in performance if the underlying SQL is identical?

I know neither is desirable!

My version is 12.2.1 if it makes any difference.

thanks,

Robert.

Thomas Dodds

Opaque view selects ALL data unfiltered then OBIS does filtering ... DB View/Mat View can be predicate filtered on the database

Probably not noticeable on a small volume - in large databases it's a no-no ... so I always do the DB way regardless of the volume.

Christian Berg-0racle

Hi Robert,

Both are code and if you're using native connectivity (OCI) then there shouldn't really be any difference.

If you really HAVE to go down that route you can always ask: In terms of lifecycle, deployment etc where is it better we put the code? RPD where we can change it ourselves whenever we want? Or with the DBAs?

Joel

Not much difference if any at all as both are pushed down to underlying data source by BI Server. The benefits are probably that you have more control with code in the rpd as opposed to code in the DB.

Thomas Dodds

I assume you are talking Opaque View which is deployed to database?  when you speak of control ... I'm curious.

Christian Berg-0racle

No I mean literally writing an opaque view inside the RPD and keeping it there - not deploying it. Kind of a hail mary in terms of "my DBAs don't let me do stuff".

Robert Angel

Many thanks Christian, this was my suspicion!