Hello everyone. Hoping someone has run into this issue before and can offer some assistance. I'm running BI 4.1, SP8. My data source is Oracle 11, using native Oracle drivers. I'm having an issue with index awareness in IDT when setting up multiple foreign keys for a dimension object. I will preface this by stating that I am always defining my filters by selecting from a LOV, and never using a prompt. To illustrate, I have built a very simple example, containing 3 tables.
I have created 3 dimension objects: Customer Name, Department Name, and Employee Name. The SQL for each is a simple reflection of the column name, and the LOV for each is the default. On the Customer Name dimension, I have defined the keys as so:
Now, when I write a query, everything works fine until the query crosses all 3 tables. For example, just pulling in the customer name and filtering by a specific customer, I get the following:
SELECT CUSTOMER_TEST.CUSTOMER_NAME FROM CUSTOMER_TEST WHERE CUSTOMER_TEST.CUSTOMER_ID = 5
I add in the Department object, everything still looks ok, and I get the following:
SELECT CUSTOMER_TEST.CUSTOMER_NAME, DEPARTMENT_TEST.DEPARTMENT_NAME FROM CUSTOMER_TEST INNER JOIN DEPARTMENT_TEST ON (DEPARTMENT_TEST.CUSTOMER_ID=CUSTOMER_TEST.CUSTOMER_ID) WHERE DEPARTMENT_TEST.CUSTOMER_ID = 5
And if I remove the CUSTOMER_NAME from the results objects, it looks even better:
SELECT DEPARTMENT_TEST.DEPARTMENT_NAME FROM DEPARTMENT_TEST WHERE DEPARTMENT_TEST.CUSTOMER_ID = 5
If I substitute the Employee object for the department object, everything still looks ok:
SELECT EMPLOYEE_TEST.EMPLOYEE_NAME FROM EMPLOYEE_TEST WHERE EMPLOYEE_TEST.CUSTOMER_ID = 5
So far, so good. However, if I write a query that tries to bring in both of them, index awareness completely breaks, and I get a query like so:
SELECT DEPARTMENT_TEST.DEPARTMENT_NAME, EMPLOYEE_TEST.EMPLOYEE_NAME FROM CUSTOMER_TEST INNER JOIN DEPARTMENT_TEST ON (DEPARTMENT_TEST.CUSTOMER_ID=CUSTOMER_TEST.CUSTOMER_ID) INNER JOIN EMPLOYEE_TEST ON (EMPLOYEE_TEST.CUSTOMER_ID = DEPARTMENT_TEST.CUSTOMER_ID AND EMPLOYEE_TEST.DEPARTMENT_ID = DEPARTMENT_TEST.DEPARTMENT_ID) WHERE CUSTOMER_TEST.CUSTOMER_NAME = 'MyCustomer5'
Notice that not only is it requiring that I go all the way back to the CUSTOMER table, even though I'm not bringing in the Customer Name object (which I wouldn't mind, if that was all it was doing), but it's now using the actual CUSTOMER_NAME field in the filter, as opposed to the PK. If I remove the FKs from the CUSTOMER_NAME object, the same query gets written better:
SELECT DEPARTMENT_TEST.DEPARTMENT_NAME, EMPLOYEE_TEST.EMPLOYEE_NAME FROM CUSTOMER_TEST INNER JOIN DEPARTMENT_TEST ON (DEPARTMENT_TEST.CUSTOMER_ID=CUSTOMER_TEST.CUSTOMER_ID) INNER JOIN EMPLOYEE_TEST ON (EMPLOYEE_TEST.CUSTOMER_ID = DEPARTMENT_TEST.CUSTOMER_ID AND EMPLOYEE_TEST.DEPARTMENT_ID = DEPARTMENT_TEST.DEPARTMENT_ID) WHERE CUSTOMER_TEST.CUSTOMER_ID = 5
But then we lose the optimization that the FKs are supposed to provide.
As a test, I recreated my universe in exactly the same way, using Designer as opposed to IDT, and in Designer, index awareness works correctly. Additionally, I tried deleting one of the FKs on the Customer object, and now it seems to be working. It doesn't matter which one I take out, but just having the one allows it to properly use the key in the query.
Other things I've tried:
In summary, there are only 3 things that I've tried that have not broken index awareness:
This is obviously a simple example; our own data model takes much longer paths than this. The fact that this works as expected in Designer, but not in IDT, indicates to me that this is a bug, unless these have to be set up differently in IDT. Any help would be appreciated.