1) Yes, partitioning absolutely matters on a SAN. But remember (as almost everyone initially assumes) that partitioning was NOT created to make queries run faster. It was created to facilitate large-scale data load/movement/maintenance.

2) Having said that, it certainly CAN make appropriately-crafted queries run much faster and have better concurrency if partition elimination can be achieved. But given your statement that there isn't an obvious partitioning key(s) this probably isn't relevant.

3) The design I am envisioning from your post seems flawed. At first blush I would think the actual bet values would be in the fact table, with a dimension that was the actual thing being bet on and a dimension of bettors. Maybe your dimension table is storing the changes in odds as the bets fluctuate? But I would think that may not need all values stored for ever - once the event was complete and the final odds calculated you could archive out all the values necessary for it's computation.

4) Which brings me into DEFINITE agreement with your thought about data archival. Any rows that don't need to be in that monster table should absolutely be deleted/archived out!!!! Disregarding the massive problem of having to scan that beast, even a single byte lookup in a nonclustered index could take a dozen or more pages in the balanced binary tree with 6.5B rows in the table!

5) Sounds like an exceptionally fun system to work on!! If you need someone to take a look and help out I would give you a discount rate to have a crack at it. 😎