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

View previous topic View next topic Go down

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

Post  meb97me on Thu Aug 05, 2010 10:34 am

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

meb97me

Posts : 34
Join date : 2010-07-28

View user profile

Back to top Go down

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

Post  ngalemmo on Thu Aug 05, 2010 12:36 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

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

Post  meb97me on Fri Aug 06, 2010 3:30 am

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


meb97me

Posts : 34
Join date : 2010-07-28

View user profile

Back to top Go down

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

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum