SQL Data Warehousing Tips 1 – Building a Period table

by on May 5, 2009

Recently, I started a personal project for analyzing stock market trends and calculating the optimal trading strategies.  I’ve learned a lot of new stuff about LINQ and SQL 2008 from this.  Based on that I’m opening a new series on data warehouse tips.  In my project, one of the things I quickly realized after calculating that I would need to generate about 1 billion rows in order to have sufficient detail for analysis of 5,000 publicly traded stocks with 1 year of history was that I needed to consolidate the data.  It was obvious I needed a way to summarize data into periods and use those as dimensions for analyzing the performance, rather than calculating all of this from the detailed data.  What would be nice, would be to have periods that could overlap and align.  For example, I want to be able to go back and look at last month, last quarter, last 6 months, and last year.  However each one of these requires a different set of transactions because even though the closing date is the same, the opening date is different.  The variation in open date affects the price at which a stock would be purchased at (or short-sold at), thus leading to a different set of transactions based on an entry/exit strategy.  In order to focus on the technical aspect of this, I won’t get into all of the application design considerations.  Suffice to say, that discrete, yet aligned periods were needed.

See the rest here: 
SQL Data Warehousing Tips 1 – Building a Period table

Leave your comment

Required.

Required. Not published.

If you have one.