How to best model this business process?

Question

How to best model this business process?

aaa121

SSCertifiable

Points: 5902
More actions
December 31, 2010 at 2:14 am

#232556

I have been thinking about how best to implement the following logic within a dimensional model for some time, I haven't yet came up with a clean solution.
The business question is one around efficiency of account management. Accounts are associated with what is know as a router, for the purposes of this discussion these can be thought of as 0-20. A change in an account status will drive the moment of an account between routers. The question is two fold; how many accounts are moving between routers over a period of time and how long does an account remain in a router.
Originally I was thinking along the lines of creating a accumulating snapshot fact table to capture the dates when an account entered a router, adding the associated time lag calculations as appropiate......then I found out accounts can re-enter a router more than once!
Any ideas?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

aaa-322853 (12/31/2010)
I have been thinking about how best to implement the following logic within a dimensional model for some time, I haven't yet came up with a clean solution.
The business question is one around efficiency of account management. Accounts are associated with what is know as a router, for the purposes of this discussion these can be thought of as 0-20. A change in an account status will drive the moment of an account between routers. The question is two fold; how many accounts are moving between routers over a period of time and how long does an account remain in a router.
Originally I was thinking along the lines of creating a accumulating snapshot fact table to capture the dates when an account entered a router, adding the associated time lag calculations as appropiate......then I found out accounts can re-enter a router more than once!
Any ideas?

FACT tables should always contain granular data - that's why we call them FACTual tables. In short, each row shall describe a single event. What DIMensional tables are you planning other than DIM_DATE and DIM_ROUTER?

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

aaa121 SSCertifiable Points: 5902 More actions · Answer 2

Sure, dimensions date, router and account. I just don't know how to structure the fact table in order to answer the business questions

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 3

aaa-322853 (1/1/2011)
Sure, dimensions date, router and account. I just don't know how to structure the fact table in order to answer the business questions

Specification suggest a row has to be inserted into FACT table each time an account status change happen. Not knowing the data my guess is captured data should include: account, router, new_status and, timestamp.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

aaa121 SSCertifiable Points: 5902 More actions · Answer 4

Hi Paul,

Thanks for your response.

The structure of the fact table you've suggested is straightforward. Specifically what I am trying to picture is how the business question can be answered from that fact table within the cube?

jeff122877 Mr or Mrs. 500 Points: 535 More actions · Answer 5

I would populate the FACT table with only those accounts whose status in the dimension has a Start_Date and an End_Date (Effective date and Expiry). With those two dates you can add a column for Days_In_Status. This will still cover those accounts that go into the same status more than once.

If you need to know about ALL accounts that are in a given status, even when they are still in that status (no current expiry date), then I would contemplate rebuilding the FACT table on a nightly basis. It's not such a bad thing to do, we do it all the time.