Yeah, it's all about compression. The higher your engine compression is, the higher octane you need.
Octane is a way of measuring a fuel's tendency to "pre-detonate". Think of how a diesel engine works. There are no spark plugs - the fuel is simply injected into the combustion chamber, and the pressure and temperature combine to create explosions.
In a petrol engine, when you have very high compression (let's take my M5, with 10:1 for example), if I put in 87 octane, the fuel would detonate due to pressure and temperature before the spark plug fired. The piston and valves would not be in the right place for the detonation to occour. The likely outcome of this on almost all modern engines is the exhaust valve opening only to greet the piston, which is the "knocking" noise.
So, as far as normally aspirated engines go, you need to find an octane that causes your engine not to knock. If you have done that, there is *no* benefit to going any higher.
On most turbo/supercharged cars go, as long as the boost control mechanisms are working properly and have not been modified to allow more boost, the manufacturer's octane rating still is the highest you should bother going. The engine won't say "Look, he's fed me 94, I'm going to run more boost!"