Fact table design

Hello.

I am working on an educational data warehouse. I have a semi-additive fact table that deals with student grades and averages. It's as such:

School, Acad Year, Student, Course, Term, Week, Week Grade, Term Average

where Week Grade and Term Average are the measures.

The term average is an average calculated based on a specific formula on top of week grade. It's supposed to appear when the user is standing on All weeks member of Week's dimension.

I have an average calculation problem in the term average...say i want to compare the student term averages.
i don't know if its correct to write an mdx formula to get it or to create a separate table for the Term Average containing the granularity by term rather than by week.

Thanks in advance for your advice

Christina

I think your design has some problems. The biggest one is that you are mixing grain levels in the fact table (terms and weeks) and both are dates. My advice is to first focus on creating the fact table at the most detailed grain level, which I think is probably days (complete day). So

FactGrades: SchoolKey, DateKey, StudentKey, CourseKey, Grade.

The date key should have some week attributes so you know to which week the current day is referring to, and the same with the term. Your Date dimension should look something like this:

DimDate: DateKey, FullDate, CalendarYear, CalendarSemester, CalendarMonth, CalendarDayInMonth, TermYear, TermSemester, TermMonth?, TermWeek, TermDayNumber, etc, etc.

From this you could create an aggregate fact table for the term averages or better yet, derive the term average with an MDX expression when you create your Olap Cube in Analysis Services.

|||

Ok let's say i fix the date issue. i still have a concern. i want to precalculate the term average because the way to get it is based on many factors and i am trying to avoid its complexity. i prefer to add it to this fact table or to create a fact table just for it.

but this measure would be considered at a different granularity..is it correct to add it to this fact table or should i create a fact table just for it....i did the second option and want to make sure its correct.

and honestly that's why i separated the week from the term because i assumed that if i put it in a different fact table, i would need to use only the term key, and not the week.

in AS,i am using the scope function in the Calcuations to change the aggregated value of the Grade measure to TermAverage when the currentmember is at the Term level.

That's one of my concerns.

I have another issue resulting from this one... i would like to share it with you in case you could give me some advice.

1-assume i'm standing at class section level, which is a level in the student dimension (school, class, section, student). for a given course i want to compare the average between sections of the same class to see which section is performing better during this week.

2-i want to do the same but for a term instead of week

in case 1, the measure used should be the Grade.
in case 2, it should be the TermAverage.

when i added these measures to the cube, i set the aggregation property to average..however the avg is semi-additive and will only function on time dimension. i therefore created 2 calculated measures, one for the grade and another for the TermAverage as such:

Grade Average = Grade/count of records in the fact table
TermAverage Average = TermAverage/count of records in the other fact table containing only the TermAverage.

and then modified the scope calculation to put these measures in the aggergation instead of the original Grade and TermAverage because the other will give me Sum while standing at Class or Class Section while what i want is the average.

Am i doing right like that by creating both fact tables, doing the measures like that and then modifying the scope to do this? i would like to mention that the aggregation now is taking much more time to give results because of the calculated measures...

Thanks again for your help

Christina

|||

I really think you should try to compute the term average with the information from the daily grades using MDX. How complex can it be? Keep it as simple as possible. If you still want to have it separately, you should create a different fact table for the term average. The only thing is that this fact table should no longer have a dateKey but a TermKey or put it as a "measure" in the fact table so you don't have to maintain a term dimension. This value is the same as the one defined for that term in the Date dimension. This is for the Data Warehouse. E.g.

DimDate.Term: 200509

FactTermAverage: SchoolKey, StudentKey, CourseKey, Term (200509), Average (19.45)

In this way you can link the term with your date dimension in Analysis Services. Just make sure to set the attribute hierarchical relationships properly for the Date dimension, and the scope for each measure (Checking the term average per calendar day, calendar month, day, etc. doesn't make sense so the term average should be displayed as "NA" in these cases).

In regards to your second question, you should have a single cube in AS for a DW (best practice). This means you would have a count measure in the cube for each fact table. However you should try to design a single calculated measure called "Section Average" which is calculated depending on where the user is in the cube:

Case

When user is doing week comparisons
Grade/count of records in the grade fact table
When user is doing term comparisons
Term Average/count of records in the term fact table
Else
"NA"
End

|||

Thank you very much for your insight.

First i never knew i could link the Term to the Weekly dimension like that.. it saved me a lot.

I will also try to do the measure. The problem is that i'm not that good at MDX and i try to avoid using it..

You wondered how complex the MDX computation could be..well to me it's complex..i will explain it to you maybe you could help me again at this one..
in the time dimension i said i have Academic Year,Term and Week.

the term average=avg(Weeks 1-8)*0.60+avg(week 9)*0.40

The acad year average = Term 1*0.25 + Term 2 *0.25 + Term 3(week1-8)*0.20 + Term 3 (week9)*0.30

Thanks much

Christina

|||

hi .. i am also working on university data warehouse but i am only working on two subjects i.e. Student record and Admission ..if some one can help me in designing of the model ..

thanx

Zeeshan Ali