Kimball's The Data Warehouse Toolkit is the definitive tutorial on the subject.
We found that the strategy works really well on paper but there are significant scaling issues on the querying side when you throw massive amounts of data at it. A simple sales report over cities for one of our larger shops under moderate load would could take 5-10 seconds to generate, which is pretty unacceptable. Caching would only take us so far because of how much data gets ETL'd every moment.
I definitely don't discount the dimensionally modelled strategy, but to make it proper fast, and not 1990's let-me-hit-report-and-go-get-a-coffee fast, you might need to write your own OLAP stack that's optimized for what you need[0]. I'd also do it in go or c.
Once we ship, we'll do a technical post on what worked and what didn't.
[0]I'd love to be proven wrong on this, so if you can generate fast reports with massive amounts of data ETL'd in real time, I'd love to hear from you. oren.mazor@shopify.com
You're actually at the coalface and I'm the bookworm, so I am totally prepared to be schooled on this one.
We did partition all of our dimension and facts tables, which helped a great deal. Aggregates were a problem that is specific to us, so we couldn't cheat the usual way that reporting servers do.
The other problem is that our stack is suddenly fully of things like java and olap and postgresql, which made onboarding people who wanted to help, and just debugging, a pain.
I like that comment about the coalface/bookworm, but sometimes it takes somebody on the outside to see what I'm missing.
