I already opened a bug and written on newsgroup about this specific scenario, but I still haven’t received an answer. This morning I met Chris Webb in Munich (I’m attending SQL Pass Europe) and he told me that one year ago someone of MS told him that it’s an unsupported scenario. It would be good to have a comment more or less official from someone of MS, because at this time the behaviour of SSAS is very disappointing (you could design a model that really give you wrong data). The following is the description of the scenario.
I just posted a bug on this, anyway I’d like to know your opinion: is the “cascading many-to-may dimensions” a supported scenario?
Let me explain.
If I define two cascading many-to-many dimensions, the higher aggregation dimension doesn’t work well and doesn’t crossjoin measures with other dimensions.
You have a fact table (Sales) that links a Products dimension.
Each product has one or more Categories.
Each category has one or more market.
If you define a model of this, Cube wizard doesn’t model well (should it be another bug?), but if you manually define relations between dimensions and fact table (and fact-less fact tables), Analysis Services doesn’t warn you about a possibly unsupported scenario. So I think it should works, but it doesn’t.
You can dice and slice cube across Product and Category dimensions, but if you try to use Market dimension you obtain NULL measures if define a member other than All for Product and Category, or you receive the total for All Products and All Categories for each Market member, independently from the Market you choose.
I tested it on April CTP.
Who wants to comment that?