I've restarted Dragon Age, and right off the bat I have to give props: there's one big difference between the Warrior/Rogue start from the Mage start, enough so that it caught be my surprise even though it made perfect sense in retrospect. So far that's already introduced a few new wrinkles into things. For context, my initial playthrough was as a male rogue without the bonus content (for reasons of connectivity). I've started a female warrior and male mage and have been swapping between them, though the mage is looking to be the one I play more of simply because I'm super curious about how elements of the game play out if you're playing a mage.
The reason I'm so curious to see the game as a mage is that one of the big recurring points of tension in the game is between the templars and the mages. For those unfamiliar with the setting, Dragon Age mages are pretty powerful, enough so to be dangerous, and their power also makes them vulnerable to demonic possession (something which can be made more likely through the use of blood magic, which is high power/high risk). To keep them in check (and because the church dictates that mages should not rule over men), mages are cloistered in "circles", isolated places that are part school, part prison. These circles are overseen by templars, whose jobs include hunting down rogue mages ("apostates") and killing blood mages, as well as being wardens of the circle.
This is a great conflict because it's wonderfully mixed. The mages are unfairly oppressed, but at the same time, the danger they represent is very real, and more, it's a danger that there are few (if any) protections against. At the same time, while some templars are sympathetic to the mage's plight or are genuine defenders, some of them are exactly the kind of bully boy nutjobs you'd expect in this kind of gig. One of the things the game keeps coming back to is that the current solution stinks, but no one has a better idea (and, in fact, you get to see the tragic end of many attempts to find a better idea).
As a final note, there are factors in play in Kirkwall (the city the game takes place in) which have escalated the usual tensions in a circle to an extreme, and they're all nicely human ones, usually the reasonable result of one thing leading to another.
The end result is an unsolvable problem with immediate consequences and personal elements. That's a beautiful combination, and one that you GMs out there might seek to emulate. Historically, we try to steer away from unsolvable problems for fear of disempowering players, but that concern is (paradoxically) diminished by making the problem big enough and putting it smack dab in the middle of things. Life is full of these problems (usually as a result of a difference between what people deserve and what can be done) and it feels false if your game does not.
But simply having these as big abstract problems is rarely very satisfying, either to a narrative or a game. What makes it work in DA2, and will hopefully work in your game, is that it's a constant source of new (addressable) problems. Not only are these problems of immediate concern, each of them puts the (big, abstract) conflict in a personal, actionable, and usually choice-demanding way. When a mage runs away from the circle and you find him, you are making a real choice when you decide to return him or let him go. It matters to that mage and to other people involved, but it also says something about the bigger problem.
To round it out, the tensions that drive the problem come from people, specific people who you can talk and work with. In the case of Dragon Age 2, there are numerous templars and mages who all have strong views and actions to take that contribute to the bigger picture. No one person (even those in charge of the factions) can truly steer the iceberg on their own, but they all contribute  . These are the people you interact with for your specific, tactical elements of play (adventures), but because they all have a position relative to this one big thing, you can feel the context of your actions.
Now, obviously, I think this is a page worth stealing from DA2's playbook for your own game, so let me add one last twist: this is not the only such problem in Dragon Age 2. In fact, early on in the game it's greatly overshadowed by another, similarly unsolvable problem in the form of a small army of Qunari that have set up camp in the city. But when that problem comes to a head, it doesn't magically solve all the other problems in the city. In fact, some things get worse, since one problem often keeps another problem in check.
For my two bits, this kind of tension, escalation and context have a lot to do with why Kirkwall feels more alive than many other fantasy cities I've played around in, and while you might not be able to bring the wonderful art or voice acting to your table, there's nothing stopping you from taking the ideas and running with them. Find a problem that's too big to solve, have it create specific, actionable consequences that reflect on the big problem, and have characters in the setting who reflect and shape the problem to give it a face. Then do it one to three more times, and see what you've made. If nothing else, you will probably find you've created and adventure generating engine, and that's no small thing.
(back) 1 - One other very human thing - any given individuals ability to make things worse is usually far greater than their ability to make things better.