I recently "inherited" a DW on MSSQL 2000 and discovered that none of the
stage, fact nor dimension tables have any primary keys or indexes! Databse
is pretty big - over 100 Gb.

Is having no PKs or indexes a smart way to go? (I guess not)

How would adding PKs and indexes affect cube building time? The DW is
updated every night via DTS packages and cubes are rebuilt every night.

Is this considered a good practice for DW updating:
- drop indexes
- insert/update new data
- recreate indexes

All comments and ideas are welcome.
If someone could post some links on some good articles regarding this, I
would be grateful

Sinke,
We have a similar architecture in our implementations. There are not
indexes or constraints on any of the fact or dimensional elements.
This was done for ETL reasons. We have a system in place called
EntelliCache that utilizes caching/data marts so the star schema very
rarely answers a query. If you have no ETL bottlenecks and want to
query the star, then a few indexes would probably not hurt. It is very
common to drop indexes, do a load into a mart and then reindex, but if
you have a very large fact table then this can be very resource
intensive. The indexes would probably help the cube building time but
would definately hurt the ETL time.

Brad

Sinke Pinke wrote: