Did you see Star Trek: Generations? There, you have the potential of Picard in pain, the aftermath of the Borg (and Soran's nihilism), having the two crews unite (at least Kirk and Picard), the destruction of the Enterprise-D, the promise of the Nexus described by Guinan, etc. THAT'S the most disappointing moment in this series.
Insurrection should've been about the ravages of war and what steps a desperate society will take to save itself. The tone of the movie was simply a mismatch for the background of the story. People are dying in the most devastating war in the history of the Federation and we have Picard doing a mambo and Riker and Troi taking bubble baths.
I have gone through some trauma in my life, nothing I want to get into, but I have been through my own personal Dominion War. In the wake of that, I valued all the little things that life offered me, that were good. Everything from small amounts of chocolate to the sun coming up in the morning filled me with joy. Think of someone with a near-death experience that values life more because they have it.
Not everyone reacts to trauma by singing an opera about the subject, wallowing in it. Some hold their loved ones a little tighter, small things become big things, and this shows, in the wake of the war, while DS9 is doing the operatic song, that people return to a sense of normalcy, even taking delight in things they used to find mundane. And to be honest, I didn't value this movie until I had gone through that personal hell. Then I watched it over and over again. Laughing at the small jokes, taking delight in being explorers (Data petting the fish, Picard looking out the window at the planet, for instance). Over and over again.
As far as the production of the movie, and its timing, I found it a welcomed change-of-pace from DS9. I still love DS9, but this was different and I welcomed it.