SQL Server Performance

Bad performance query time using NON_EMPTY_BEHAVIOR and named set can it be improved?

Discussion in 'SQL Server 2005 Analysis Services' started by bpimenta, Mar 25, 2009.

  1. bpimenta New Member

    Hello,

    We have an evaluation system based on processes, competences and grades. A process contains competences that

    have grades where as an
    evaluatee has a grade on a competence for a certain process.

    The main objective is to create a matrix with process, competence and evaluatee with the final grade.

    There is a grade for the evaluator analysis, one for the auto-analysis and one for the final process.

    Here are the created members in use:


    CREATE MEMBER CURRENTCUBE.[MEASURES].[SUM: Process Competence grade - Evaluated]
    AS (Aggregate (
    CoreC_Evaluated,
    [Measures].[SUM_GRADE]
    )),
    VISIBLE = 1;

    (Works well)


    CREATE MEMBER CURRENTCUBE.[MEASURES].[COUNT ROWS: Process Competence grade - Evaluated]
    AS Aggregate (
    CoreC_Evaluated,
    [MEASURES].[FCT EVALUATION RESULTS Count]
    ),
    VISIBLE = 1;

    (Works well)

    CREATE MEMBER CURRENTCUBE.[MEASURES].[Process Competence Grade - Evaluated]
    AS iif (IsEmpty([SUM: Process Competence grade - evaluated] / [COUNT ROWS: Process Competence grade -

    evaluated]), null,
    ([SUM: Process Competence grade - evaluated] / [COUNT ROWS: Process Competence grade - evaluated])),
    FORMAT_STRING = "#",
    NON_EMPTY_BEHAVIOR = { (CoreC_Evaluated,[MEASURES].[FCT EVALUATION RESULTS Count]) },
    VISIBLE = 1;

    (Takes forever)


    The competences have been arranged in areas but, some have no specific area so they have been arranged in a

    "N/A".
    Usind a named set (CoreC_Evaluated) we where able to avoid it being used.
    The problem starts when we try to use the "NON_EMPTY_BEHAVIOR" to speed up.

    We try to use the Measure Sum_Grade in the "NON_EMPTY_BEHAVIOR" clause. Using this the response time improves

    but
    the previous line that was limited by the named_set appears. When we use another expression the response time

    stays the same.

    We hope we are making any sense, so if any explenation is needed feel free to ask along.

    Thanks in advance for any feed back,

Share This Page