none
KPI time intelligence filters RRS feed

  • Question

  • Hello all,

     

    More scorecard questions!

     

    I have a scorecard which shows actuals and targets for the year. However there are only actuals until this month, but there are targets pre-configured for the rest of the year.

     

    What I thought I would be able to do is to set the time intelligence filter to 'Year.FirstMonth : Month', but when I preview and update the scorecard it just shows #ERROR.

     

    Time intelligence appears to be working on the data source as I can set it to just 'month' or 'year' and the figures are correct.  Is this expected? Is it that I cannot use a range or set trough time intelligence when configuring KPIs and have to use MDX instead?

     

    I have enabled monitoring server logging to the event viewer and this is the error:

     

    Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException: The 'Calendar Year Month Hierarchy' hierarchy appears more than once in the tuple.

     

    As a test I added a date filter to the dashboard and used the same time intelligence filter. This is what the preview looked like:

     

    [Date].[Calendar Year Month Hierarchy].[Month].&[1]&[2008],[Date].[Calendar Year Month Hierarchy].[Month].&[2]&[2008],[Date].[Calendar Year Month Hierarchy].[Month].&[3]&[2008],[Date].[Calendar Year Month Hierarchy].[Month].&[4]&[2008],[Date].[Calendar Year Month Hierarchy].[Month].&[5]&[2008],[Date].[Calendar Year Month Hierarchy].[Month].&Devil&[2008]

     

     

    Thanks for all your help,

     

    Matt

    Thursday, June 5, 2008 10:20 AM

Answers

  • i think the problem here is the way that the time intelligence filter is applied when it is part of a metric definition.

    When you setup time intelligence as a dashboard filter, PPS creates a SET when you have a range of members. So, if you setup an entry on your filter as "Month-1:Month" it will generate the following members:

    Code Snippet

    [Date].[Calendar Year Month Hierarchy].[Month].&[5]&[2008]
    [Date].[Calendar Year Month Hierarchy].[Month].&[6]&[2008]



    Now, when you actually link up the dashboard filter to a scorecard or other element it gets passed as a set and not a tuple
    i.e.:

    Code Snippet

    { [Date].[Calendar Year Month Hierarchy].[Month].&[5]&[2008], [Date].[Calendar Year Month Hierarchy].[Month].&[6]&[2008] }



    So this works fine.


    Now back to what you want to do... from my testing i dont think it is possible to enter a range of time members when using time intelligence directly in a metric definition. From the error you have posted it appears that the mdx generated internally by your time expression is causing a problem in the WHERE clause - by generating a tuple expression like:

    Code Snippet

    WHERE
    ( [Date].[Calendar Year Month Hierarchy].[Month].&[5]&[2008], [Date].[Calendar Year Month Hierarchy].[Month].&[6]&[2008])




    What it should be doing is creating a set expression with curly braces:

    Code Snippet

    WHERE
    ( {[Date].[Calendar Year Month Hierarchy].[Month].&[5]&[2008], [Date].[Calendar Year Month Hierarchy].[Month].&[6]&[2008]} )




    Im not sure whether this is by design or is a bug/omission.

    You are correct - you can still achieve what you want by using an mdx tuple expression.. but you will probably need to create a named set that dynamically references the periods you want.

    As an example, for the last 3 months data, you can created a named set called [Last 3 Months] that always brings back the current month and previous 2. Then your metric deifinition will have an mdx tuple expression such as:

    Code Snippet

    SUM([Last 3 Months], [Measures].[whatever])





    Sunday, June 8, 2008 3:27 AM

All replies


  • Hi all,

    Apologies for asking this question again - but can anyone help?

    If I have to use MDX when creating KPI's, then so be it. But I'd just like to make 100% sure.

    I'm sure that I could use a  filter on the dashboard page, linked to the scorecard and pass a set via time intelligence to the KPI's but in a couple of instances I really rather not have drop downs; just the KPI's preset.

    Thanks again!

    Matt
    Saturday, June 7, 2008 8:59 AM
  • i think the problem here is the way that the time intelligence filter is applied when it is part of a metric definition.

    When you setup time intelligence as a dashboard filter, PPS creates a SET when you have a range of members. So, if you setup an entry on your filter as "Month-1:Month" it will generate the following members:

    Code Snippet

    [Date].[Calendar Year Month Hierarchy].[Month].&[5]&[2008]
    [Date].[Calendar Year Month Hierarchy].[Month].&[6]&[2008]



    Now, when you actually link up the dashboard filter to a scorecard or other element it gets passed as a set and not a tuple
    i.e.:

    Code Snippet

    { [Date].[Calendar Year Month Hierarchy].[Month].&[5]&[2008], [Date].[Calendar Year Month Hierarchy].[Month].&[6]&[2008] }



    So this works fine.


    Now back to what you want to do... from my testing i dont think it is possible to enter a range of time members when using time intelligence directly in a metric definition. From the error you have posted it appears that the mdx generated internally by your time expression is causing a problem in the WHERE clause - by generating a tuple expression like:

    Code Snippet

    WHERE
    ( [Date].[Calendar Year Month Hierarchy].[Month].&[5]&[2008], [Date].[Calendar Year Month Hierarchy].[Month].&[6]&[2008])




    What it should be doing is creating a set expression with curly braces:

    Code Snippet

    WHERE
    ( {[Date].[Calendar Year Month Hierarchy].[Month].&[5]&[2008], [Date].[Calendar Year Month Hierarchy].[Month].&[6]&[2008]} )




    Im not sure whether this is by design or is a bug/omission.

    You are correct - you can still achieve what you want by using an mdx tuple expression.. but you will probably need to create a named set that dynamically references the periods you want.

    As an example, for the last 3 months data, you can created a named set called [Last 3 Months] that always brings back the current month and previous 2. Then your metric deifinition will have an mdx tuple expression such as:

    Code Snippet

    SUM([Last 3 Months], [Measures].[whatever])





    Sunday, June 8, 2008 3:27 AM
  • Hey _proffy_,

    Thank you for your help and research. At least it wasn't me going crazy

    Can anyone from the PPS team comment?

    Thanks,

    Matt
    Sunday, June 8, 2008 5:38 PM
  •  

    I have this problem in ProClarity where I want to select multiple members of the hierarchy to become a calculated dimension and later to be used on the rows to sum multiple measures on the columns.  If I do not make them a set (use {}) then it errors "hierarchy appears more than once in the tuple”,   if I make’em a set then it passes the mdx test but it brings null.  I tried to define those calculated  members in the AS using set {} and (“,” or “:”) as a delimiter as suggested in some postings on the web.  Again it passed the test and created a calc. member but when I used it in ProClarity for the report I got null values.

     

    So far the only way I got them to work  is when I first defined each member as a calculated member and then create a new calculated member as a sum  of them:

     

    [CM1] + [CM2] +… etc.  Works fine but it is a lot of work.

    Thursday, August 28, 2008 7:30 PM