Question on Type 2 Dimension

View previous topic View next topic Go down

Question on Type 2 Dimension

Post  sathishkumar on Thu Sep 01, 2011 7:34 am

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 ?

sathishkumar

Posts : 1
Join date : 2011-09-01

View user profile

Back to top Go down

Re: Question on Type 2 Dimension

Post  BoxesAndLines on Thu Sep 01, 2011 9:57 am

Move market value to the fact table.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Question on Type 2 Dimension

Post  ian.coetzer on Thu Sep 01, 2011 3:48 pm

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
avatar
ian.coetzer

Posts : 57
Join date : 2010-05-31
Age : 36
Location : South Africa

View user profile

Back to top Go down

Re: Question on Type 2 Dimension

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

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