I disagree with concept of main antagonist being a Big Bad Evil/ Final Boss to beat. I don't want another game in which despite of what I do - I am the good guy.
I could be the worst **** on the planet, but at the end I bring back the water chip and save the village. Or destroy the evil treat of put_name_here by accident, cause my evil empire was running low on milk and I went out to get some and *this* happened.
Screw that, there was enough games where the main character is (like it or not) the savior. Sure, story needs a culmination and resolution, but it doesnt have to be "stab-the-ubg' kind.
Being more on topic,
The best antagonist is the one that either:
1)has a conflict of interests with you. He can be a good-doing paladin, pure at heart and unquestionable in his ways - with the only trouble being - he/she sees you as the villain (perhaps even is right about that ).
It could be the 'convincing' guy - see Letho from Witcher 2, who in the final scene pitches you a variation of the old line "we are not so different you and I" - and worse, he is most likely right. Or Loghain - after you hear him out it raises the question "if I were in his position, knowing just what he knew, wouldn't I do the same?" (also known as "Nothing personal, just business")
Or this (pretty forgotten) guy, CBG Spender:
Not to be confused with another smoking fella from ME2 - who also fits the archetype.
For both of the smoking guys, the agenda was to resolve the same conflict you are trying to - just the means and ways were outside the comfort zone. But RPGs being about choices, an unique opportunity to see their way arises. Shades of grey make perfect antagonists, black and white makes hack-n-slash games.
2) the second type - it's personal. For you or for the antagonist, though better if for you. Irenicus in BG2 is a perfect example - Some may see the plot as player's attempt to save the world (a-gaain), but I think for many of us it was personal. Not because he is to be hated, but because he went after the main character and his friends at one point. And doesn't seem to stop there. See also: Vlad in Max Payne 2.