Dimensional modeling - deposit entity

Alex Sharkov

Im folow bus matrix by Ralph Kimball

There is 2 bank business process:

- Get deposit from client in some currency

- Prolong deposit

and i highlight some dimensions for bus matrix:

-Deposit (dim$deposit)

-Client (dim$client)

-Currency (dim$currency)

-Product (dim$product) - cause some type of deposit we have

after all i create 2 tables facts:

fct$deposit (id_deposit, --this surrogate key

is_src,

date_begin_deal,

date_end_deal, --plan date

date_fact_close, --in fact close

ref_client, -- reference to client

ref_currency, --reference to currency

ref_product, --reference to product

ref_deposit, --that's reference to dim$deposit

ssum -- that's sum of deposit

)

fct$deposit_prolong(

id, --this surrogate key

ref_src, -- id on source system

ref_deposit, --reference to deposit dim

date_begin,

date_end,

ref_product, -- with prolong product can change

ssum --sum of prolongation

)

--for this fact tables i populate data as one row in source system = one row in dwh

ok and dimension's (i skip currency and product.. and other)

dim$deposit with other attributes of deposit entity

ok now i have :  fct$deposit refer to dim$deposit, and fct$deposit_prolong refer to deposit

In my situation there is STATUS of deposit  on source system ..and i think about what better way to  add status.

time to time status of deposit can change : deposit can to be OPEN, WORK, CLOSED

So in my question what a better way to add STATUS?