Worf failed as a ratings stunt, but not as an addition to the DS9 cast of characters. If nothing else, the addition of the Klingon plot threads increased the scope of the show to include the entirety of the alpha quadrant. In addition, the Klingon invasion of Cardassia set the stage for Dukat's pact with the Dominion, thus the entire shape of the later seasons is defined by the events of The Way of the Warrior.
Who knows how the Dominion War would have played out had Worf never joined the show, but it's unlikely that it would have been as epic if the Dominion had no foothold in the alpha quadrant, not to mention that some of the best plotlines of the later seasons (the Romulans joining the war, Kira going to Cardassia, etc.) would have been impossible or radically different had the Klingon involvement never occurred. So, as far as the big picture is concerned, Worf's arrival had a profound impact: it forced the writers to up the ante and increase the scale of the show.
Speaking specifically about his interactions with the other characters, Worf fit right in on DS9, perhaps a little too well in that he stood out more on TNG, where he was a bit of a rebel and a relatively complex character compared to most of the crew. On DS9, among a whole crowd of exiles, flawed characters and misfits, he often came across as a bit uptight and one-dimensional. There are still a lot of great Worf moments on DS9, though, and he actually gets to kick some ass rather than taking the fall for the threat of the week, as was often the case on TNG (The Worf Effect).
Also, if anything, Worf's character arc on DS9 is that he goes from being conflicted about his Klingon and Starfleet identites (as was often the case on TNG) to being more of a full-time Klingon who fully embraces that side of his heritage and is less inhibited because of it. He gets in touch with his "Klingon heart," "What I say now, I say as a member of the House of Martok, NOT as a starfleet officer," he blows up the Dominion shipyards to honor Jadzia, we see him more and more on the bridge of the Rotarrin (sp?) in full Klingon garb, he leaves starfleet at the end of the show, etc.
Overall, I think Worf's best material is split pretty evenly between TNG and DS9, though the Klingon honor spiel was a lot fresher when it was first explored on TNG. Ron Moore doesn't really get around to deconstructing it until Tacking into the Wind, and I do think that it would have been potentially effective to take that more cynical angle on Klingon culture earlier on.
Also, no Worf = no Martok as a major recurring character (probably), so that's another factor to consider, as Martok is awesome.