Post reply

SSAS 2005 MDX expression for a KPI question

  • May 20, 2009 at 12:29 pm

    #205147

    I am a newbie to SSAS and trying to construct a KPI in my cube.

    I am receiving an error when browsing the KPI - "Infinite recursion detected. The loop of dependencies is: Client Related Time Value -> Client Related Time Value.

    I am assuming the issue is with my Value Expression - ([Task Data].[Task Category].&[Client Related Time] / [Task Data].[Task Category].[All Tasks])

    I am wanting to evaluate the number of hours spent for a particular task category against time spent on all tasks and derive a percentage.

    I can query this value via the following MDX query:

    WITH MEMBER [Task Data].[Task Category].[% Client Related Time] AS ([Task Data].[Task Category].&[Client Related Time] / [Task Data].[Task Category].[All Tasks])

    SELECT [Task Data].[Task Category].[% Client Related Time] on 0

    FROM [Time Tracker][

    I can also browse the cube and display this value across all Task Categories.

    Why does the MDX expression ([Task Data].[Task Category].&[Client Related Time] / [Task Data].[Task Category].[All Tasks])

    give an error in the KPI?

    Thanks.

    Robb

  • May 20, 2009 at 2:25 pm

    #997894

    try

    ([Task Data].[Task Category].&[Client Related Time] / (sum([Task Data].[Task Category].[All])))

  • May 20, 2009 at 7:17 pm

    #998024

    Jason-

    No change. Still getting the infinite recursion error.

    Robb

  • May 21, 2009 at 6:18 am

    #998186

    Robb,

    try and use your measure explicitly in the numerator and denominator... and try dimension parent in the denominator or root. ie:

    ([Task Data].[Task Category].&[Client Related Time],[measures].[value])/ ([Task Data].[Task Category].parent, [measures].[value])

    ([Task Data].[Task Category].&[Client Related Time],[measures].[value])/ (root([Task Data].[Task Category]), [measures].[value])

  • May 21, 2009 at 6:41 am

    #998205

    I updated the KPI with the following expression-

    ([Task Data].[Task Category].&[Client Related Time], [Measures].[Time Hours]) / ([Task Data].[Task Category].Parent, [Measures].[Time Hours])

    Now I am getting a 1.#INF which tells me it is a division by 0 issue. Which makes sense since not all associates will have time in this category but the base value expression does not have any filter on it?

    Also when I apply dimension filters to narrow the focus to view an associate that does have time in this category, the value does not change from 1.#INF.

  • May 21, 2009 at 6:55 am

    #998218

    that is strange... parent should be an aggregated value.

    if you create a calculated member of the parent portion and browse, what does it return?

  • May 21, 2009 at 7:01 am

    #998221

    I removed the .Parent from the expression and it works now.

    I am assuming that adding the [Measures].[Time Hours] gives the expression the "source" it needs for the given context, i.e. &[Client Related Time]?

    Robb

Viewing 7 posts - 1 through 6 (of 6 total)

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