Archives of the TeradataForum

Message Posted: Thu, 02 Feb 2006 @ 13:33:32 GMT

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

Hello Dave,

I would insist on the solution performing excellent/very good and being consistent.

I would probably go to the length of denormalizing - if you don't have "excessive number" of order-lines pr. order.

I would choose the PI on the Order/Orderline-table to be something like (ItemId,Date) - that's how it is in our Sales Detail table, and a lot of others.

Partion by Date (using RANGE_N).

As to the granularity of the partitions, I would probably be a little conservative - we normally use 14 days, but sometimes we go for 1 DAY.

The table would be MULTISET.

INSERTs will be very fast because of MULTISET and because of the partition matching the arrival pattern of the data.

Integrity would be enforced by a very well-structured ETL-process.

This PI would:

- Distribute well

- Enable star-joins (rowkey based) and Product Join DPE.

In our case with Sales Detail it also aggregates well (local aggregation) in the Store-dimension.

(I think the ability to aggregate well - local - is an often overlooked requirement to the PI).

To regain some space I would (as we always do on our big tables) use Multi-value-compression - via some tool.

(Lately we've been surprised how much space we could regain by compressing measure-columns, but the DDL needs to be generated by a tool.).

If this table should equi-join on OrderNo, I would put a Single Table Join Index on the table with PI = OrderNo.

Probably with a subset of the columns - you can't use column compression on Join Indexes at the moment.

I would have my ETL-process maintain summary tables.

If I get bad data loaded, I would use the negate-logic I described in another post.

PS: I would not allow some modelling guru/data-administrator to ban the use of MULTISET or the use of denormalization.

Best regards and good luck

Ole Dunweber
Coop Nordic

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