Looking for some help designing a small prototype for investments. Ideally, what I'm trying to show is a position of a portfolio as it changes over time. A position is simply what value is attached to a portfolio at a given point in time, and a portfolio is the securities (stocks, bonds, etc) that are included in the measurement.
So if I have 5 shares of IBM that are worth 100$, 10 shares of Microsoft worth 200$, my position right now is $300.
I'm wondering how to track this. Do I have a fact table to track each individual investment, with a DimInvestment to tell me more about it, and a DimDate to allow me to slice and dice by date/time. If I did this, how would I track the positions? I was thinking I would have a DimPosition table that receives an entry each time the position changes as well as a new set of rows for my Fact table.
Am I headed in the right direction?