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
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.