When can Aggregate Join Index (AJI) be accessed?


When can Aggregate Join Index (AJI) be accessed?

Hi Gurus,

Requirement is to establish the efficient ROLAP performance; for that we are looking to create AJI. There are OLAP views created for users and queries will be based on joins between them including the Fact table. Most of our AJI are working except for below scenarios.

Hence, there are few doubts on AJI stuff; these can be limitations of Teradata (perhaps Im unaware) -

  1. Is the Self Table join issue resolved in TD14? Any alternate to resolve it?
  2. Is 'Search condition must be deterministic' means WHERE clause for value access is not allowed in AJI
  3. Some OLAP views are created with CASE statements to introduce new column (which is not present in Base tables);                                                                            Ex:-  Tab_1 (a,b); Tab_2(a,d)      OLAP views on Tab_1 is View_1(select a,b from Tab_1) and for Tab_2 is View_2 ( select a,d,CASE when d=0 then 1 else 0 from Tab_2) . AJI definition :- Create JI JI_test as SELECT a,c,d,sum(b) from Tab_1 T1 inner join Tab_2  T2 on T1.b=T2=b.                                                                             The above snippet may not be clear; but my basic question is when a new column is introduced via CASE  in view then Would AJI be accessed?
  4. Some OLAP views are also having hardcoded values to introduce new column  (which is not present in Base tables); Would AJI be accessed ? (same snippet as above, but imagine hardcoded value instead of CASE)
  5. Any alternate in above scenarios if answer is NO.
  6. To get the access path through AJI, is it necessary that AJI should have all the columns defined/joined in the same way done under OLAP view. I mean, New column (via CASE/Hardcoding) should be part of base tables only and not for VIEW.
Note- Stats collection has been done properly.

I would appreciate your help; because we are not sure who is confused more...us or Optimizer :)