I’m looking into using Index Awareness in order to optimize one of my universes.
The documentation is a little vague as to what objects it shoud/could be placed on.
Should I ideally go through every object from a table and define the primary key from that table in it?
And on the object representing the primary key itself - is there any point in defining index awareness?
as for foreign keys - do I set these on the foreign key objects themselves, or on other objects? in what table?
to simplify the answer, let’s take the example in the Designer guide (on page 325):
we have a customer table with the fields last_name and city_id, and the table city, with the fields city and city_id.
Assume we have objects representing each of these fields.
On which would I put index awareness, and what kind?
I covered index awareness to some depth a few years ago at a conference presentation. It was a double-length presentation, and I covered Index Awareness, Shortcut joins, and JOIN_BY_SQL in part one.
It was “Tales of a Universe Ninja, Part One” and you can find it (along with part two) on my presentations page on my blog.
To answer your question in brief: you can use index awareness on anything that can truly be identified by a key. In a dimension table in a star schema, only the lowest level dimension qualifies. For example, if you have a time dimension then you could use index awareness on the date, but not the week, month, quarter, or year.
So if I understand correctly, based on your explanation and presentation, I would not use it at all on the fact table, but only on the dimention tables.
(BOB member since 2004-05-05)
(BOB member since 2002-06-06)