Facts or Dimension Attributes?

View previous topic View next topic Go down

Facts or Dimension Attributes?

Post  jimbo1580 on Fri May 01, 2009 4:01 pm

My company is currently building a data warehouse and I have a design issue that I am not sure how to address. In general, our business is selling an asset, such as the stock of a corporation, with a certain number of shares and a value. The value of the asset changes daily as the stock price changes on the stock market. And the number of shares changes (less frequently) when dividends are reinvested. Our management wants to know the original value of the asset when we got it to sell along with its value at various times in the sale process. My design dilemma is how to design, store, and track these numbers and changes.

My first thought is that my fact table would be "Sale" and would represent the sales process. It would be an accumulating-snapshot fact table. Each fact would be linked to a dimension called "Asset", which will describe the asset being sold, along with various dates representing defined events in the sales process.

What I'm not sure of is where to put "asset value" and "# of shares" and how to track their changes. Once the asset is sold, the # of shares and value are facts that I want to track about the sale, but during the sales process, the asset value and # of shares are attributes of the asset that change.

Does anybody have any thoughts?

Thanks in advance!

jimbo1580

Posts : 23
Join date : 2009-04-30

View user profile

Back to top Go down

Re: Facts or Dimension Attributes?

Post  BoxesAndLines on Sat May 02, 2009 10:51 am

I'm not sure an accumulating snapshot is the correct fact table in this instance. As you've noticed, there is no good place to store fact columns asset value and number of shares. These are measures. That leaves either a transaction fact or a snapshot fact. Since the events in the sale process likely occur more frequently than daily, I would rule out the snapshot fact. The snapshot would be good for metrics like average daily balance or other predetermined time interval. That leaves the transaction fact approach. In this case, the grain is sale+event. In your fact table you can now track number of shares, asset value, and other dimension states as needed. Anything not work now?
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Facts or Dimension Attributes?

Post  radsampath on Wed May 06, 2009 9:43 am

If i understand clearly the Asset is a type 2 dimension with daily snapshots .Since there is need for you know the current value of the asset and what was the price when the process started ,i would suggest you tie your fact to the different snapshots of the Asset .Accumulating shapshots are meant to track the events and i am not seeing any events here .Have type 2 and do the point in time analysis

radsampath

Posts : 8
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Facts or Dimension Attributes?

Post  Jeff Smith on Thu May 14, 2009 11:34 am

I think you either need to be able to identify the original purchase transaction or create Original Purchase Amount and Original Quantity on the fact table and make these columns deginerate dimensions.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Facts or Dimension Attributes?

Post  ngalemmo on Mon May 18, 2009 4:42 pm

You have a sales fact which should be transactional in nature to record each transaction (including dividend/interest reivestments) tracking quantity, value by date, customer etc...
There is a second fact table tracking asset value (i.e. stock prices) by day (presumably) which would be used for mark-to-market calculations.
If you want, you can have a snapshot table combining the two other fact tables, summarizing current positions, original value and mtm value.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Facts or Dimension Attributes?

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