• Because of the (correct) design of your dimension what you are seeing is distinct composite key selections. The easiest way to get around this is to introduce new attributes in your dimension for things like company and team that are not part of a composite key. An example can be found in the Date dimension of the AdventureWorks cube (http://msftdbprodsamples.codeplex.com/releases/view/55330). Here you will see that in the hierarchies you have attributes called "Month"; these are not actually "Jan", "Feb", Mar" etc. they are "Jan 2013", "Feb 2013", "Mar 2013" etc. There is a separate attribute for "Month of Year" which is the distinct month names without year. The hierarchical Month attribute has a composite key so that it always belongs to a year whereas the Month of Year attribute only has itself as a key, therefore when processing occurs (SELECT DISTINCT x FROM x) it only returns the 12 month values.

    In short, to avoid confusion, put your hierarchical attributes in a hierarchy and disable normal browsing of them as this may confuse users. Have separate attributes outside of hierarchies if a distinct non-hierarchical value is what you need. You can always cross join these later.


    I'm on LinkedIn