It was cool because it showed a pre-federation starfleet. Humanity is the underdog scrappy little species trying to get its feet wet in a much larger galactic community. Because starfleet did not have technical parity with other races the stakes felt much higher for each encounter than in TNG or TOS era.
It was lame because it was ENTIRELY too horny. Also the Xindi subplot was painfully obvious as an allusion to the war on terror. It didn’t land for me.
Overall it was a great show though. It explored lots of interesting technical details of the world of Star Trek and attempted to explain their genesis. Reed alert, the prime directive, the paradox of being a diplomatic vessel with MACOs aboard and the jurisdiction of force.
I laughed at the show at its premiere, but by the end I was a die hard fan. They really won me over.