SQL Server Performance

Strange behaviour of time dimension

Discussion in 'SQL Server 2005 Analysis Services' started by ritika.thakkar, Mar 1, 2007.

  1. ritika.thakkar New Member


    I have a time dimension which is connected to a sales fact.
    The dimension has an attribute year, having 2 values curently, 2005 and 2006.
    There is also a time hierarchy which is : year-week ending-date

    The problem is as follows:

    when i select years on the row axis and sales value on the columns, i get correctly aggregated values for both years, 2005 and 2006.
    however if i deselect the year 2005, and select only 2006, i do not get any values.

    a Similiar behaviour occurs if they year dimension is in the filter.

    however, if i select the time hierarchy in the filter or on the row axis and select only year 2006, it gives me correct values.

    Why is this strange behaviour occurring? anyone faced a similiar situation, please help.

    Thanks in advance!!
  2. ranjitjain New Member

    Can you post the actual MDX query you are firing, one which is giving right result and those one as well which you think executing weirdly.
  3. ritika.thakkar New Member

    This is the MDX query i am firing:

    select [Measures].[Sales Value Base] on columns,
    [Merchandise].[Reporting Dept].members on rows
    from [IRS Cube]
    where [Time].[Year].&[2006]

    it gives me correct results when i use it in SQl Server Management Studio.

    However, when i try the same in Analysis Services through the browser tab in the cube, i get no values.
  4. ranjitjain New Member

    Try this:
    Open the cube in Browser mode
    Put [Merchandise] dimension on the area where it says "Drop Row fields here"
    After this put your Measure on the area where it's written as "Drop Totals"
    And finally on the top of browser where you can select dimension and filter expression,
    Select Time dimension and year hierarchy, and finally in filter expression select the member 2006 and check the result.

    You can even check the actual queries which AS is building in background to build Pivot query by running Analysis server Profiler and capture the events 1>Query Begin 2>Query End.

    I'm sure you will get the same kind of MDX query which you had fired in SSMS and same result.
  5. ritika.thakkar New Member

    i have tried this ranjit, but it still doesnt work :-(

Share This Page