fact tables for stock market analysis

Dear all,

For a small project I'm importing (political) stock market data into a data warehouse to conduct detailed analysis.

I've already created the following fact tables and accompanying dimensions:
orders (accumulating snapshot): store details of the buy/sell request of assets and add resolution data when available
transactions (transactional): store date,buyer,seller, amount, volume and price of traded assets
share prices (daily snapshot) loaded from transactions: aggregate high/low/open/close etc. for each asset

However, I also want to analyze the portfolio (number of assets / cash) and performance of each user (calculate and compare the value of all his/her assets) and compare them over time.

I looked arround and found: https://kimballgroup.forumotion.net/t1551-dimension-model-design

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...)

Can you give me some hints on how to achieve this? How could the additional fact tables look like?

Thank you in advance,
Roman

I would build a snapshot fact table, much like a checking/savings account, that would give the total value at the end of the day given the current positions held in the account.

Dear BoxesAndLines

Thank you for your fast reply. It will help me to proceed.

I have another question which should be easy to answer for an expert but is quite confussing for a beginner:

As described above I have an order (accumulating snapshot; with order nr as DD) and a transaction fact table (with the corresponding order numbers of Buyer and Seller as DD). If I now want to know which orders were open at a specific point in time is it okay to make two subqueries and left-outer join them using the order number (DD) and other conformed dimensions or is there a different, better solution?

Thank you very much,
Roman

Another question:
When to build an olap cube an where use sql to query/relate multiple facts?