Analyzing SQL fired when pulling data from 2 Datasources

[AnthonyJ]

Hello,

I am pulling data from  two datasources -  One from Oracle and One from Composite

The tables mentioned are just hypothetical.

I have 3 tables in the Oracle datasource, same instance and same schema, as below

PRODUCT
CUSTOMER
FACT

and created a query item for Product Name and Customer Name.

If I pull in the Product Name and Customer Name query items, only 1 SQL is fired. This is all good.

Select
product_name,
customer_name
from
product,
customer,
fact
where
product.product_id=fact.product_id
and customer.customer_id=fact.customer_id

Now, I have one table, Region, pulled from the Composite Datasource.

Region is joined with the Customer table in the model.

When I select Region Name, together with Product Name and Customer Name, the SQL fires 3 queries
ie.

Query 1
----------
Select
  Product_name,
  product_Id,
  customer_id
from
  Product,
  fact
where
  Product.Product_id=fact.Product_id

Query 2
----------
Select
  Customer_name,
  customer_id 
from
  Customer

Query 3
----------
Select
  Region_name,
  Customer_id
from
  Region

I am expecting that the SQL will fire 2 queries and not 3, since the the query items used from Oracle belong to the same instance, same schema.

Expected Queries to fire are

Query 1
----------
Select
product_name,
customer_name,
customer_id
from
account,
customer,
fact
where
product.product_id=fact.product_id
and customer.customer_id=fact.customer_id


Query 2
----------
Select
  Region_name,
  Customer_id
from
  Region


Any ideas ?

I am enclosing the model for reference

[navissar]

Could you add the cardinality of the relationships to your sketch? It looks a lot like Cognos is attempting a stitch query for some reason.