Design for periodic data cuts

Question

I've been building an application that has a 6-monthly update schedule, i.e. a cut of the data for H1 & H2 of each year. The previous version of this application implemented this by making a copy of an MS Access database and starting again from scratch. Obviously this leads to multiple copies of a database lying around a network, with all the attendant problems you'd expect.

The new design includes a Dataset table, which holds a list of data cuts (e.g. 2014 H1, 2014 H2... etc.).

What is the best way to implement this into the larger design? I can think of two ways to do it:

1 - Add a 'Dataset' field to all fact table records, i.e. prices, volumes etc. are associated with a particular reporting period.

2 - Add a new table acting as a junction between dimension tables and the data cut, i.e. each asset (e.g. product, stock item etc.) in the database is assigned a new Id for each cut.

I can see benefits and drawbacks to each approach: then first leads to a more complex key in many tables, and the second leads to more tables but potentially greater efficiency.

Is there a standard method/best practice of implementing this, or is there a better approach entirely that I'm unaware of?

Answer 1

Your question implies that you're using a Kimball Star Schema approach. Option 1 implies a star schema while option 2 implies a more snowflaked design. Kimball argues against snowflaking your design.

Therefore, go with option one. It will be easier for your report writers long term and newer in memory database technologies will take advantage of it. While you're implementing option one, switch to a surrogate key instead of the multi-field key you're concerned about creating.