I hit on this in the "how do you write a villain" thread. I think the most important part of a villain is that he's a round character. That's why I never really was frightened by Sauron in LOTR, but Saruman I believed as a bad guy. (Also Wormtongue.) It's like, okay, Sauron's evil, but... uh... okay... whatever.
The Borg are an exception because while they don't grow as characters (except for the Queen and Hugh's offshoots) they change nonetheless in their acquisition of new technology.
Voldemort, also... to me, not such a great villain. I mean, okay, once we get to Book Six we start to understand him and he becomes a better villain, but in the first four books he's just sort of this specter of evil, and in the fifth, he's a more evil presence but we just have to take on faith that he is, indeed, evil.
It's like... okay, take Slytherin. Supposed to be evil. All the students are pretty much drawn as flat, though the teachers are round (ie: Snape, Slughorn -- literally and figuratively). So you feel more worried about Snape than Malfoy.
The problem with film is that you often don't have the opportunity to face a fully rounded villain, at least not in a major motion picture. Most people don't want to think that much, or at least, studio execs think they don't. In Transformers, none of the bad guys except the CD player dude were really round characters, but on the TV show, Megatron and Starscream were both fully-formed characters.
On TV, you have the luxury of a story arc to really establish a good villain -- the opera guy from Highlander (series), for example, appeared in several episodes, and we got to know him and why he hates MacLeod from the flashback sequences.
IMO, the best villains are the ones that are the protagonists or the pragmatists -- books/movies/shows about a villain, like Day Watch or Dexter; books/movies/shows with Lawful Evil characters... they are doing what we consider "evil" because they think it's the right thing to do.
(Think about good guys killing bad guys... killing a bad guy is good, because you've gotten rid of him, but bad, because you killed him... so are you good or evil?)