Esserise on the Bulbagarden forums had a really neat insight to SM's storyline.
So of course I would appreciate the decision to focus SM's storyline on the personal development of a single young girl as opposed to preventing whatever apocalyptic horrors the UBs might wreak upon the world (and even that storyline still happens, to an extent, albeit in the post-game). The way I see it, there's actually some unpredictable tension in the way that Lillie addresses and responds to her problems, and that makes it an area worth exploring. Whereas we know the world isn't going to be destroyed by the Ultra Beasts. We are invariably going to succeed in stopping them. Whatever "threat" they may be said to pose is entirely hollow from the very outset.
I think this is a good thing to keep in mind with story writing-as fun as wild, disaster-preventing stories are (and they are good to use!), a smaller story that's more about the way people interact underlying it is a good way to set your story apart and make it less predictable. (it also makes the audience more emotionally invested in characters, which can make the dramatic scenes have a stronger effect)
The plot in Gen 5 has a similar depth. At the climax, you know you're going to defeat N to become champion, and you know you're going to catch your dragon, just like all box legendaries before it. But seeing how N changes in his worldview-especially with his father-is a more unpredictable story.
I think it's worth mentioning that the legendary Pokemon involved in both of these weren't "all-powerful gods", either. They had legends and great power, but they weren't the creators or rulers of the universe's properties-they were just unique. That's not to say that a Pokemon that does use that is a bad design-I could go on and on about what I love with the legendaries we have-but if a legendary in a plot isn't clear-cut like that, it kinda forces you to be a little more creative in incorporating it into the story, rather than just being "get this Pokemon and the world is altered exactly as you want it".
Uranium also does this quite well. It has a lot of action-themed plots-the chief ranger, a nuclear terrorist, the rising Pokemon champion, and a secret international officer-but it's ultimately a story about two close families that are hurting. (and it manages the balance between the two quite well!)
Gens 3, 4, and 6 did not do this very well, IMO. They were still quite fun to adventure through, but the main conflict was essentially "this crime boss is discontent with the world, and wants to use a legendary to alter it"-so even amongst the excitement of the Distortion world, Team Flare's base, and that sick underwater cave, there wasn't much to anticipate from the story. Of course I'm going to capture the legendary and stop this crisis-that's just how this works.
I get the feeling that this sounds like I'm ragging on the storylines of most of the games, and that's not at all my intent-even when I knew the end results of their plot, the world and characters it involved were still engaging and delightful. The core part of a story isn't the only important part, after all. But I think if you want to make your story unique, it's a good idea to include a smaller-scale story-not necessarily in favor of a larger one, just in addition to.