Cube with 2 measure groups (with different relationships to dimensions) returns too many dimension members in reports

I have a cube that contains stock-take data from retail operations. It has 2 measure groups - one containing the number of stock units counted in each stock-take batch (which is related to the Product, Batch and Time dimensions), and another containing product prices (which is just related to the Product dimension).

The measures in the prices measure group use the Min and Max operators (i.e. they show the minimum or maximum price across the selected members of the Products dimension).

I seem to have configured the cube and dimensions correctly (the price measures display as expected) ... except in the following case:

If I query the cube in Excel and get Units by Time and Batch, I see the expected result (see image below, filtered on Time=2015, and hiding rows with no data), i.e. just the batches with Units fact data for the selected Time dimension members.

If I now add one of the price measures (without changing any filters), the Units total is unchanged (as expected) but I now see every member in the Batch dimension (see image below). This is a problem for end-users, because all they want is to see the prices for the product in the batches previously selected.

From a technical point of view, it kind of makes sense - the price facts have no relationship to Time, so cannot be filtered by my Time filter the way the Units were.

Is there anything I can do (in the cube design or Excel) to eliminate the Batch members that have no Units for the given filters? Note: I can't get the users to write MDX queries - report building needs to remain "point and click"!

EDIT 27/1 (adding extra screenshot to illustrate strange side-effect of setting Ignore Unrelated Dimensions = False per TomV's answer)

As you correctly noted this is what happens when you try to display measures across a dimension to which they don't have a relation.

You basically have 2 options

• Use IgnoreUnrelatedDimensions

• Use an MDX solution

I would suggest you try the IgnoreUnRelatedDimensions first, as the measures would be aggregated better, and NonEmptyCrossJoins would be able to benefit from the Bitmap Indexes used when Attribute Relationships are defined.

The MDX solution is more a last resort because of possible performance issues, and you would also need to define a calculated measure for each measure you have. The MDX solution would most likely be either complex or mess up your totals/show incorrect data.

IgnoreUnrelatedDimensions

When looking at the Adventureworks 2012 cube you have this situation where Employees are not linked to the 'Internet sales' but they are linked to the 'Reseller sales'.

When browsing the cube the result is the same as yours when you add measures from both measure groups across the employee dimension.

You can solve this by setting the IgnoreUnrelatedDimensions property to False on the Measure Group.

After reprocessing the cube and refreshing the browser the result now looks like this:

There are a few caveats with this though, such as an issue with setting DefaultMember

If the DefaultMember is not the All level on one of the attributes of the unrelated dimensions you don't get aggregated data since the default member still acts as a filter even if it's not included in your pivot table:

For example after setting the DefaultMemberproperty on the gender attribute of the employee dimension like this.

This is the result when no slicers are included in the report. Of course, you would want to see the total here:

I seem to recall some other edge cases with calculated measures but I don't remember the specific situation.

MDX Solution

If you run into any of the edge cases you would have to resort to an MDX solution such posted by @GregGalloway, but that could end up being tedious and very specific to a single report or use case:

CREATE MEMBER CURRENTCUBE .[Measures].[Max Current CP With Units] AS
  IIf (
    NOT IsEmpty ( [Measures].[Units] ),
    [Measures].[Max Current CP],
    NULL
  ) ;

The drawback of that solution would be that you would be messing up the way totals look and possibly present incorrect data but that might be acceptable in your specific case of price information with MIN/MAX aggregation but would totally break for sums, and will probably show false data to your users.

For example when running this query

WITH MEMBER [Measures].[Filtered internet order count] as
'
IIf (
    NOT IsEmpty ([Measures].[Reseller Order Count] ),
    [Measures].[Internet Order Count],
    NULL)
'
SELECT {[Measures].[Filtered internet order count],[Measures].[Reseller Order Count]} ON 0,
[Employee].[Employees].allmembers ON 1
FROM [Adventure Works]

Produces this result

Which looks equally strange, since the total is the same as each individual member. But that may be acceptable in your specific case of min/max pricing, I think it's mostly showing false data as there are no internet sales for those employees.

I guess if you control the reports it would be possible. For the added pivot table in your edit I guess it would produce OK results.

StackExchange Database Administrators Q#127213, answer score: 7