Well I agree part 1 holds up a lot better than part 2. however, here is how I see it for some of your questions:
Where were the Klingons, Romulans, and the other superpowers?
I think it can be argued that when the Borg hit both the Federation and Romulan outposts, they determined the Federation the greater threat and attacked the UFP, first. From the Romulan POV, they were content to sit back and see how it played out. From the Klingon POV, maybe they didn't have time to mobilize, or couldn't due to internal issues. No doubt the Council was starting to fracture after Sins of the Father. Cardassians were probably experiencing the beginnings of the collapse of their empire on planets like Bajor and other conquered lands. And the Dominion had not been encountered yet. The Gorn, Breen, and Tholians are more or less second-rate powers.
Why was there only a single Borg cube?
Good question. Perhaps they felt it was sufficiently adapted since the last encounter with Enterprise D and the outposts the Borg destroyed, and thought 1 was enough. The real question is why did the Borg only send just 1 cube in FC, after TBOBW it was obvious more than one was needed.
This felt like a conflict between the Enterprise and the Borg, rather thana conflict between the the Federation, it's allies, and the Borg.
Probably because the Enterprise was pretty much the only ship left from the federation Fleet, after Wolf 359. And as for the allies, they were either too far away and\or had their internal problems.
The stakes never felt very high to me. Once Picard was saved the tension just dissolved. I can understand if this episode was a real shocker the first time around, especially if you had just watched part 1 and had months to wait before part 2. But in retrospective, is this episode really as good as you remembered?
Well the Stakes seemed high, but tension seemed less, once Picard was rescued. Part 1 was a shocker and a real cliff hanger first time around. I was in the service when Part 2 premiered (I had just graduated Basic and was in AIT at the time, preparring to go to Gulf War 1) and I remember all of us who were trek fans crowded around the TV for Part 2(every last one of us enthralled) because we all just "had to know" how the Borg were going to be defeated! It was definitely suspenseful and exciting.
When I watch it now, Part 1 reamains just as awesome as when I first saw it, while Part 2 is a bit anti-climatic to me.