Oracle Communities

Oracle Analytics and AI

Child Item

Create Opaque View using multiple tables

Hi,

Can we create an opaque view using multiple tables in query?

I have a query from 2 different tables with a join condition, now I want to create a view and see the results of view in the reports.

Also, how to model in BMM as there is no other table to join as prerequiste before seeing the report.

Appreciate for help.

Find more posts tagged with

Accepted answers

All comments

Gianni Ceresa

Hi,

An opaque view is just what the name says: a view. So you can have any kind of query there inside as long as the result is a set of columns. It's purely SQL (not OBIEE at all, like if you had this view in your database).

To use this in your analysis (report = BI Publisher, analysis = OBIEE Answers) you need to model it like everything else in the BMM layer first and the presentation layer after.

Your view probably contains some attributes columns and some measures, so in the BMM you still model it in that way: a fact table for the measures columns and a dimension table for the attributes columns. Both using the view as source.

Thomas Dodds

NOTE: There is no predicate filtering with an opaque view -- you may have performance issues depending on what you are doing with it.

Robert Angel

Note 2:

I have been 'on a rescue mission' in a number of organisations that have tried to implement OBIEE purely on database views / opaque views.

To use my favourite metaphor this is like buying a screwdriver and using it to hammer nails.

It is sub-optimal.

It has no reusability, one view generally equals one query.

It does not take advantage of ETL / star schema to improve performance and restructure data for ease / speed of report writing.

In short if this is your strategy with OBIEE then please think again, it will be a very great opportunity lost.

[Deleted User]

You're overly alarmist. You're fake news. Analytics is a commodity now. Everything is schema-on-read. No one does data modeling anymore. If the tool can't work with pure code it's a bad tool.

Choose your favourite (ignorant / managerial) retort ;-)

3512682

Hi Gianni,

I appreciate your response on giving good understanding on views.

If you can clarify one more related issue,

I have to create an analysis based on given 2 views which are created using EBS soure tables, So how can I model them in BMM ( 2 views are acting as isolated FACTS) with no relationship with Warehouse tables.

Finally, I need to bring in these seperate views and do union to see the results in report.

Thank you.

Gianni Ceresa

Take the problem piece by piece: you have 1 object containing measures and attributes.

That's what a degenerate dimension is (in dimensional modelling).

OBIEE requires the BMM to be a star, a star means a fact table and at least a dimension. So 1 object will play the 2 roles, you model the measures columns in the fact table setting the aggregation rule, you model the attributes in the dimension table. That's it

3512682

To be more clear on my requirement,

The view 1 has 3 columns XX, YY, ZZ

The view 2 has (3 similar column names with meaningful data as view 1) 3 columns XX, YY, ZZ

So how to model for view 1 and view 2 in BMM to finally do UNION ALL at the analysis to achieve the end result?

Thank you.