AJI creation for a single high priority query

Data Modeling
Teradata Employee

AJI creation for a single high priority query

Hi Everyone,

I have the following query for which I have to create an AJI:

select a11.MAT_GRP_GL_ACCT_ID  MAT_GRP_GL_ACCT_ID,

a12.FISCAL_PERIOD_NAME  FISCAL_PERIOD_NAME,

a11.COMPANY_CURRENCY  COMPANY_CURR,

sum(a11.AMOUNT)  WJXBFS1

from RPT_SPEND_DETAIL a11

join D_TIME_FY_V6 a12

 on (a11.POSTING_DATE = a12.DAY_CALENDAR_DATE)

where (a11.COMPANY_ID in ('1057')

 and a12.FISCAL_PERIOD_NAME in ('201612', '201611', '201610', '201609', '201608', '201607', '201606', '201605', '201604', '201603', '201602', '201601')

 and a11.SPEND_IND = 'Y')

group by a11.MAT_GRP_GL_ACCT_ID,

a12.FISCAL_PERIOD_NAME,

a11.COMPANY_CURRENCY

The two views RPT_SPEND_DETAIL and D_TIME_FY_V6 are complex views.

What would be the best way to start coding an AJI tailored specifically for this query?

Thanks,

T

4 REPLIES
Teradata Employee

Re: AJI creation for a single high priority query

I suspect this question might be get a better response in a different sub-forum like DATABASE.

Enthusiast

Re: AJI creation for a single high priority query

For starters, you can't build a JI against view columns, you'll need to start with the base table columns.  start by recreating this exact query with base table columns, including any 'legal' transformations and conditions.  

Once your JI is done, and stats collected, check the query explain and make sure the JI is getting used by the optimizer.  If not, you may have to tweak the JI. 

Teradata Employee

Re: AJI creation for a single high priority query

Thanks for your suggestion Mr VandeBerg.

AJIs have a lot of restrictons and I was hoping for a quick fix :)

Which I've discovered isn't possible.

This view only has one query running against it so we're considering materializing the view in the form of a table.

Enthusiast

Re: AJI creation for a single high priority query

SAP data... Microstrategy... naming convention like that of TAS solution from Teradata... cool!

is a sparse ji also a solution? on spend = Y with a partitioning on fiscal period name.

by the way: if a time dimension view contains a lot of logic, then make that physical first. For us ot had a dramatic positive impact on the DWH.