Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Help with MDX Time dimension

Unanswered Help with MDX Time dimension

  • Sunday, January 20, 2013 11:44 PM
     
      Has Code
    Sign In to Vote
    0

    Hi everyone

    I have a datetime dimension (Dim_DateTime) and in there I have a hierarchy (Calendar) definded something like this

    Dim_DateTime

        Year

        MonthofYear

        dayofMonth

        HourofDay

        DatetimeId

    I am trying to write a very simple MDX where I want to aggregate a measure from 6/1/2010 till 1/12012 and I want to use the aggregations done at the different levels of hiarchy. I want to develop the following MDX to give me only the aggreagations for that time period defined. May be add them in teh where clause or somethnglike that.  How can I use the Calendar hierarchy, I am not using it in the following MDX, but how can I use it.

    Select  
 [Measures].[Value Actual] ON COLUMNS ,
 { NonEmpty([Dim_DateTime].[Date Time Id].[Date Time Id])} on rows 
FROM [Energy Aggregator]
 where [Dim_Item].[Item Id].[Item Id].&[63323]

       

        

     

All Replies

  • Monday, January 21, 2013 4:08 AM
     
     
    Sign In to Vote
    0

    is this what you want ?!

    select [Measures].[Value Actual] on 0,
    {[Dim_DateTime].[Calendar].[YEAR].[2010].[1].[6]:[Dim_DateTime].[Calendar].[YEAR].[2012].[1].[1]} on 1
    from [TC Cube]

     
  • Monday, January 21, 2013 8:18 AM
     
     
    Sign In to Vote
    0

    Thanks much for your help on this

    Yes something along those lines. I executed your query but it didnt return anything.  and the Cube has data.

    I tried doing something like that but the problem is that its only pulling data for one year, I need 2010 till 2012

    NonEmpty

    ( [Dim_DateTime].[Calender].[year].&[2010] * [Dim_DateTime].[MonthofYear].members

    Would really appreciate help on this.

    Thanks

    -Sarah

     
  • Monday, January 21, 2013 8:25 AM
     
      Has Code
    Sign In to Vote
    0

    Try this:

    NonEmpty

( {[Dim_DateTime].[Calender].[year].&[2010]:[Dim_DateTime].[Calender].[year].&[2012]}) * [Dim_DateTime].[MonthofYear].members

    Please vote as helpful or mark as answer, if it helps
    Cheers, Raunak | t: @raunakjhawar | My Blog

     
  • Monday, January 21, 2013 8:45 AM
     
     
    Sign In to Vote
    0

    Hi Sarah ,

    I was trying Butmah solution and it worked fine :)  Did you use NonEmpty ? or the below ?

    select [Measures].[Value Actual] on 0,
    { "MyFirstMemberFromDate_Hier"  :   "MySecondMemberFromDate_Hier" } on 1
    from [TC Cube]

    Regards, David .

     
  • Monday, January 21, 2013 10:05 AM
     
     
    Sign In to Vote
    0

    Thanks much for your help.

    Yes I did put it that way without the nonempty. But no results. One question I have is, why do we need to check things at the year level?

    {[Dim_DateTime].[Calendar].[YEAR].[2010].[1].[6]:[Dim_DateTime].[Calendar].[YEAR].[2012].[1].[1]} on 1

    Why can I not use Month, or day?

    Thanks again, look forwardto your help..

     
  • Monday, January 21, 2013 11:50 AM
     
     
    Sign In to Vote
    0

    Check thing at the year level ? I was using the day level accrossing two years ..

    {[Dim Date].[Date_Hir].[Day].&[20111221]:[Dim Date].[Date_Hir].[Day].&[20120115]} on 1

    Regards, David .

     
  • Monday, January 21, 2013 12:47 PM
     
     
    Sign In to Vote
    0
    In your case, you can discard [YEAR] .... something like [Dim_DateTime].[Calendar].[2010].[1].[6] is enough. but if you have something higher than [Year] in your [Calendar] hierarchy, then you need to include [YEAR].
     
  • Tuesday, January 22, 2013 3:37 AM
    Moderator
     
     
    Sign In to Vote
    0

    Thanks much for your help.

    Yes I did put it that way without the nonempty. But no results. One question I have is, why do we need to check things at the year level?

    {[Dim_DateTime].[Calendar].[YEAR].[2010].[1].[6]:[Dim_DateTime].[Calendar].[YEAR].[2012].[1].[1]} on 1

    Why can I not use Month, or day?

    Thanks again, look forwardto your help..

    Hi Sarah2005,

    In Analysis Services, the colon operator allows you to use the natural order of members to create a set. We can use it at any level. Please refer to the following Adventure works samples:

    Month level:

    SELECT {[Date].[Calendar].[Month].&[2001]&[7]:[Date].[Calendar].[Month].&[2002]&[8]} ON COLUMNS,

    [Measures].[Reseller Sales Amount] ON ROWS

    FROM [Adventure Works]

    WHERE {[Geography].[Country].&[United States]}

    Day level:

    SELECT {[Date].[Calendar].[Date].&[20010701]:[Date].[Calendar].[Date].&[20010904]} ON COLUMNS,

    [Measures].[Reseller Sales Amount] ON ROWS

    FROM [Adventure Works]

    WHERE {[Geography].[Country].&[United States]}

    Please let us know if you have any more questions.

    Regards, 
     

    Elvis Long
    TechNet Community Support

     
  • Wednesday, January 23, 2013 12:38 AM
     
     
    Sign In to Vote
    0

    Hi Elvis

    Thanks so much for your help and guidence with this.

    Yes, that piece I was able to do. But the problem is I want to avoid breaking the MDX to do month, day, year seperately from the calendar hiararchy. So if I input parameter is 6/25/2010 and end date is 2/15/2012

    Accoriding to what you have said I will have to do months seperately starting from June of 2010 till feb 2010 and that will be entire month of June, which will be wrong as I only want aggregations from 25th of June.

    So inorder to resolve that I will have to do 6/25/2010 till 7/1/2010 exclusive day level calendar and then 7/1/2010 till 1/1/2011 monthly and then

    1/1/2011 till 1/1/2012 Yearly and then again 1/1/2012 till 2/1/12 monthly and then 2/15/2012

    Thats ALOT of breaking of dates.

    I was hoping to see if there is a way to use the calendar hiararchy to do it breaking on its own at daily, monthly and yearly level.

    I hope I am making sence here.

    I can get the result set from the leaf level of the hiararchy at daily or hourly level for 6/25/2010 and end date is 2/15/2012. It displays the records fine but when the year span becomes 10 years. It would be best for the engine to give results based on yearly calculations like if I say give me aggregations from 2/14/2000 till 4/12/2012. Thats 12 years, whats the bast way of pulling records without breaking the date variable in chunks and pulling data straight out from various hiarachies.

    Look forward to your response.

    Thanks much again

    -Sarah

     
  • Wednesday, January 23, 2013 12:46 AM
     
      Has Code
    Sign In to Vote
    0

    Thanks Butmah

    So what your saying is that. Something like this should work, I havent tried it as yet but will do in the next couple of hours.

    I wonder how is it actually pulling data, is it pulling it from yearly level where whole year needs aggregation and monthly where entire month needs aggregation or is it aggregating at the leaf level?

    Select 
[Dim_DateTime].[Calendar].[2010].[1].[6] :
[Dim_DateTime].[Calendar].[2012].[12].[6]

    Thanks much

    -Sarah