Index awareness Issue

[Former Member]

Hello Experts

I’m trying to apply index awareness in my universe. I have a
Month dimension table that is connected to my Fact table. In the Month table, I
have a Fiscal Year ID (foreign key).  The
Month ID is composed of the year ID and the month number (201309, 201308, and
201206). The user passes a Fiscal Year Description for instance “Fiscal Year
2013”, which is also in the Month table to display data. Then, the system displays
data for all month in the past fiscal year.

I’m applying the index awareness on the Year ID under the
Month table.  I used a LOV to create a
short list of years with data, which are 2012 and 2013.

What I’m observing ing the following. Without index aware applied,
it returns correct data that corresponds to the Fiscal Year Passed.  But, once index awareness applied, it does not
matter which Fiscal Year selected, it uses 2013 to search, and it returns data corresponding
to both years (2013 and 2012).

Something I noticed however when looking at data in the Moth
table, for each year, there are multiple occurrences because of Month surrogate
key that was created using  year and
month. (201309, 201208 for instance). So the Year_SID are not unique.

My question: is there any ways I can get this index
awareness work with the current structure? What would be the solution?  I’m thinking, maybe the index awareness is
not working because there aren’t unique Year_SIDs, but what I still don’t
understand is why IDT keeps using 2013 in the generated query even when I
passes Fiscal year 2012 ?

[Former Member]

You are right.. the index awareness does not work if there is no unique SID in the table..

Can you post the query that IDT generated from report or universe ... the query should tell the truth..

[Former Member]

Thanks Dugamadhab for this quick reply. However, I still have a question: Why IDT sends a query with 2013 instead of 2012 when I select 2012? This is an incorrect behavior that happening before the query is passed to Oracle? Is there anything I can do on business layer level to ensure that I sends the right number (2012) when I pass "Fiscal Year 2012? Why does it use 2013 not 2012?

Below is the script.  By the way , without index awareness, it works fine, it returns correct results except that it does a full scan which I 'm trying to solve. CAN'T PASTE THE QUERY SORRY!

What it passing in the query is : Month.Year_SID=2013 instead of 2012

[Former Member]

When you allow index awareness the filter is translated to the corresponding  key value in the filter... So most likely you have multiple entry in the table for year 2013 which is getting translated to the key value causing the issue..

The analyze the filter criteria created by the query and you can easily understand what is wrong..

Also when utilizing index awareness you should always need to select from the list of the prompt and not just type the value... As typing values does not work with index awareness..

[Former Member]

Yes, that's correct, I have multiple entries for both 2013 and 2012; and I have a List Of Value I'm selecting from the prompt. And, in fact I dont undersant that I may not work because there is not a unique value for every number passed (corresponding to the string selection). But, I still don't understand is why it keeps using 2013 even when I select 2012.

[Former Member]

If the query where clause has only 2012 which is shown explicitly then there is probably additional issue with your query which could be very difficult to indentify

[Former Member]

Hi Durga/All,

My issue is that Index aware doesn't work even when I have a simple query with 2 objects and a filter on 'Chart of accounts full name' which used 'chart of acct. ID' PK from the same table and 'chart of acct.' FK from a fact table, this is one of the options I checked out but no luck. Below are the tables, joins, and other pieces of useful info. you could use as a reference.

There is No LOV associated to this object too, Please let me know what I should have in Select/Where clause of Key tab, if FK is needed and do I change the datatype to numeric from string for my 'chart of acct. ID'.

Also, both tables show 'chart of acct. ID' as key along with Client ID and Lang ID but None of them worked.

Please give me some inputs on this,

Regards, Kris