dimension model design:

Hello,

I am trying to design dimensional model for stock market data. I am unable to design fact tables for this.

The measures I have are open, close, high,low, volume, adj prices

no of shares, share purchase(dollars), investment dollars, investment value dollars, investment return percent.

The dimensional attributes are stock name, day of purchase, industry and sector.

The problem is unable to determine the grain (to which of the three types, i need to categorize and decide?)
Also can the fact tables contain repeatitive groups? (I am assuming that the fact table is highly normalised, 3 NF ).
Can I have more than one fact table then? If yes can I have same foreiign keys (derived from the dimension table primary keys) for these two fact tables?

I a just a beginner in the process of learning. Any intuitive explanation and justificatin ismch appreciated.


Thanks,
SVO.

There would be multiple fact tables. At the lowest levels, a fact table represents a business event or state. From what you describe, you mention trade data, market close data, and portfolio performance... all different facts at different grains. Grain, nature of the event/state, and timing are considered when identifying fact tables.

From a modeling standpoint, each fact table is fully independent of any other fact table, so each fact table contains a complete set of dimensional foreign keys appropriate to the fact. It would seem to me you would have more dimensions than what you have listed for some of these facts (client, portfolio, etc...)

Thank you.