Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a calendar date dimension backed by a physical table of dates (originally created on SQL Server 2000, hence the datetime instead of date):

CREATE TABLE [dbo].[PostDate_Dimension](
    [post_date] [datetime] NOT NULL PRIMARY KEY,
    [day_of_year] [int] NOT NULL,
    [day_of_month] [int] NOT NULL,
    [month_of_year] [int] NOT NULL,
    [post_year]  AS (datepart(year,[post_date])),
    [post_month]  AS (datepart(month,[post_date])),
    [post_day]  AS (datepart(day,[post_date]))
)

The Post Date dimension has four attributes (with member key columns listed, some of which are calculated in the DSV):

  1. Day (Dimension Key) - post_date
  2. Month - post_year, post_month
  3. Quarter - post_year, post_quarter = DatePart(quarter, "post_date"))
  4. Year - post_year

It's nothing too fancy, obviously. I also have a few calculated measures that use ParallelPeriod to calculate YTD figures from the previous year, for quick side-by-side comparison without requiring the user to choose a specific slice of dates. Just pick the current year, and it will find the latest date with sales in it, then compare to that same range from the previous year.

Finding the appropriate date in the previous year normally boils down to this:

ParallelPeriod(
    [Post Date].[Post Date].[Year],
    1,
    Tail(
        NonEmpty(
            Descendants(
                [Post Date].CurrentMember,
                ,
                Leaves
            ),
            Measures.[Total Price]
        ),
        1
    ).Item(0)
)

The Tail call is where it finds the latest date beneath the currently selected Post Date member (typically the current year). That works fine. But if that returns Feb. 29, meaning the last sale for a particular combination of dimension members occurred on Feb. 29, then it passes Feb. 29 into the ParallelPeriod function, which subsequently returns null. And then the previous-year YTD measure also returns null.

So, in a nutshell: Based on this particular schema, is there a simple way to have ParallelPeriod behave nicely for Feb. 29 inputs? If it just returns Feb. 28 of the previous year, that's fine.

EDIT:

A few things I've tried:

  • Using this expression to adjust the Post Date member:
    Iif(MONTH([Post Date].[Post Date].CurrentMember.Member_Caption) = 2 And DAY([Post Date].[Post Date].CurrentMember.Member_Caption) = 29, [Post Date].[Post Date].CurrentMember.PREVMEMBER, [Post Date].[Post Date].CurrentMember)
    This works, but the code would be atrocious, since I'd have to replace all [Post Date].[Post Date].CurrentMember with Tail(NonEmpty(Descendants([Post Date].CurrentMember,, Leaves), Measures.[Total Price]), 1).Item(0)).
  • Using Except to remove all Feb. 29 dates from the results of NonEmpty(Descendants([Post Date].CurrentMember,, Leaves), Measures.[Total Price]). I can't figure out the proper syntax (if any) to get a set of all Feb. 29s from the dimension.
  • Creating a .NET assembly with a user-defined function that takes a member as a parameter, and returns the previous member if it's a Feb. 29. It seems like the classes in Microsoft.AnalysisServices.AdomdServer are extremely limited and don't even allow for this basic task (nor even retrieving the member key as a date value).
share|improve this question
    
A quick and dirty fix might be to put an exception for feb 29 in a SCOPE statement. Try a little google-fu. Never bumped into this issue before but a google search for 'mdx parallelperiod feb 29 leap year' turns up a bunch of blog postings and suchlike that might be of help –  ConcernedOfTunbridgeWells Mar 2 '12 at 14:23
    
Interesting. I tried a bunch of Google searches but wasn't finding anything. I'll check those out. –  db2 Mar 2 '12 at 14:26

1 Answer 1

up vote 1 down vote accepted

Alright, I think I've got a workaround I can live with. First, I created a named set in the cube, called Feb29:

Filter(
    [Post Date].[Post Date].[Day].ALLMEMBERS,
    MONTH([Post Date].CurrentMember.Member_Caption) = 2 And DAY([Post Date].CurrentMember.Member_Caption) = 29
)

Then I've amended the portion of the calculation which looks for the latest sale date (note the - Feb29):

ParallelPeriod(
    [Post Date].[Post Date].[Year],
    1,
    Tail(
        NonEmpty(
            Descendants(
                [Post Date].CurrentMember,
                ,
                Leaves
            ),
            Measures.[Total Price]
        ) - Feb29,
        1
    ).Item(0)
)

Thus when it looks for the latest sale date in, say, 2012, it will ignore Feb. 29, and compare to the previous year's sales from Jan. 1 to Feb. 28 instead. I'm still open to suggestions if there's a better way to do this (particularly that awful Filter expression I used).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.