Sunday, September 12, 2010
I'm reading: MDX Coding Standards for non-spaghetti codeTweet this !
MDX is one such language that is not used as heavily as SQL, but coding standards or coding guidelines are one such aspect of development that needs to be applied to any programming language. Spaghetti code is one of the worst nightmares of any developers. Some of you might not know what does the term "spaghetti code" mean, though it a very popular term in the development parlance. In simple words, spaghetti code means a code that is like a crystal maze where it's quite complex to find the other end of the exit. In such code it's hard to figure out the tail of the code or the final result of the code when you start analyzing the code. Some key rules that I have learned from my experience to avoid such code getting created in MDX are as below:
1) Comment in detail about the query: When you use dimensions and measures in your MDX query, knowing the configuration and properties of MDX is quite important. It's always beneficial, if the intention of the query is made clear with a text explanation of the query logic in comments. This much it much easier to understand the query.
2) Try to make the code / query as explicit as possible: Many times the code becomes confusing due to the laziness observed while coding. For ex. one can omit specifying the measure to be used on any axis or on the slicer, if the measure to be used it the default measure. But consider that at some point in time the default measure of the cube needs to be changed, all the queries that has not been specified explicitly, the measure to be used, those queries would break.
3) Create a list of navigation shorthands: In MDX like in T-SQL, there are more than one ways of querying the same thing. For ex one can use brackets ( ), * operator as well as crossjoin function to achieve the same result. Another ex, one can use .parent.children function on a member and the same effect can be achieved by the .siblings function. In querying a cube, MDX helps how you navigate at a particular slice of the cube. One should make a list of common navigation functions that would be required in the code, and state down the shorthands to avoid creation of any spaghetti code.
4) Use functions instead of operators: In MDX, you can use * instead of crossjoin function, - instead of except function. Using text functions makes the code more readable and easy to understand instead of using the operators.
Above suggestions are based on my personal experiences and not any universal standard for MDX coding. One learns about the ways of avoiding spaghetti code only when one gets dumped into the spaghetti created by someone else. And fortunately or unfortunately this experience has made me conclude many rules some of which are mentioned above to avoid spaghetti coding.