What's the best dimentional design for this situation?
2 posters
Page 1 of 1
What's the best dimentional design for this situation?
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!!
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
Re: What's the best dimentional design for this situation?
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.
Similar topics
» Different time zones
» Dimentional Modelling Insight
» Centipede Ok in this situation?
» How to handle the following situation? (Sorry, I cannot come up with an appropriate title for this...)
» Confused on how to model a certain situation.
» Dimentional Modelling Insight
» Centipede Ok in this situation?
» How to handle the following situation? (Sorry, I cannot come up with an appropriate title for this...)
» Confused on how to model a certain situation.
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum