4

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.

Units by Time and Batch

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.

enter image description here

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)

enter image description here

7

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

  1. Use IgnoreUnrelatedDimensions
  2. 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.

enter image description here

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

enter image description here

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

enter image description here

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.

enter image description here

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

enter image description 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

enter image description here

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.

  • Setting IgnoreUnrelatedDimensions to False does eliminate the batch members with no Units, but it has a strange side-effect. It isn't however due to DefaultMember or calculated measures .... all price measures now show Null values, even against the Product dimension (that they have a direct relationship to); strangely however Excel's totals of the price measures DO have values (not sure how a total of a min/max is done?). See the screenshot I have added as an edit to the Q above). – Laurence Jan 27 '16 at 14:58
  • @Laurence ah I see, the price measures appear not to be linked to the time dimension, which is why you don't see them in the columns – Tom V Jan 27 '16 at 15:06
  • yes, the only relationship they have is to the Product dimension. So am I right in thinking that the setting change means that the price measures now must have a relationship to all used dimensions in order to be displayed (and I see totals because totals are for no particular i.e. all time dimension members)? – Laurence Jan 27 '16 at 15:08
  • @Laurence which is why you get nulls in the columns where you put them against the time dimension and the totals are right because those are across the products so yes. If you put the property back to true and created this pivot table you would have seen the same value in every month. Just the problem you described in your question but with another dimension, go ahead and put it back the way it was and create this pivot table – Tom V Jan 27 '16 at 15:09
  • 1
    On balance I think I will settle for the IgnoreUnrelatedDimensions = False solution. It is better to show no data (for dimension combinations that don't allow it) than to show values that could be misinterpreted. Plus there is all the work and maintenance involved in setting up calculated measures and deciding what other measure my price should be dependant on. Thanks so much @TomV for the extensive explanations and sticking with me as the question got more complex. My understanding of SSAS and MD went up a notch! – Laurence Jan 27 '16 at 16:19
2

Add this calculated measure to the cube:

Create Member CurrentCube.[Measures].[Max Current CP With Units] as 
IIf(Not IsEmpty([Measures].[Units]),[Measures].[Max Current CP],Null);
  • Using this measure does eliminate the batch members with no Units, thanks. I have a new problem however (not caused by this, but only just noticed it) ... I thought the prices would be the min/max of the prices belonging to the products related to the selected stock-take facts. However they are the min/max of the entire set of prices (i.e. always show the same values, regardless of Time filters). Both measure groups relate to the Products dimension, so I am confused. Any way to make this work? – Laurence Jan 26 '16 at 17:21
  • Could you write a SQL query to join the tables and get the prices you want? If you can provide that we can discuss how to reorganize the cube to achieve that need. Possibly a m2m dimension with a bridge table? – GregGalloway Jan 26 '16 at 17:28
  • @Laurence Avoid the MDX solution if you can, it's hard to maintain and can lead to slow performance – Tom V Jan 27 '16 at 9:18
  • 1
    @TomV let's see where we end up before we say to avoid MDX. The MDX above will perform great. But I agree you may be right by the time we are done with the additional changes. – GregGalloway Jan 27 '16 at 11:50
  • @GregGalloway will it still perform great if you have to do this for all your measures in that measure group, year-to-date over those calculated measures etc? – Tom V Jan 27 '16 at 12:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.