Avoid subqueries and temp tables in filters

[MSIsam]

I have a report that runs every week using a schedule and runs for last week data only. An exapmle of the SQL is:

SELECT 
  WEEK_DIM.WEEK_DESC, 
  FACT.SALES
FROM 
  FACT,
  WEEK_DIM
WHERE 
  FACT.WEEK = WEEK_DIM.WEEK
AND WEEK.LAST_WEEK_IND = 1

If I can specify the specific week in the where clause, the performance is improved. The desired SQL would be:

CODE
SELECT 
  WEEK_DIM.WEEK_DESC, 
  FACT.SALES
FROM 
  FACT,
  WEEK_DIM
WHERE 
  FACT.WEEK = WEEK_DIM.WEEK
  [b]AND FACT.WEEK = 200408[/b]

I have tried to create a prompt with a relationship filter so that the list of values is a single week (last week) but there is no way that I know of to have that value automatically defaulted. If I do not select, the scheduled report will fail.

Is there a way to do this? Any help would be greatly appreciated.

 

[entaroadun]

Try creating a relationship set filter that translates the last week indicator to week. This should put the last week indicator filter into a subquery.

Your RDBMS optimizer may be too smart for that though...

 

[MSIsam]

Thanks Entaroadun,
the use of a subquery (relationship filter) does not give the same performance as explicitly stating the week key. Looking at the explain plan for the SQL (Teradata V2R4), the optimizer does an all rows scan of the entire fact table. If the "AND FACT.WEEK = 200408" statement is used instead of a subquery, the filter is applied before the all rows scan.
One solution that I cannot get to work is to have the filter use the entire list of values generated by a prompt. I tried this by creating a prompt with the relationship filter (returns week key for current week as list of values). This will work for queries run by the user but I cannot figure out a way to get the scheduling part to work. Seems like this could be a good feature to add for an new release.

 

[nlim]

MSIsam, I'm not sure how much performance improvement you are can get given the fact data seems like it is already in a weekly format. However I do have a suggestion which might help.

If you want the SQL generated to explicitly insert a "value", the only value it can insert is a date. So for example if you can code your "week" attribute as a date type, then you can use the dynamic date feature. For discussion purposes let's suppose you did that and the week is represented by the first sunday in that week.

Then you can create an attribute filter, pick ID and then use the dynamic date filter. You'll be able to make the SQL generate exactly the date you need. Scheduling works well with this too.

So in your case, you can add a "date_ending" form to your week attribute by adding a date column to your week lookup table. In your dynamic filter, you then choose week@date_Ending, and use the dynamic date as well.

Since this requires the where clause to be on the lookup table, i'm not sure how much your performance will improve.

Let us know which you decide to do, and the outcome.

 

[entaroadun]

Three things:

1. There's a VLDB setting that will take the subquery and move it into a temp table. Maybe the temp table will make Teradata use the index.

2. You can try creating a custom filter with ApplyComparison that takes the system date on the Teradata box, changing it into Week ID format, and filter on that.

3. Is there any way to put an ApplyComparison or ApplySimple around a MSTR dynamic date? Creating an attribute form that converts Week ID into date format won't work because the expression will be wrapped around the column, not the dynamic date. Wrapping the expression around the column will automatically cause the DBMS to not use any indexes, unless you have function-based indexes on the column.

 

[nlim]

the better solution is to add a "week ending" column in the week lookup. This column should be a datetype column. That way you can apply the dynamic date clause to this column.

I don't think you want to applysimple on the system date. The issue is that what is required is a = 200408. Typically only exact values trigger use of indices...

 

[MSIsam]

Thanks Guys for your input,

I have tried to get the week key from a derived table but the performance is still the same.
As for the system date strategy...Since we have a week key in our fact table, we are still stuck with trying to create this key from a system date table. To make things worse, our fiscal calendar does not have any set business rule to define start and end periods. It has been predefined in the dimension tables.
Our current direction is leaning towards keeping the reports as user defined and having the prompt provide the explicit week key. Running these reports in batch is not worth the added CPU usage on our system.

Thanks for all of your input. Eventhough we have not come to a resolution, this information has been valuable and it's much appreciated.