Identifying intra-dimensional members relationship and reducing cube sparsity in Analysis Services 2005 SP2 : Cube design tip

In my previous blog, I discussed the approach for "Handling inter-dimensional members dependency and reducing cube sparsity using reference dimensions in Analysis Services 2005 SP2 : Cube design tip". [Watch for the difference, Inter and Intra] The approach discuss the situation where members from one dimension are valid for a few members on other dimension. The typical example would be that for Sales Accounts, normally,only the sales department would be applicable and Sales data wont exists for say, "vehicle expenses department".

But what if there exists a relation between the members on a given "single" dimension? During the cube modeling phase, many a times designers ignore this aspect and later, many of the needed business reports can not be generated by the MDX reporting clients and complex MDX queries need to be hand coded.