Model Design - Year of Course
2 posters
Page 1 of 1
Model Design - Year of Course
Hi Kimball Forum Members,
This is my first post so I am quite excited! I would like to say thanks in advance for all the help that you have given me indirectly in other posts on this forum.
I actually have three questions, two of the questions are of a similar nature as you can see below.
The first is; I have a course dimension with a course length attribute and several fact tables that capture the 'year of course' for example course registration and course outcome. If I want to query across the two fact tables using conformed dimensions by 'year of course' then I need to have 'year of course' in a dimension but this seems like a very trivial dimension, maybe with only a couple of attributes. Is this the right design decision or should the year of course live elsewhere such as in a junk dimension as I don't see where else it would go.
My second question is; I have a student dimension with an attribute called 'location' and is a small set of codes that indication if a student is local, out of town, out of state etc. These 'location' codes are also used in in target and forecast tables but only to a course level. Again if I want to query across these table using a conformed dimension where would the 'course location' code live? Would I just use a subset of the student dimension e.g. select location from student group by location?
The third and final question is; is it acceptable to pivot (cross tab) a fact table with a dimension attribute for ease of summing in the fact table. For example a dimension has the following attribute values a,b and c and in my fact table these are instantiated as fact attributes in their own right e.g. bit columns a,b and c in the fact table?
Thanks a lot for your help,
MasterOfTheUniverse (My BO universe that is!)
This is my first post so I am quite excited! I would like to say thanks in advance for all the help that you have given me indirectly in other posts on this forum.
I actually have three questions, two of the questions are of a similar nature as you can see below.
The first is; I have a course dimension with a course length attribute and several fact tables that capture the 'year of course' for example course registration and course outcome. If I want to query across the two fact tables using conformed dimensions by 'year of course' then I need to have 'year of course' in a dimension but this seems like a very trivial dimension, maybe with only a couple of attributes. Is this the right design decision or should the year of course live elsewhere such as in a junk dimension as I don't see where else it would go.
My second question is; I have a student dimension with an attribute called 'location' and is a small set of codes that indication if a student is local, out of town, out of state etc. These 'location' codes are also used in in target and forecast tables but only to a course level. Again if I want to query across these table using a conformed dimension where would the 'course location' code live? Would I just use a subset of the student dimension e.g. select location from student group by location?
The third and final question is; is it acceptable to pivot (cross tab) a fact table with a dimension attribute for ease of summing in the fact table. For example a dimension has the following attribute values a,b and c and in my fact table these are instantiated as fact attributes in their own right e.g. bit columns a,b and c in the fact table?
Thanks a lot for your help,
MasterOfTheUniverse (My BO universe that is!)
MasterOfTheUniverse- Posts : 2
Join date : 2012-09-12
Re: Model Design - Year of Course
1. Most folks just use the date dimension which contains a year column to drill across on any sort of calendar requirement.
2. Create a location dimension independent of the student dimension to track geography.
3. Not sure I quite understand what is going on here. I don't instantiate dimension attributes on the fact table. That's what the dimension foreign key is for, joining to the dimension to get the dimension attributes. Copying dimension attributes to the fact table is not a best practice.
2. Create a location dimension independent of the student dimension to track geography.
3. Not sure I quite understand what is going on here. I don't instantiate dimension attributes on the fact table. That's what the dimension foreign key is for, joining to the dimension to get the dimension attributes. Copying dimension attributes to the fact table is not a best practice.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Thanks
Thanks for your help.
In response to your suggestions.
1.The year of course is not really a calendar date but year of the course e.g. 1 |First Year, 2|Second Year, 3 | Third Year ... I don't think I could use the calendar dimension for this purpose.
2.OK, I will explore this idea further.
3.I have a single attribute that can take on about 20 distinct values. This have been group by the business into 6 distinct codes again that they want to count so for ease of use I have added these as bit columns to the fact table in the data mart.
Thanks again,
Martin.
In response to your suggestions.
1.The year of course is not really a calendar date but year of the course e.g. 1 |First Year, 2|Second Year, 3 | Third Year ... I don't think I could use the calendar dimension for this purpose.
2.OK, I will explore this idea further.
3.I have a single attribute that can take on about 20 distinct values. This have been group by the business into 6 distinct codes again that they want to count so for ease of use I have added these as bit columns to the fact table in the data mart.
Thanks again,
Martin.
MasterOfTheUniverse- Posts : 2
Join date : 2012-09-12
Similar topics
» SSAS 2008R2 - Creating "Year over Year Growth %" or "Year To Date"
» Reasons to use a 3NF design over a Dimensional Model design for an EDW - Discussion
» Sales list year over year performance
» dimension model design:
» Model Design With Several 0 to Many Joins?
» Reasons to use a 3NF design over a Dimensional Model design for an EDW - Discussion
» Sales list year over year performance
» dimension model design:
» Model Design With Several 0 to Many Joins?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|