locked
Counting members based on cube fact table date range RRS feed

  • Question

  • Hi,

     

    I am trying to reduce the number of records loaded in a cube by giving facts a date range valid-from and valid-to date in the fact table. For example (Member, value, valid_from, valid_to)

     

    12345    20   2008-01-01   2008-01-15

    12345    25   2008-01-16   2008-03-31

    23456    20   2008-01-01   2008-03-31

    .....

     

    The Valid_From and Valid_To dates each have thier own dimension

     

    How do I do a count of members where a date falls within the date range on the fact?

     

    ie for value 20 and a reference date of 2008-01-09 the count would be 2, for value 25 and ref date of 2008-01-05 , count = 0 in this small sample.

     

    It would seem I need a third reference time dimension to act as a reference date for the calculation?

     

    Thanks to anyone who can help.

     

    Monday, September 8, 2008 5:15 PM

All replies

  • What you want to do is something called "in-flight" events.  Here's a link to the topic. There are also several posts on this forum that discuss this problem at greater length.

     

    http://www.ssas-info.com/analysis-services-articles/50-mdx/498-counting-in-flight-events-in-mdx-by-mosha-pasumansky

    Monday, September 8, 2008 5:46 PM
  • Martin

     

    Thanks - I did find this post but for some reason could not get it to work. I think the issue was that Mosha used an order date as the seed which will always exist. I am not sure how to define my seed date based on existing dimension data (valid_from for example) even though a member might not exist in the cube

     

    Thanks again.

    Monday, September 8, 2008 6:30 PM
  • Trevor,

     

    Without more information about your business domain, I wouldn't be able to suggest a third date role. It could be that you're passing a date into a report (SSRS?) and you need to use that value to drive the set. Something like the following:

     

     

    Code Snippet

    WITH SET [Valid Members] AS

    Filter (

    CrossJoin (

    { StrToMember("[Valid From Date].[Calendar].[" + @DateValue + "]") : NULL },

    { NULL : StrToMember("[Valid To Date].[Calendar].[" + @DateValue + "]") }

    )

    , ( [Measures].[Value] ) = 25

    )

     

     

    It's a way of replacing the third date role if you're looking to use the expression in a report.
    Monday, September 8, 2008 10:21 PM
  • Martin,

     

    Thanks for helping me out. I am not passing a parameter but I guess I am trying to use existing member in the 'valid from' date dimension as a parameter in the measure

     

    Aggregate (

    CrossJoin (

    { StrToMember("[StartDateDim].[Day].[" + dimension member date + "]" ) },

    { StrToMember("[EndDate].].[Day].[" + dimension member date + "]" ) }

    )

    , Measures.[Member Count]

    )

     

    and then I would use the valid date dimension in the report.

     

    All I seem to be able to get is a count of members with a specific 'valid from' date and not a count of those falling in the  from/to range.

     

    I that clearer or am I barking up the wrong tree?

    Tuesday, September 9, 2008 4:43 PM
  • I think I got it now. You're going down the right path. However, the expression above only gives you the count of members which have a valid from date equal to the valid to date.  I think the expression below will work. The first set gives you all the members with StartDateDim equal to or prior to the currently selected date while the second set gives you all the members with a EndDate on or after the selected StartDateDim value. I think that's what you're trying to get.

    Code Snippet

    WITH MEMBER [Measures].[Active Member Count] AS

    Aggregate(

    CrossJoin (

    {Null:[StartDateDim].[Day].CurrentMember },

    {LINKMEMBER( [StartDateDim].[Day].CurrentMember, [EndDate].[Day]):Null }

    ), ( [Measures].[Member Count] )

    )

     

     

    HTH,
    Tuesday, September 9, 2008 9:59 PM
  • Martin,

     

    This looks like it will work for me. Thank you very much for your patience and help.

    Wednesday, September 10, 2008 4:24 PM
  • Martin,

     

    I tried your suggestion but get an error of 'Unknown dimension [StartDateDim].[Day]' even though [Day] is a level in the StartDateDim dimension?

     

    Any ideas?

     

    Otherwise I think this will work great.

    Wednesday, September 10, 2008 8:24 PM
  • You would need to specify the level as [Dimension Name].[Hierarchy Name].[Level Name]?  I'm only guessing though.

     

    Wednesday, September 10, 2008 11:19 PM
  • Old post but this has just solved a huge issue for me.

     

    In excel when filtering for a random date, I had to return a count of the measure. However In my fact table I only had date ranges.

     

    This code seems to do the job!

     

     

     

    Aggregate(

     

    CrossJoin (

     

    {Null:[Date From].[Date Sk].CurrentMember },

     

    {LINKMEMBER( [Date From].[Date Sk].CurrentMember, [Date To].[Date Sk]):Null }

     

    ), ( [Measures].[Offer ID] )

    Saturday, February 4, 2012 3:29 PM