Querying across two Subject Areas in Oracle Business Intelligence Suite (OBIEE)

Question

It is with a heavy heart that I come to ask for help on OBI queries. I have two areas that I want to join in an analysis using OBI 11g. Here's the schema:

Distribution
|-- Purchase History
|    |-- Billing Info
|    |-- Order Number
|    |-- User Email
|    |-- Shipping Info
|    |-- ...
|-- Branding
|    |-- ... etc

OrderSource
|-- Orders
|    |-- Order ID
|    |-- Order Type
|    |-- ...
|-- Customers
|    |-- Customer ID
|    |-- Email 1
|    |-- Email 2
|    |-- ...

For now, I'd like to be able to JOIN ON Distribution.Purchase History.Order Number = OrderSource.Orders.Order ID, or similar with User Email and Email 1. I've found a place (New -> Analysis -> OrderSource -> Advanced tab -> New Analysis) where I can input actual SQL and have tried a few queries (based off of generated queries and also from scratch):

SELECT
   0 s_0,
   "OrderSource"."Customer"."Customer ID" s_1,
   "OrderSource"."Customer"."Email 1" s_2
FROM "OrderSource"

and

SELECT
   "Distribution"."Purchase History"."User Email"
FROM "Distribution"

for instance. But as soon as I try to join any of these together (with explicit JOIN or WHERE) I get the same generic SQL identifier error. Is there a standard way to work with multiple sources in OBI? Should I be approaching this differently? Any and all help appreciated.

Answer 1

Let's do some Necromancy and raise a zombie.

OBIEE works with "model" not with "queries". You would model this by either joining on these attributes in the physical model - if you want to go down the restrictive, database-like way. Or you would model this logically in the business model by using said attributes as conformed dimensions which tie together your entities (and more specifically your facts) and let OBI do the data set stitch join in-memory. So zero physical "join" at all.