I think that one of the reasons the game is so good is because there's sufficient detail to impart motivation to your dwarves even where none exists. In a way, it relies on our vulnerability to superstition.
You can see this in the fan fiction. Our little dwarves don't have anything near the complexity they're ascribed, but they have sufficient complexity that it just seems reasonable enough to say, "He didn't want to do that job" rather than "That job didn't catch him when it flashed because he was too far away." In other words, it's very good at creating the illusion of AI without necessarily using complicated AI. This also frees the player from a sense of shame on failure: you didn't fail because you cooked the booze, you failed because your stupid dwarves cooked all the booze. But you still get to take credit for the things you, as a player, are proud of.
Another reason it's good is because of design philosophy, which is sort of, "Let's do this right, even though it's expensive and hard, rather than using a quick fix to make it work well enough." That's where the simulatory parts of the game enter play (I don't think it's pure simulatory sandbox, unlike Gatleos-- I find pure sandbox rather boring, and appreciate the gameplay elements of DF.) You really see this shine in the bugs, where fun, unexpected behavior crops up, but it's in all elements of play, because there's a sense throughout the game that you CAN be smarter than the developer-- that you can do things the developer never thought of, because he didn't make a walled-in world for you to play in the way he intended. Instead, he made a system, that you can play any way you can dream of.
Third, the game combines multiple goals into one game. You can play it like an RTS-- defeat the goblins! You can play it like simcity: make something beautiful and epic like a giant pyramid. You can play it like a logic puzzle, by trying to figure out how to accomplish some particular goal, like making an obsidian factory. You can play it like an RPG: level your favorite dwarf into a fearsome killing machine. (That's not even counting adventure mode.) These multiple goals work great together-- beauty isn't just based on scarcity and interesting design, but on function as well, for instance. By the time your interest in one goal is fading, your interest in another is blossoming.