Regarding the Dark Ritual, old god soul, and things of significance...
I think the Dark Ritual choice presents moral conflict with severity that is based on your own Hero's moral compass. My main Hero, a male Cousland engaged to Anora, refused the Dark Ritual for numerous reasons. He wasn't going to do it himself because he was loyal to Anora. He would not ask Loghain to do it either because he fully intended on Loghain making the final blow if Riordan could not. The old god's soul did not deserve a second chance at life anyway. Above all, he would never force an evil entity on an innocent child. None of these things would he do, even at the cost of his own life. He is a Cousland, and as his father always said, they do what must be done. No moral conflict at all.
My secondary Hero was greedy and selfish. He was a blood mage. He desired power. He had slept with Morrigan (and Leliana) many times before this. So of course he had no problem with the Dark Ritual. He wanted to live, and he needed Alistair alive to act as his puppet on Ferelden's throne. No moral conflict at all.
Compare that to some other character, like a City Elf who might not want to die, but can't let Alistair die either for fear of what Anora might do to the elves out of retribution for the City Elf killing her father in the duel. But at the same time, can he really force an evil old god soul on an innocent child? He'd be no better than the humans in that case, always controlling other people's lives. Or hey, maybe Alistair was just your friend and you didn't want to see him die. Moral conflict.
As for the Dark Ritual's significance. Of course it was significant. Aside from saving the lives of the Wardens involved with the final blow, it also presented extra storyline for Inquisition. Now the problem here, why some see this as insignificant, is because we don't see the final purpose of the old god soul. Flemeth takes it away somewhere and what happens to it after Solas shows up is unknown, but the bottom line is we still don't know what role it plays yet. I will agree that BioWare could have made this more epic by say... having Kieran transform into a dragon at Skyhold, and then the Inquisitor having to defeat him before he tears the place apart, after which Kieran would have to be taken somewhere to harness his power or else have someone else take the soul from him. But having a story that showed the power of the soul would have been better. In fact, they should have made him the dragon you call on to fight Corypheus' dragon, else Morrigan learns it from Flemeth, or takes the old god soul into herself to become the dragon. Still, I'm sure we will see how important an old god soul is in following games, even if the Dark Ritual was not performed. Remember there are still two old gods left.
As for the Dark Ritual's relevance to the Hero's survival and return to the scene, there is none. In my first example above, my Hero lives not because of the Dark Ritual, but because Loghain did the right thing in the end. So the Ritual was never the cause of the Hero surviving past the end of the game. This is how it was written by BioWare from the start. The option of having the Hero live was always part of the series. As such, doing something with the Hero is now BioWare's responsibility, and doing something GOOD with the Hero is BioWare's test. Will they pass or fail? I think their success will depend on how willing they are to listen to the fans and take our advice, and I see much good advice in every topic here that talks about bringing the Hero back.
Again, I agree it had little bearing on the storyline of Inquisition, but it still might in future installments as we learn what the old god souls are. I saw a theory on YouTube that the old god souls are keys to the eluvians, but I think that's just wrong. I think it's clear that they are just powerful spirits who are capable of wielding powerful magic. That's how Kieran activated and redirected the eluvian to the Fade. I think Flemeth was interested in the old god soul for the reason of amassing great magical power to fuel her reckoning. Maybe she plans/planned to use it to free the other elven "gods". Or maybe it would be used to restore Mythal's fragmented and weakened soul. Wish we could have seen more in Inquisition, but we didn't. I'm sure there will be more in the near future.
I don't think the Dark Ritual lessened the impact of the reveal that a Warden must die to slay an archdemon. I had suspected it for a while before that while playing anyway. Besides, my Hero had Riordan and Loghain to take the final blow before him. So he was pretty safe, and he didn't have that strong of a bond with Loghain as he would have had with Alistair. Even so, I played a Cousland, who seemed to have that "sacrifice" theme following him around since Highever. So ending up sacrificing himself to save Ferelden would have been not such a stretch. Theoretically, he would have died defending his mother and father had Duncan not been there to conscript him. So I would have seen it as him having been living on borrowed time. I think the impact would only hit hard if the Warden were a female who romanced Alistair. It would mean one of them might have to die.
I agree with Heimdall about the long term consequences being unclear. That was the major catch for most players. But it's impossible to see the consequences in the present. Whatever happens won't happen for many years. All you know is something bad might happen some time down the road, and with an old god involved, that likelihood becomes much greater.
I don't think Weeks' sunset ending applies to any Hero. Isn't every living Hero on the quest for the cure? Even those who romanced Zevran are still said to be on the quest thanks to Witch Hunt being mandatory, right? And while Zevran doesn't make an appearance, he does in DA2 and sends letters in DA:I regarding some chore table missions. In any case, the sunset ending will never apply to most Heroes. My main Hero is the King of Ferelden, so he will always be in a position of great influence and importance. And to this point, BioWare set the game up this way. If they didn't want the Hero being accounted for in each following game, then this option should not have been available. Same goes for anyone who romanced Morrigan, Leliana, or Alistair. Or in that case, don't bring any of those characters back. Let them actually ride off into the sunset with their Hero/Heroine.
At present, the Hero should have about 17 years left before the Calling. 30 years to live, you start to hear the Calling near the end. So it's at about the 27 year mark, give or take depending on your Hero. Or if your Hero drank the Power of Blood concoction, he has about what... 400 years to live? And of course when the Hero finds the cure, he or she will have their natural lifespan restored, or at least they won't die because of the taint in that 17 year range.
Basically it all boils down to this. If your Hero made the ultimate sacrifice, then your Hero is the odd one out and you probably won't get to see them again except maybe in statue form at Weissaupt. The general rule is that the Hero survived and went on to do more great things. The Orlesian Warden in Awakening is just a placeholder for your Hero whom you were supposed to keep alive. Evidenced by the fact that the Orlesian Warden gets a default world state where Alistair is the king and Morrigan treats you basically the same even though she doesn't know you at all. The US Heroes are the minority. Future Dragon Age games will continue to feature our Heroes.