What's the best dimentional design for this situation?

View previous topic View next topic Go down

What's the best dimentional design for this situation?

Post  leinatian on Fri Aug 05, 2011 4:53 pm

Hi, I'm a beginner of dimentional design and I was given a task to design our Legal Aid Duty Counsel data warehouse structure.

To simply explain our current situation. A referral is given to lawyer for a certain service date for certain # of clients the lawyer should serve on the day. Later on the lawyer will bill us with usually more than one billings/submittals with the amounts and # clients he actually served. The submittals are entered on different batch date.

Usually the questions we are trying to answer are
- Referrals (by service date)
- Billing (by batch date and service date)
- Client Counts (by service date)
- Any of the above combined

If I have one fact table, I would have more than one lines for same referral (hence same # clients referred) but different billing information (i.e. billing #, # clients actually served, $ amount billed). This will cause problem if “referrals” are reported by “batch date”, and there is more than one submittal, then referral metrics would be reported more than once.

If I have three fact ables as following, I kind of feel that I'm not following the best practice in Kimbal method, because we'd like to have as less fact table as possible?
DutyCounsel. FactDutyCounselDailyReferral (service date level)
DutyCounsel. FactDutyCounselBilling (submittal level)
DutyCounsel. FactDutyCounselClients (submittal level)

Any suggestion on which way to go? Thanks a lot in advance!!

leinatian

Posts : 1
Join date : 2011-08-05

View user profile

Back to top Go down

Re: What's the best dimentional design for this situation?

Post  ngalemmo on Sun Aug 07, 2011 1:15 pm

I would not use one fact table. The processes behind deferral and billing are far too disjoint to try to keep everything in one place. As far as clients go, it depends on how you define it... is a client a referral or someone you bill? But either way you probably don't need a client fact.
avatar
ngalemmo

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

View user profile http://aginity.com

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