Help settle a design arguement - Fact Column or Dimension row?

[meb97me]

I would value some options on the following "discussion" me and a colleague are having.

We are designing and building a Data Warehosue capturing learning activities for customer.

Our Events fact table captures various events that happen to the client such as
Starts Training, Starts Module, Completes Module, Completes Training etc
and looks something like this (abbreviated version)

DateSK	EventSK	ClientSK	QualSK
20100101	1	10	1
20100102	1	20	1

Now there is a box on the form they fill in which specifies how long the course last (eg hours) and depending on the length of the course determines how much money is charged so we want to capture this. However the same course can be attended by people for different lengths of time ie the full version or a shorter version.(there are ajust several different possible length options vavailable from a drop down list)

So the question is would this be better captured in the Event Table as and additional field ie DurationSK referencing a new duration Dimension Table or as different versions in the Qualification Dimension Table? and what are the pros or cons of each methodgoing forward once its in the cube?
ie

QualSK	QualRef	Title	Duration
1	TRN1	Training Course 1	4hrs
2	TRN1	Training Course 1	8hrs

cheers

[ngalemmo]

I would go with multiple dimension rows with course length being part of the natural key.

A parallel to this is the way SKUs (stock keeping unit identifiers) are assigned in an inventory system. Every unique version of an item is given its own SKU so it is simple to know what is or isn't in stock. For example, if you have 1 inch 3-ring binders in white, black and red, each color has its own SKU. How a product is packaged is also a determining factor. If you have 2 versions of the same product, one version comes in a box of 3 while the other is in a box of 12, there are 2 different SKUs. There are a host of other reasons, such as different printing on a label, that results in different SKUs.

The point is to clearly identify the product that is being sold. I am suprised your ordering system wasn't set up to do it this way in the first place.

[meb97me]

Thats great ngalemmo thanks very much, that is the way we were swaying towards but its good to get another expert opinion

[Sponsored content]