I want a show like that too, but there's no evidence that the writers/producers of Stargate are capable of delivering it. They never have, so why should they magically be able to do it now? Either they need to be replaced with a whole new crew or Stargate needs to return to the tired old formula that neither you nor I want to see, but will garner enough ratings for survival. Looking at Skiffy's overall lineup, they do fairly well with other brainless fluff shows and Caprica's cancellation shows the danger of attempting a tricky and difficult premise.
Really, what I want is a space opera show that does what The Walking Dead is doing - just look at that show for an example of how to write characters and execute a premise. Make me interested. Make me care. It's funny how some shows make it look so easy and others don't have the slightest idea.
To be really precise, a good story is about giving us at least one character we identify with and care about; getting that character into deep trouble; and letting us root for him/her to get out of that trouble. A show like that might make you think or dream, but it's not necessary.