Need help designing this star for education sector/university

[inventivethinking]

I am new to Dimension Modeling and I am working on doing a dimension model for a university. The current business process that I have picked up is actually sales/revenue. I have been reading different chapters of different books and although I think I have a good understanding of facts and dimensions I am having some tough time fitting the sales process on to the paper.

Ideally the sales process in the school is similar to other businesses where students are customers and the product is the "courses" they take. However in certain situation there are different product types and I don't know how to fit the product type. For example student pays an Application fee, late fee or transcript request fee which is not associated with any course. How do I fit these different type of revenue streams in my star?

What I have done so far is like this

Code:

Sales_FACT
====
Date_Key_FK
Product_Key_FK
Campus_Key_FK
Student_Key_FK
ChargeCredit_SKU
Amount


Product_Key
------
Product_Key_PK
SectionID
AcademicYear
AcademicTerm
AcademicSession
CourseCode
CourseName
ProductType????

Now for certain type of products (e.g. a transcript request fee) - I do not have the coursename,code, year term,session --  I am struggling how this will work.


Anyone has any input on this? or any helpful material/schema examples will appreciate them


Thanks,

[rkraj]

If student pays an Application fee, late fee or transcript request fee then it is for a course rt?
Meaning
ONE Course ID -> Many FEE types
Please correct me if i misunderstood?

[inventivethinking]

If  student pays an Application fee, late fee or transcript request fee then it is for a course rt?
Meaning
ONE Course ID -> Many FEE types
Please correct me if i misunderstood?

Actually no, there are many types of fee which are not associated with any courses. Think of it similar to a company which provides products and services. So the product attributes are totally different from service attributes. How do you accomodate it?

[ngalemmo]

The simplest is to have a wide dimension table with attributes populated as needed depending on the type of product.  Some attributes would be common to all types, such a product ID, name and product type.  Given the nature of the subject matter (i.e. its a small dimension), there is no reason to do anything more.

[rkraj]

If there is no relationship between COURSE and FEE then we can't fit it...
What i am seeing is...
You can create a FACT table with Just
STUDENT_NO,FEE_TYPE, FEE_AMT, DATE
and generate the revenue out of different types of FEE_type for particular Student/customer.

[Andersan]

Anyone has any input on this? or any helpful material/schema examples will appreciate them

[ngalemmo]

After re-reading this, the advice I gave was pretty bad…

If I was to model this I would be considering the following dimensions:

Fee Type
Semester/Session
Course/Section (with allowance for a non-specific course as is the case with full-time tuition and general fees)

Generally in universities, revenue recognition is handled differently between the colleges and outreach programs (such as extensions). Colleges are usually measured by enrollment (FTE) with a standard $ per FTE value, while outreach programs look at revenue per course. Enrollment would be tracked by another fact table that would include semester/session and course/section as conforming dimensions.

[Sponsored content]