Archives of the TeradataForum

Message Posted: Mon, 29 Jun 2009 @ 19:50:34 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Dave - Thanks for your reply and leads. Our present solution is close to, what you proposed as #2 (dividing dimension into slow and fast changing tables) and #4, (same PI for fact and dimension tables); and seems to be performing well. Ironically, what we call as a fact table is just a 'one dimension' fact table; where the fact is a monthly snapshot of several metrics of the dimension. The slowly changing attributes end up in the dimension table; and the required monthly metrics in the fact table. All queries requires some descriptive attributes from the dimension at the time of the fact snapshot, hence must always use the range on the dimension to select the applicable dimension row (this rules out #1 and #3 options). Our present implementation will have the fact with a PI of DimKey and MonthId and is partitioned on MonthID; and the dimension by DimKey. Our queries are predefined reports and are driven by a specific MonthID (with no other dimension filters; like everything for month X).

I see two execution plan options - Plan 1) a full table scan of the fact for the MonthID, then joining with the dimension table on DimKeys satisfying the date range OR Plan 2) a full table scan of the dimension table selecting rows satisfying the range criteria for the MonthID ; then joining to the fact on DimKey and MonthId. I cannot see a way around the full table scan in the first step.

Thanks.

	<Prev	Next>		<<First	<Prev	Next>	Last>>