Skip to Content
avatar image
Former Member

Defining multiple FKs on dimension object breaking index awareness

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:

  • Rearranged the join order, putting Customer in the middle so that any join from it to other dimension tables would only be a single hop. No change.
  • Rearranged the order of keys in the keys list. No change.
  • Published to CMS because in the past I have seen different results between WEBI and the query panel in IDT. No change.
  • Simplified the join between DEPARTMENT_TEST and EMPLOYEE_TEST (removed DEPARTMENT_TEST.CUSTOMER_ID = EMPLOYEE_TEST.CUSTOMER_ID condition). No change.
  • Tried with and without setting up keys on remaining objects, as below. No change.
    Department Name:
    PK - DEPARTMENT_TEST.DEPARTMENT_ID
    FK - EMPLOYEE_TEST.DEPARTMENT_ID
    Employee Name:
    PK - EMPLOYEE_TEST.EMPLOYEE_ID

In summary, there are only 3 things that I've tried that have not broken index awareness:

  • Building the universe in Designer
  • Defining no FKs on the object
  • Defining only one FK on the object

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.

1.png (11.4 kB)
9.png (18.2 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

0 Answers