I have the following query for which I have to create an AJI:
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?
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.
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.
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.