CODEWITHSUNDEEP
databasedatabase-designdata-warehousedimensional-modeling
user18620
user18620

Reputation: 120

Dimension Modeling: need help doing this star for a university

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

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 definately appreciate them

Thanks,

Upvotes: 1

Views: 63

Answers (1)

fenix
fenix

Reputation: 1746

You will find a plenty of those cases in future. Generally, you are experiencing this problem because you are mixing two different types of 'product' in your case.

It can be resolved logically or technically.

During the ETL process, in cleansing step, you can rewrite your null fields with sql (nvl, coalesce, CASE WHEN) with something related to field

nvl(CourseCode, 'No Course Code') as CourseCode

Then, when you group by ProductType, and CourseName you shoud get something like this:

ProductType     CourseName     sum(Amount)
------------------------------------------
AppFee          Course1             345.13
AppFee          Course4            8901.00
TranscriptFee   No Course Name      245.99

Or, you can put it in separate tables. Even that is contradictory to your business process (can't have different products in fact row), sometimes terms you want to merge (i.e ApplicationFee and TranscriptFee) have many different grouping levels which is often too hard to map.

Edit:

No, snowflake make sense when there exists big dimension tables, high cardinality, many levels, as well as many to many relationship (i.e movies, categories). In your case good idea is to follow ERP/CRM database design, because it's current working solution. If there is no such reporting possibilty you want, you can make more generic dimension table:

Product-Service Dimension
--------------------------------------------
SurogateKey
NaruralKey
Type(Product/Sevrice/Other)
Level1(ProductType/ServiceType)
Level2(ProductSubType/ServiceSubType)
Level3
Level4
Attribute1
Attribute2

Upvotes: 1

Related Questions