7 Replies Latest reply on Oct 15, 2018 11:38 AM by 3512682

    Create Opaque View using multiple tables

    3512682

      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.

        • 1. Re: Create Opaque View using multiple tables
          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.

          • 2. Re: Create Opaque View using multiple tables
            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.

            • 3. Re: Create Opaque View using multiple tables
              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.

              • 4. Re: Create Opaque View using multiple tables
                Christian Berg

                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 ;-)

                • 5. Re: Create Opaque View using multiple tables
                  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.

                  • 6. Re: Create Opaque View using multiple tables
                    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

                    • 7. Re: Create Opaque View using multiple tables
                      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.