Re: Order details table reference live data

Do not make any confusion. OLAP design rules neither regulate AT ALL physical implementation layer based on relational principles nor it may be placed on the same level. OLAP layer is purely a presentation layer that introduces flexibility into how the results should be presented to decision makers. In NO CASE, schemas implemented should be denormalized. A star schema is nothing but a query output that is PHYSICALLY rearranged so that it gets queried faster. My best advice to you is to separate both layers. Your raw data schema should be purely normalized. Then your star schema implementation(facts tables, dimension tables) would be built at lesser cost and with a higher probability of having some usefulness. In other words, no matter how well designed is your schema and how targetted are your filters, if your raw data schema is not FULLY normalized then it will return FALSE results.

Hope this helps...
Johan Sjöström wrote:
> This is a real pickle. My company's web application offers a lot of
> ways to order products, so there are quite a few Order/OrderDetail
> tables in the db (one for every type of order).
>
> The problem is that the Order Detail tables aren't denormalized,
> instead they reference current live data such as the Product table.
> Alas, when someone changes the name or price of a product that has
> already been ordered, that order is affected.
>
> Needless to say, this means that all orders and history reports can get
> really screwed up.
>
> What are the recommended solutions for this type of situation? A lot of
> tables and a lot of foreign keys are involved. I don't have any OLAP
> experience. Creating history tables for basically every table in the
> system seems like a nightmare also. The db has approx. 1200 stored
> procedures and 180 tables. Minimal-impact solutions are, as always,
> preferred by the management.
>
> Cheers,
> Johan Sjöström
> MSc, MCAD
Received on Tue Sep 05 2006 - 11:21:25 CEST