It varies, for me at least. I've had orgasms that have been like the line from Hitchhiker's Guide To The Galaxy about having your brain smashed out with a large gold brick, and ones that have been barely noticeable.
But in general, just before orgasm is the point of maximum tension before the release, and that's going to be enjoyable, regardless of how strong the orgasm is.