All Forums Data Modeling
mb255021 5 posts Joined 06/14
04 Aug 2016
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

jkurdsjuk 6 posts Joined 01/14
04 Aug 2016

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

Jake Kurdsjuk

VandeBergB 182 posts Joined 09/06
04 Aug 2016

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. 

Some drink from the fountain of knowledge, others just gargle.

mb255021 5 posts Joined 06/14
09 Aug 2016

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.

Johannes Vink 28 posts Joined 08/14
09 Aug 2016

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.

You must sign in to leave a comment.