Note this is the percieved danger, not the actual danger.
Psychological warfare is, simply put, about screwing with the other guy's head. At the moment, the best I can think of is to offer some examples of psychological warfare units, and how/why they are percieved as such. Note that this is not an exhaustive list of psychological warfare units; it's just an example of some and a few thoughts on why they qualify.
The Doom of Malant'ai
Well, let's go right on ahead and start with the one everyone's talking about. See, part of its impact is the fact that everyone seems to be talking about it. It's all 'OH MY GOD! IT CAN GET 10 WOUNDS AND THROW OUT AN S10 AP1 BLAST! IT CAN KILL 10,332.77721 INFANTRY IN ONE PHASE!' And then there's the whole rules debate about whether or not infantry in transports are effected by the whole 'psychic vampire' deal.
Now why, exactly, do people worry about it? Here's what I can think of:
1) On a good drop, it can potentially effect several infantry units
2) It can potentially force morale checks/retreats on said infantry units
3) It turns around, has 7-10 wounds to its name, and applies a thermonuclear psychic blast to something important
4) Oh, and it also arrives via drop pod, so there are fewer issues with mishaps
So, we've established (in people's minds) that it can potentially ruin your day single-handedly. Ok, now let's pierce the psychological veil:
1) the average roll on 3d6 is 10-11; most units have ld 8-10. This is 1-3 casualties. Big whoop.
2) S8 doesn't care how many wounds you have after you fail a save
3) Can't shoot that psychic blast if you're in assault
4) Psychic hoods, etc. still stop the Cataclysm
Now, the Doom probably WILL kill some infantry (if it hits 'em in transports), but unless you really cluster up and give the doom a sweet spot to land in, it'll kill a few, and hopefully your army has some S8 around to deal with the problem. Can you hit it with three S8 wounds? Then you can probably solve the problem. Or, can your army take out a marine squad in a turn? Then the worst the Doom did was absorb fire.
Marbo is, in my mind, a bit more credible of a threat than the Doom. Marbo is a mere 65 points for a guy with Stealth that appears where he wants to. Once there, he has a nasty pistol, a demo charge, and poisoned melee attacks.
Why is he scary?
He can't be within 1" of the enemy. He is one infantry unit. It is very easy to leave a hole (as he only needs a 2inch + 20mm hole to appear in) he can inhabit. Then, he chucks a demo charge at something expensive.
Rambo basically makes you ask 'did I deploy in such a manner as to deny the enemy a demo charge shot into the rear of a vehicle?
Basically, he's enough of a bargain (in non KP missions, anyway) at 65 points to make the other guy deploy a little more carefully.
It's nastier against armies that have multi-wound T4 creatures, as S8 can insta-kill priority targets like Crisis Suits, Tyranid Warriors, and Zoanthropes just to name a few. It also can harm vehicles, especially if you tag them from behind.
On the other hand, once he chucks the demo charge, Rambo is not THAT frightening. He'll throw out pistol shots, or try to stab something, but you can usually turn around and just shoot him down or assault him. All he does after the demo charge is beg for bullets and a gruesome death.
This is actually a category as opposed to a specific unit. Basically, something like a Whirlwind or Basilisk sits back and chucks explosives at you, and you can't shoot it back (unless you have ordnance of your own). This is frustrating because it feels 'unfair'; nevermind that your opponent paid points for it normally. It's shooting YOU, but YOU can't SHOOT IT BACK! It's frustrating for some folks.
But, but, what happens if they're suddenly on your flanks? The scare value of outflankers depends on the outflankers and what they can actually do when they get there. You're gambling that you'll get your troops on the right side (66% chance, or the same as a power armor save before any re-rolls) at the right turn (...good luck computing those odds) and they'll be able to do something (which is rather up to your opponent).
If you're outflanking a smaller unit or only 1-2 selections, it's not a credible threat. If you outflank too much, you risk diluting your main forces. I mean, you COULD outflank a Land Raider full of Terminators*, but what happens when it shows up on turn 5 and 600 points of your army did precious little?
It's a gamble, but it's one I honestly don't think much of. However, that's me.
Psychological Warfare versus Legitimately Deadly Stuff
Some units draw fire because of their percieved danger. Other units draw fire because they actually ARE that frightening. Here are some examples of units that draw a heap of fire just because of the damage they can reliably do to your army, and they'll rarely last a match.
Space Marine Land Speeders w/ Multi-melta, heavy flamer
Did you bring tanks? It can nuke 'em. Did you bring non-power-armor infantry? We can roast 'em. It's an AV10 platform that's in your face, threatening your assets with death on turn 2. The only armies that don't abjectly worry about them are those that tend to 'spam' stuff like razorbacks or chimeras. Anyone relying on heftier tanks or camping infantry have GOT to pay attention to these things, or risk losing expensive tanks or swathes of infantry.
They have BS4 meltaguns. They are fast. They can get in your way. They are AV11, so you HAVE to use anti-tank weapons on them. Oh, and did I mention they'll block you and/or kill your expensive stuff if you don't handle them? They may not always kill stuff, but they CAN impede progress and buy time for the Tau player, which is well worth it.
Eldar Fire Dragons
How do 5 meltaguns at point blank strike that Land Raider, monstrous creature, T4 multi-wound unit, etc.? I think the answer is 'very nicely' if you're Eldar, and 'OW!' if you're not. Fire dragons draw so much fire because they really ARE that good (and cost-effective) at killing stuff. They're a legitimate threat to any armor short of a full-on infantry horde.
The Real Perk of Psychological Warfare
The real goal of psychological warfare is to make the enemy make a mistake. You're bringing a unit whose percieved danger is out of proportion to their actual danger, or at least you hope that's how the other guy views it. Is it viable? The answer is actually the two most powerful words in the English language: 'It Depends.' It depends on the unit you're calling on to do this task, and on how the other guy percieves it.
That's why I consider psychological warfare a gamble. It will work on some people some of the time, and you're hoping that's enough.
*Provided you take Khan, and take the Land Raider as a dedicated transport.