The problem was that Berman ran the formula for TNG into the ground.
DS9 was more of a Ron Moore and Ira Steven Behr operation (my understanding was that Berman concentrated on Voyager and for the most part left them alone to run their own house), and while not as successful as TNG, it was still I believe in the top five of syndicated dramas during its run back in the day. And DS9 was criticized during its original run for being the "dark" Star Trek series that was different. There was a contingent of Trek fans that believed it had betrayed Roddenberry's legacy.
Both Voyager and Enterprise were more firmly Berman-Braga productions and basically aped the TNG formula within their setting over and over again to the point of being stale. The one thing that struck me about watching Enterprise in the first 2 seasons when it was under their control as showrunners is how it takes no chances or deviations from Voyager or TNG, as far as music, tone, or just visual style.
To me, the tone of Strange New Worlds is the Discovery tone which leans into more of a Kelvin Universe-esque feel while not calling it that.
Berman gets blamed, but that was not on on Berman. That's Trek fandom's short memory.
With Voyager, all the original EPs wanted a darker show were consequences mattered and carried forward week to week. Not exactly year of hell, but closer to Battlestar Galactica.
With Enterprise, Berman and Braga wanted a show that was again, highly serialized. The first season would be about getting the NX-01 built, and the first missions would commence in Season 2.
In the Voyager case, since Voyager was the lead show of the then new UPN network, the network executives wanted a highly accessible TNG clone. They said "do TNG".
In Enterrpise, since that was again going to be the lead show on UPN, the executives said "we want accessible, do Voyager".
In both cases, Berman was handcuffed to the demands of UPN, because launching that network (and the advertising revenue it would bring in) was considered vital to Paramount. Paramount had been wanting to launch a 4th (by the 90s, 6th) network since the late 1970s and Star Trek was always going to be a centerpiece. Berman and Braga had to fight tooth and nail to not only get Season 3 of Enterprise made, but also to have the serialized story finally done. And they had behind them 9 years of argument that a purely episodic show wasn't attracting audiences. Season 4 got made because the budget was cut massively (2/3rds or something). But Season 3 and 4 also had an enemy in the form of Les Moonves, who detested Sci Fi and Star Trek in particular. His becoming head of all of CBS (a promotion in 2003) is the singular event that led to Star Trek's cancellation. His exit is one reason it came back in streaming form (he was on his way out the door and the makeup of CBS All Access was left to subordinates).
There is no beating an executive who doesnt want you to exist. It's as simple as that.
The decline of Trek in the 90s is complex. Part of it is tied to the decline of syndication in general. By 1997, DS9 was pretty much the last major syndicated non-talk show. It suffered due to a fading model as network centralizaiton took root - namely the founding of WB, then UPN, and then in 2006, the CW merger. Also the spread of cable in the 1990s. DS9 was the last off a sinking ship.
Voyager? Being the centerpiece in the 6th place network that was only available in something like 40% of American homes at launch and 70% of its peak badly hurt it. As did the fact with a national timeslot as a network show, it was routinely preempted by sports (especially Hockey, which the smaller networks that carried UPN in major metro areas usually picked up as Football and baseball went to larger networks). This is the same for Enterprise, coupled with the breakout of broadband internet changing people's leisure habits. Target demographics stopped watching shows and started playing MMOs. The spread of DVD at the time also led to people waiting to buy entire seasons of shows on DVDs (and DVDs sold very well), a kind of precursor to binge watching.
None of this is to say that Berman (and later Braga) didn't make MASSIVE mistakes. Oh they did. They made decisions which directly undermined viewership of their shows. Ensign Kim, Mayweather and Chakotay were horrifically squandered characters. The mangling of the Borg three times a season undermined one of the Franchise's big draws. The "alien of the week" of Voyager, continuing into Enterprise, was a dated model that prevented people from becoming invested. Making Season 3 about the fucking Xindi (because who gives a fuck about a random new alien) instead of the Klingons or Romulans was a misfire from a "let's draw audiences in".
Seriously, they could have skipped ahead a few years at the end of Season 2, and done the Romulan War. But they decided to do yet another alien of the week (well the Season). I actually really like Season 3 but objectively, bad risk when your show is on the line.
The decline and end of Berman era has been discussed for nearly 20 years and is very, very complicated because it's only partially about Trek, and actually a lot about the change of how media was consumed, media company consolidation, production costs, internal company politics, technological advancement, advertising changes and so much more.
Could anyone have done better? I think that's hard to say. I think there are certainly things Berman and co fucked up that would have maybe bought them a bit more time. But I think no matter what they did, Les Moonves was lurking out there ready to kill Star Trek because he was a microdicked boomer who thought it was nerd shit. If Enterprise earned a Season 5, i doubt it would have been given a 6 simply because of that.
If anything, Berman's era, which is far more successful than unsuccessful and made Trek as most of us know it, is a textbook case of why its important for executive producers to know when they had their time and maybe its time to give someone else a shot because they're creatively exhausted. Why didn't Berman do this? My theory for 15 years has been he thought he and he alone could protect Star Trek and Gene's legacy (which he did zealously) from Paramount/CBS by utilizing his personal relationship with senior executive at CBS. That executive left as Moonves ascended. And sure enough, Star Trek and Berman were finished within 2 years.
