Facts or Dimension Attributes?

[jimbo1580]

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!

[BoxesAndLines]

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?

[radsampath]

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

[Jeff Smith]

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.

[ngalemmo]

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.

[Sponsored content]