I found it cool in lot's of games, mostly games wich made use of it to make the feedback to make the gameplay feel more immersive.
In Ratchet & Clank, where you were guiding tornado, were pretty sweet, it felt like you worked against the elements of nature, dragging the wind in place.

It were cool to be able to stear that, and Ratchet and camera at once..
Guiding arrows over long distances in Heavenly Sword were also fun, especially fun to watch a friend fall out of the couch when trying to do last minute adjustments to hit the groin of a dude, and failing miserably.
Didn't like guiding cannon-balls, or other objects as much as the arrows.
I also liked the minor sniper-adjustments in Killzone, not so much as extra assistance, but it felt more realistic that you had to keep the gun (or in this case controller) absolutely still when aiming..
And shaking the controller to ward off attack-dogs wich had downed you, or shaking to roll to get the fire from flamethrowers in first Resistance, were also pretty cool, I think that might be the best use overall.
The most stupid thing , where shaking the controller as a madman to eat 100 Pizza-slices in a JRPG, granted it were funny and hard to do while laughing..
The boob-jiggling thing in MGS4-videos were also epic.
