This discussion is archived
13 Replies Latest reply: Sep 2, 2013 8:05 PM by 1012572 RSS

Querying against multiple subject areas

user634297 Newbie
Currently Being Moderated
We would like to write a query against two or more subject areas. Is that possible in OBI EE 10g? Looks like this is a feature available in Business Objects and our users are requesting it. Please suggest how it can be done.
  • 1. Re: Querying against multiple subject areas
    David_T Guru
    Currently Being Moderated
    Unfortunately, no. Unlike in Business Objects where you can easily build a user variable to grab fields from another Universe, you can't do this in OBIEE. In OBIEE, you would have to build bridge tables to link the data or make joins and then push that to the presentation layer of the Subject Area you are using.

    There are some limited work arounds to specific uses like sub queries (filtering based on another request), but nothing like what is available in Business Objects... at least in 10g. Sorry.
  • 2. Re: Querying against multiple subject areas
    DeboRoy Explorer
    Currently Being Moderated
    Hi

    Try this link

    http://nerdsofobiee.wordpress.com/2010/03/05/combining-obiee-answer-requests-in-presentation-layer/

    This may a workaround for the functionality you are looking for

    Regards
    Debo
  • 3. Re: Querying against multiple subject areas
    David_T Guru
    Currently Being Moderated
    Uh, Debo, I mentioned this in my post...
  • 4. Re: Querying against multiple subject areas
    646090 Expert
    Currently Being Moderated
    Hi user634297,

    David T is absolutely correct. If you're on 10g, you're stuck revamping your RPD and expanding your existing subject areas, playing around with advanced filters or tricks with presentation variables. But all of these methods are not the ideal solution.

    The good news is that there is light at the end of the tunnel. I played around with OBIEE 11g and it definitely supports this. I haven't investigated the backend RPD to see what kind of maneuvers are required to pull this off, but at least it is a feature now.

    Good luck!

    -Joe
  • 5. Re: Querying against multiple subject areas
    gerardnico Expert
    Currently Being Moderated
    I disagree with everybody and I hope that I'm right ;-)

    OBIEE is able to read a logical SQL which follows the ansi 92 standard.

    Then you can do what you want such as:
    select Table.Column from SubjectArea1
    UNION ALL
    select Table.Column from SubjectArea2
    The culprit is that the GUI of answer in 10g can't help you to write it
    (as Joe said, may be in 11g with the multiple subject area feature).
    You have to put it manually in the Advanced Tab of an answer.

    Cheers
    Nico
  • 6. Re: Querying against multiple subject areas
    646090 Expert
    Currently Being Moderated
    Hi Nico,

    You are correct, OBIEE 10g will let you use UNION statements in the complex SQL. In fact you can use "Combine with Similar Request" to drive a UNION, UNION ALL and MINUS statement from the Criteria tab without having to go to advanced settings.


    What we were trying to say above was that it doesn't let you JOIN two subject areas together. For example,

    SELECT SUBJECT_AREA1.COLUMN1, SUBJECT_AREA2.COLUMN2
    FROM SUBJECT_AREA1, SUBJECT_AREA2
    WHERE SUBJECT_AREA1.KEY = SUBJECT_AREA2.KEY

    The above just isn't possible.

    One thing you can do that sort of accomplishes it (but it behaves really kludgey) is use the union statement + null/0 place holders + group by statement. This is a pain though. For example:

    1) Your Logical SQL should be something like:
    SELECT SUBJECT_AREA1.COMMON_DIMENSION, SUBJECT_AREA1.MEASURE1, 0 MEASURE2
    FROM SUBJECT_AREA1
    UNION
    SELECT SUBJECT_AREA2.COMMON_DIMENSION, 0 MEASURE1, SUBJECT_AREA2.MEASURE2
    FROM SUBJECT_AREA2
    2) In the criteria tab, create new aggregate columns based on MEASURE1 and MEASURE2 grouped by COMMON_DIMENSION. For example:
    NEW_MEASURE1= SUM(MEASURE1 by COMMON_DIMENSION)
    NEW_MEASURE2=SUM(MEASURE2 by COMMON_DIMENSION)
    ***I think you have to reference the saw name and not the logical name at this point. Hence it may read something like: SUM(saw1 by saw0) and SUM(saw2 by saw0)
    3) Throw the COMMON_DIMENSION, NEW_MEASURE1, NEW_MEASURE2 into a pivot table and you're all set.

    Like I mentioned, it behaves very strangely and is a pain to implement, but it does display the expected results a join would.

    -Joe
  • 7. Re: Querying against multiple subject areas
    gerardnico Expert
    Currently Being Moderated
    Just to add a complex Logical SQL example for a densifiication:
    http://gerardnico.com/wiki/dat/obiee/logical_sql/obiee_sql_densification

    You can see that I use a RIGHT OUTER and also a CARTESIAN join.
    I suppose then that you can also use it between two different subject area.

    Then I suppose that you can for instance perform a FULL OUTER JOIN between the same dimension table
    but in two different subject area to merge the data as the federated query does.

    Cheers
    Nico
  • 8. Re: Querying against multiple subject areas
    user634297 Newbie
    Currently Being Moderated
    Thanks for all your quick responses!
  • 9. Re: Querying against multiple subject areas
    gerardnico Expert
    Currently Being Moderated
    @Joe,

    Two posts in the same time. I just read yours. Juste rewrite it.
    SELECT COLUMN, KEY FROM
    (SELECT TABLE.COLUMN1 COLUMN, TABLE.KEY KEY FROM SUBJECT_AREA1) A,
    (SELECT TABLE.COLUMN2 COLUMN, TABLE.KEY KEY FROM SUBJECT_AREA2) B
    WHERE A.KEY = B.KEY
    I don't promise but it must work.

    Cheers
    Nico
  • 10. Re: Querying against multiple subject areas
    gerardnico Expert
    Currently Being Moderated
    @Joe,

    Without a lot of description yet, here a working example:
    http://gerardnico.com/wiki/dat/obiee/multiple_subject_area

    And you will see that OBIEE creates only one query and the full outer join is then performed by the database.

    Cheers
    Nico
  • 11. Re: Querying against multiple subject areas
    646090 Expert
    Currently Being Moderated
    That's awesome Nico! I thought I tried that exact thing awhile ago and I got an error basically saying I couldn't use the two different subject areas in the same logical query. Perhaps I just had a typo in my logical SQL. I'm going to need to check that out again.

    I wonder what OBIEE does if the query is against two different logical facts. I'm at the airport now but probably early tomorrow I'll hit up the SH RPD and see what happens.

    Thanks for correcting me!

    -Joe
  • 12. Re: Querying against multiple subject areas
    gerardnico Expert
    Currently Being Moderated
    With pleasure.

    Just keep in mind when doing a logical SQL that a table in a regular SQL
    is a complete "select from subject area" statement in a logical sql.

    A subject area is then a sort of BIG denormalized table of a star schema.

    Good day or night and good flight
    even if it's a little bit later, I just wake up ;-)
    Nico
  • 13. Re: Querying against multiple subject areas
    1012572 Newbie
    Currently Being Moderated

    Hi,

     

    Where exactly is this "Combine with Similar Request" option in the criteria tab?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points