Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

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

2 posters

Go down

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

Post  meb97me 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

Back to top Go down

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

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

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

http://aginity.com

Back to top Go down

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

Post  meb97me 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

Back to top Go down

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

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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