Ballista elephants can be VERY powerful in the right situation, but it is so rare to find the opportunity. It can be quite useful to get rid of powerful mounted units without having to chew through defenders. But the times where it is actually useful are so few and far between, at least with the AI, it is virtually pointless.
I always wondered if it might have more use in multiplayer (the human being unable to protect mounted units with pikes and maces, or as Kawalimus getting their fast healer out of the picture)?
Nope, BE still suck even then.
If mounted is part of a big stack, it tends to be collateral'd to death (and lacking defensive bonuses, mounted is not so great vs contemporary collateral). This is assuming the opponent isn't using elephants of its own and for some reason opts to use mounted against you (idiots in MP, or the AI otherwise). But the AI is bad and defensive wars in this era aren't common unless you screw diplo. EVEN AFTER ALL THAT, you still require a rare resource.
It would probably be more effective in multiplayer, as the players would actually have to defend against attacking stacks. In single player, the AI is most often on the defensive, so their units tend to be fortified in cities, which happen to be the one location that ballista elephants' effect doesn't work.
So, you're going to attack Khmer in MP. He has ivory. Would YOU build mounted to attack him? Why assume anyone else would? If you're playing someone bad enough to attack you with a mixed stack of mounted (not a great move to begin with) when you're khmer, you don't need the UU to win. You beat that person when he/she joined.
If mounted is by itself, the BE is a normal elephant. It is hard to put it above bottom 3 UU, if not #1 worst.
Prats are very good and can handle most defenders alone in their era.
Bowmen are annoying to attack but pretty bad otherwise, and completely normal vs mounted.
Landsknecht is admittedly bad.
Janissary is average in that it makes good stack cover for cannons and will beat everything below muskets in the field. On slower speeds you can probably off non-deity AIs with it.
Panzers plain suck vs the AI (As in their adjusted value over tanks, tanks are good in general vs the AI but panzers don't offer much extra at all) and aren't terribly common in MP unless you start in that era...at least tech parity tank warfare where no naval implications exist isn't super common in MP ancient starts...
SEALS are in a similar boat ---> with air cover they're a moderate boost to the naval raid stack, but not quite good enough go to on their own cost-effectively...and given that they're late...!
Redcoats used to be good and people still overate them because of that. They're not good enough to take cities defended by rifles w/o siege, and they're not good enough to handle pinch cavalry or siege + whatever in the field.
Cossacks are a little better. Cavalry charges are solid in BTS (not as good as 18 str rape though). The thing about cossacks is that you can take pinch and still dominate defending mounted, and will be odds on against contemporary field stacks. Not great, but an average-above average UU instead of a top 3 one it used to be.
I'm also not a big fan of NC despite liking general HA's a lot. NC is gimped vs archers and those are very, very common defenders. It is also gimped vs other mounted. The AI doesn't build a lot of spears. In MP i'd rate it a bit higher, but as it STILL doesn't beat spears consistently and is more expensive than one, I'd not be especially eager to use it.