It think the Dominion war works but as a framework for some of the more complicated choices the characters need to make. It also give us a chance for decisions to not play out in an hour episode.

It gave us the most interesting human klingon stories since TOS. Allies with different views and where the klingon perspective has more value.

I agree the actual battle scenes and episodes are usually week.

I also agree Sisko and the wraith / prophets stuff was terrible though. Though I always hated the prophet stories not just the end. I would have loved to see a better ending to Dukat and Winn than blasted with space magic.

Star Trek just isn't good at big wars. Single battles where "Oh shit there's a borg cube coming" can be tense and exciting but I'm way more into the ethical dilemma or space mystery mind screw episodes.

I think that's the main problem with Enterprise. Someone fresh out of Voyager's writing room said the phrase "temporal cold war" without thinking about what those words meant first, and then they said "Fuck it, let's go full Starship Troopers."