locked
KPI seems to ignore "Dimension Usage" references RRS feed

  • Question

  • Hi,

    I have the following scenario:

    2 Measures, using Ytd and Sum to get current and last year YTD values.  These are calculated by using VBA and Today() function.  These both work ok.  I decided to hide these measures and create a KPI to display these values.  I have set the "Value" Part of the KPI to the Current Year YTD measure value, and the Goal to the Last Year YTD measure value, then a case statement on the trend to display the difference as a status arrow.

    The cube deploys and processes successfully.

    If I use Excel to display these measures/values it works but with issues.  I have two dimensions, Brand and Sales Part.  If I use the Brand dimension the KPI calculates correctly, however if i add the Part Number from my Sales Part dimension it doesn't work correctly.  It displays every single member of my sales part dimension, and not the ones that are listed to the relevant brand.  Obviously just to mention that if i do the same with a regular measure it works fine.

    Also, if i have my Brand and the KPI value, goal and trend shown it also includes totally empty row values. I thought that they should be suppressed - again as a note, this is only the case when my trend arrow is shown.  Should I handle nulls here?

    I hope somebody can help me understand this puzzle!

    Thanks,

    Dom

    Thursday, June 20, 2013 9:30 AM

All replies

  • Hi Dom, 

    It seems there are some wrong relationships in the cube structure. Have you perform a test when browser the cube in SSMS? Please check the relationship under the "Dimension Usage" tab. 

    Saturday, June 22, 2013 8:10 AM
  • Hi,

    Thanks for your response.  I have double checked all my relationships and they all seem ok.  If I use a measure with the same dimensions selected everything functions correctly, its only when i use the KPI that it doesn't seem to take the relationships into account.

    Thanks,

    Dom

    Monday, June 24, 2013 9:25 AM
  • Hi,

    It appears that the issue i had was a missing relationship my DSV (thought it was there, but seems to be missing on closer inspection).  Just rebuilding in my test environment will confirm if this solves the issue or not.

    I actually spotted different functionality using SSMS  and hand writing an MDX query - excel seemed not to reflect the same results.

    Thanks


    • Edited by Dom Cotton Tuesday, June 25, 2013 3:11 PM typo
    Tuesday, June 25, 2013 3:10 PM
  • Hi Dom,

    Have you solved this issue after redesign your DSV in your environment? Please let us know how things go. If you solve this issue, please share your solution. If not, please post your detail information, so we will work together and help you to troubleshoot this issue.

    If you have any feedback on our support, please click here.

    Best Regards,


    Elvis Long
    TechNet Community Support

    Sunday, June 30, 2013 1:37 AM
  • Hi,

    Got sidetracked on something else.

    I was missing a relationship in my DSV between my fact table and my dimension for part.  However I put all of this in place and rebuilt and still am getting unexpected results.  I assume that the problem is in relationships and my DSV somewhere so will continue to work on it, and I will post my solution if i get there!

    Thanks,

    Dom

    Monday, July 1, 2013 8:32 AM
  • Hi Dom,

    Have you solved this issue? Please let us know how things go.

    Regards,


    Elvis Long
    TechNet Community Support

    Thursday, July 4, 2013 1:39 AM