THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus

Cascading many-to-many dimensions: unsupported scenario?

I already opened a bug and written on newsgroup about this specfic 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 behavious 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 factless 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?

UPDATE 2005-08-22: It will be supported, read my new post!

Published Thursday, May 12, 2005 11:33 AM by Marco Russo (SQLBI)

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS


No Comments

Leave a Comment


About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog



Privacy Statement