Help settle a design arguement - Fact Column or Dimension row?
2 posters
Page 1 of 1
Help settle a design arguement - Fact Column or Dimension row?
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)
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
cheers
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
Re: Help settle a design arguement - Fact Column or Dimension row?
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.
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.
Re: Help settle a design arguement - Fact Column or Dimension row?
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
Similar topics
» FACT Design Question - How to design FACT Table when computation is required on the degenerate dimension?
» Advice on a single Fact Table Column which could link to more than one different dimension
» Dimension Design with intermediate tables between fact and dimension
» Dimensional table design dilemma, Aditional column or Xref table
» Suggestions on Dimension/Fact design
» Advice on a single Fact Table Column which could link to more than one different dimension
» Dimension Design with intermediate tables between fact and dimension
» Dimensional table design dilemma, Aditional column or Xref table
» Suggestions on Dimension/Fact design
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum