Model Design - Year of Course

View previous topic View next topic Go down

Model Design - Year of Course

Post  MasterOfTheUniverse on Wed Sep 12, 2012 12:01 pm

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!)


MasterOfTheUniverse

Posts : 2
Join date : 2012-09-12

View user profile

Back to top Go down

Re: Model Design - Year of Course

Post  BoxesAndLines on Thu Sep 13, 2012 9:26 am

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

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Thanks

Post  MasterOfTheUniverse on Fri Sep 14, 2012 5:07 am

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.

MasterOfTheUniverse

Posts : 2
Join date : 2012-09-12

View user profile

Back to top Go down

Re: Model Design - Year of Course

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