Post reply

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

  • January 25, 2016 at 8:00 am

    #150962

    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 dimension 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"!

  • January 25, 2016 at 4:35 pm

    #1854331

    What happens if you remove the Units column? Does it still return all the batches? Also, is this MD or Tabular?

  • January 26, 2016 at 2:24 am

    #1854374

    Yes, I get the batch dimension members if I remove Units.

    The cube is MD, not tabular.

  • January 26, 2016 at 8:41 am

    #1854455

    The issue has to do with your MIN/MAX formulas. They are providing the value at that intersection of dimensions. I had this issue recently in tabular but can't remember right now what the solution is in MDX. I'm looking into something similar today and if I come across anything, I'll let you know. I haven't used MDX for about 3 years until the last 1.5 months so I'm a little rusty right now. If you can post your formula it may be helpful.

  • February 4, 2016 at 9:57 am

    #1856601

    For the record, I resolved the issue and the answer can be found here: http://dba.stackexchange.com/questions/127213/cube-with-2-measure-groups-with-different-relationships-to-dimensions-returns

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply