Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

What's the best dimentional design for this situation?

2 posters

Go down

What's the best dimentional design for this situation? Empty What's the best dimentional design for this situation?

Post  leinatian 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

Back to top Go down

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

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

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

http://aginity.com

Back to top Go down

Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum