If one so wanted, one could divide fiction into a spectrum that goes from "straight-forward" to "obscurant". A straight-forward story is one which carries everything that it containts on an open sleeve, it doesn't hide anything from the reader and slowly but surely reveals all of its secrets. They are stories that don't leave much to the imagination, they have a theme, or idea, or message they want to tell and they are making sure that it is getting to you. On the other hand, obscurant pieces of fiction often only hint at certain ideas, leave things ambigious and open to interpretation. Their goal is to build a story together with the reader: The story is as much where your imagination took it to as the actual text itself.
A story being "obscurant" or "straight-forward" says nothing about its complexity or depth. You can have an obscurant story with heaps of depth and complexity, or an obscurant story that is fairly simple. Many poems or love songs only hint at their plot through beautiful and engaging wording and articulation, but said plot can often be fairly simple. Likewise, everyone knows about some obscurant stories with incredible depth, such as Franz Kafka's works which can at times have 5 to 10 different interpretations of the text which are all simultaneously considered valid.
Both kinds of stories have their merit and place to be, and both are enjoyable. But personally, I have always enjoyed straight-forward stories more. Because in many ways, when I am enjoying fiction, I am not interested in my own opinion, or what emotions it causes within me, or what thoughts I am having when reading or consuming said fiction. Reading fiction is for me an exercise in relating to humanity. Any piece of fiction has the worldview, experiences, context, likes and dislikes of their creator poured into it and by reading it, it's as if I can partake in their world and their ideas, and if I find myself within them, if I can understand where they are coming from, it makes me feel connected to them.
Fate/Stay Night is an incredibly straight-forward story. Its stakes are clearly defined, its characters are thoroughly explored, the rules of its world are explained in excessive detail, and its messages and themes are clear for all to see. While this does cause a rather slow pacing, extremely long info-dumps and to some extent repeated content, it also allows FSN to be one of the most thorough and systematic pieces of fiction that have ever come out of otaku media.
FSN is a central piece of otaku culture and as such is fully rooted in it. Most anime fans have started getting into this culture through simple shounen anime stories such as Dragon Ball, Naruto or One Piece, thus we are all familiar with certain youngsters with interesting and cool abilities fighting for their friends/the world, overcoming difficult challenges, growing as a result and philosophical or thematic opposition being resolved through battles, where usually the winning party didn't just win because they were simply stronger, but because their superior philosophy allowed them to win. What FSN does is not necessarily "deconstruct" the shounen we grew up with and that marked us, but it takes these themes and tropes and takes them as seriously as possible, explores them thoroughly and builds an extremely rich world where these themes have their perfect home.
The central theme of FSN, as anyone should know by now, is heroism. But what sets FSN apart is that while it explores the theme of heroism - as explained - in a very straight-forward and obvious manner, it does it in such a methodical and thorough way that at the end of it, you feel like you have learned everything that there is to learn about the trope, have experienced all iterations of it, all of its consequences, its beauty, its ugliness, its desirability or undesirability. FSN is as close as you can get to a full discussion of any theme or trope . At the end of it all or on a reread, you can see all the different pieces fall together to create a coherent network of interconnected symbols, themes, the chechov's guns, world-building and systems whose sole purpose is to flesh out the theme of heroism.
FSN achieves this by structuring the whole plot around its protagonist. It is no exaggeration to say for me that I have never seen any character in fiction that is so thoroughly explored and analyzed as Emiya Shirou. While in some ways he is a "simple person" - it would be impossible to fully explore any sufficiently realistic person - every detail of his story, his relationships, his abilities, his personality, his social status were clearly chosen with purpose in mind, not only for Shirou to serve as the perfect vehicle to explore the heroic ideal, but also to allow each of the three routes to take his character in three very different directions and explore everything that this character could possibly offer. Each route of course discusses a different aspect of the ideal of heroism, and in each route it is Shirou's character that goes in a different direction in order to do so.
And it is in this way that FSN is extremely straight-forward. By the end of it, there is nothing about Emiya Shirou that we do not know or do not understand, there is nothing left to the imagination about this character, his strengths, his weaknesses, goals (or lack thereof) or how he would act in each situation, yet it is precisely this what makes FSN unique and in my opinion a masterpiece of otaku culture next to the likes of Steins;Gate - by the end of it we have experienced a complete discussion. We were given a theme or idea, the ideal of heroism and every aspect of it was explored, everything ended up fitting together, but we were given it in a familiar and entertaining package of thrilling action, fascinating abilities, hotblooded characters and hype moments. It is fun and entertaining, but never ignores or forgets its own themes or its own depth.
FSN is a visual novel where one can fully get lost in, and at the end come out of as a richer person. There is nothing quite like it.