I think you might be misunderstanding what Marmell is talking about. As I read it, the issue with "mystery" isn't really mystery about the opposing force's capabilities, but rather mystery about the PCs' capabilities.
Overall, I agree with Marmell that a lot of what people want from games can be problematic to put in and still keep it a good game. I can think of more, even broader examples:
Risk to characters: One thing people seem to want is long-term risk to characters: that is, a character can be permanently injured, lose a key item, get a permanent debuff, and so on., and it's not easy to just resurrect yourself or something. This can facilitate themes like self-sacrifice, heroism, and so on.
The problem is: Let's say a character makes a heroic move, and saves his friend but hurts himself to the point where he will never be able to adventure again. What do you do? Do you make the player sit out the rest of the campaign? If so, that's not very fun. Do you let the player make a new character? If so, then there wasn't really any element of "self-sacrifice" involved, because all that happened was that the player got a free character respec.
On a blog post complaining about the lack of risk in D+D 4e I remember seeing a comment like "What's next? There will be respawn points outside every dungeon so that nobody ever has to be dead for more than a few minutes?" This was intended to be an absurd logical extreme, but D+D has had "respawn points" for a long time: it's called "creating a new character and rejoining the party when they leave the dungeon."
Player and character knowledge:
Most of us would probably agree that part of the reason we play RPGs is to do stuff that isn't practical to do in real life. For example, we can't actually fight orcs or cast magic spells in real life, but we can in a game.
One complaint players sometimes have about some games is that the system is too abstract and forces "thinking about the mechanics" rather than "thinking about the game world." This article on "near vs. far thinking" gives an explanation of this distinction:
Tales of the Rambling Bumblers » Blog Archive » Near vs. Far Thinking in RPGs
Of course, the problem is that the more "near thinking" you require, the harder it is to play a character who can do things that you can't do in real life. For example, if the DM actually required players to describe the physical process of picking a lock, say, then it would be impossible to play a thief unless you actually knew how to pick locks in real life. And if you had to give detailed descriptions of, say, martial arts maneuvers in order to use them, then you would have to know a lot about real-life martial arts in order to play an effective martial artist.
Another way of looking at this is the following: Let's say the experience I want to get out of the game is "playing a super-smart engineer who can MacGyver up any gadget he wants on the fly." If all that equates to in game terms is optimizing my character's "engineer" skill and then rolling it every time, then it's not necessarily significantly different in play than any other class that tries to optimize a skill and then roll it when necessary, so it's not likely to provide a satisfying experience of "playing an engineer." On the other hand, if I actually have to describe exactly what components I'm using and how I am combining them to design my gadget, then it's going to be hard for me to be effective unless I actually am a super-smart engineer who can design complicated gadgets on the fly, in which case I wouldn't need to play the game to have that experience. So you probably want something that is in the middle.
The way that a lot of games seem to try to handle this problem is to effectively redesign the whole relevant activity to be something that the players can understand. Combat is a good instance. Take 4e for example. Things like encounter powers, hit points, healing surges, and the action economy have little if any relationship to the kinds of things that people trained in actual fighting think about in a real fight. But in order to have a game that people can play without being trained in actual fighting, you have to get rid of anything that requires knowledge of actual fighting. And then in order to make the game interesting, you have to add the tactical depth back in through the use of artificial concepts.
Another alternative that some games use is to base the system off of real life, and then to use the rules to effectively teach the players the relevant information. So you might have a game where all the martial arts maneuvers are based off of real life, and then the game tries to teach you enough about martial arts to allow you to make informed decisions about strategy. The main complication of this is that real life can be extremely complicated to model accurately (see, for instance, this article: Realistic Firearms in RPGs)
Recreating a Specific Story:
The quote above about "playing out the plot of Lord of the Rings" reminds me of a quote from a blog post (I forget where): "If the goal is to recreate Lord of the Rings, there's already an easy way to do that: bring a copy of the Lord of the Rings books, assign each player a character, and have everyone read their character's dialogue." The point is that the whole point of role-playing games is that you can make decisions and have random events which can take the story in unexpected directions, but if you do that then you're no longer "recreating the story". So again probably what you are actually looking for is something in the middle.
