Question on Type 2 Dimension

[sathishkumar]

We have Dimesion table called Account , where we maintain the account information and its martket value.
This table is of Type-2 Dimension.
Since the price of stocks changes daily, the market value changes daily where as other columns of the Acccount table does not changes.

Eg:
Account_number | Account name | client_name | Market_value

Due to this design, we have the row inserted daily and occupies more space on the database. Is there any specific design to caputure the history without creating a new table to store Account_number and Market value ?

[BoxesAndLines]

Move market value to the fact table.

[ian.coetzer]

Hi Agree,
This 'market' value is a 'fact' about the account - it does not 'describe' the account.
So it belongs in a fact table.

There are three types of fact tables:
1) Transactional
2) Accumulating
3) Snapshot

In this case it sounds like you need to create a snapshot fact table - which simply stores the market value at some point in time.

It does not sound like a specific business event that will trigger a new fact record - and thus not 1) or 2)
And you do not want to simply revisiting the record - you want to insert a new record - if you were going to simply revisit the record and update the fact i would suggest an accumulating fact table.

The grain for this new fact table would be:
* one record per account per date

[Sponsored content]