fact tables for stock market analysis

View previous topic View next topic Go down

fact tables for stock market analysis

Post  romanp on Sun May 19, 2013 4:53 pm

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: http://forum.kimballgroup.com/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

romanp

Posts : 3
Join date : 2013-05-19

View user profile

Back to top Go down

Re: fact tables for stock market analysis

Post  BoxesAndLines on Mon May 20, 2013 10:37 am

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.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: fact tables for stock market analysis

Post  romanp on Thu May 23, 2013 3:25 am

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

romanp

Posts : 3
Join date : 2013-05-19

View user profile

Back to top Go down

Re: fact tables for stock market analysis

Post  romanp on Mon Jun 03, 2013 1:48 am

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

romanp

Posts : 3
Join date : 2013-05-19

View user profile

Back to top Go down

Re: fact tables for stock market analysis

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