Hi.

I've got a post on my blog that should be able to help you:

http://jsimonbi.wordpress.com/2011/01/14/dealing-with-parent-child-sql-queries-pt2/

Here is an excerpt that aggregates each level:

;WITH CTE

As

( SELECT p.ProductName

...

Probably too late but:

You want to compress as much information on a page as possible.

Things to consider for your fact table:

1) Use small datetime if possible

2) Avoid varchar fields...