Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Create Opaque View using multiple tables

Received Response
11
Views
7
Comments
3512682
3512682 Rank 3 - Community Apprentice
edited Aug 13, 2024 5:38PM in Oracle Analytics Cloud and Server

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.

Answers

  • Gianni Ceresa
    Gianni Ceresa Mod

    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
    Thomas Dodds Rank 8 - Analytics Strategist

    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
    Robert Angel Rank 8 - Analytics Strategist

    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]
    [Deleted User] Rank 2 - Community Beginner

    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
    3512682 Rank 3 - Community Apprentice

    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
    Gianni Ceresa Mod

    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
    3512682 Rank 3 - Community Apprentice

    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.