Need help designing this star for education sector/university

View previous topic View next topic Go down

Need help designing this star for education sector/university

Post  inventivethinking on Tue Aug 19, 2014 3:14 pm

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,

inventivethinking

Posts : 3
Join date : 2014-08-19

View user profile

Back to top Go down

Re: Need help designing this star for education sector/university

Post  rkraj on Wed Aug 20, 2014 1:23 pm

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?

rkraj

Posts : 12
Join date : 2012-06-29

View user profile

Back to top Go down

Re: Need help designing this star for education sector/university

Post  inventivethinking on Wed Aug 20, 2014 2:49 pm

rkraj wrote: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?

inventivethinking

Posts : 3
Join date : 2014-08-19

View user profile

Back to top Go down

Re: Need help designing this star for education sector/university

Post  ngalemmo on Wed Aug 20, 2014 9:59 pm

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

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Need help designing this star for education sector/university

Post  rkraj on Thu Aug 21, 2014 7:27 am

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.

rkraj

Posts : 12
Join date : 2012-06-29

View user profile

Back to top Go down

Re: Need help designing this star for education sector/university

Post  Andersan on Tue Nov 04, 2014 11:52 am

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

Andersan

Posts : 1
Join date : 2014-11-04

View user profile

Back to top Go down

Re: Need help designing this star for education sector/university

Post  ngalemmo on Tue Nov 04, 2014 1:08 pm

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

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Need help designing this star for education sector/university

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