Anyone gotten Year-to-Date to work using SSAS 2005 'Define Time Intelligence' wizard?

[duwayne...@utsouthwestern.edu]

Hi,
Having a separate dimension for special data calculations
(Year-to-Date, Prior Year-to-Date, Year over Year increase, etc) is
highly-desired by most BI users as is well known. We had a separate
'Periodicity' dimension in our AS2000 cubes, but have been intrigrued
by the SSAS2005 'Add Business Intelligence Wizard's possibilities.
However, I've not been able to get the 'Year to Date' function to
work...

From within a cube, if one starts this wizard and then selects
'Define Time Intelligence', the Wizard assesses the time dimension
hierarchies present, and asks to have one selected. Then it offers to
create a variety of useful calculations, in 3 main categories:
(1) Year To Date, Quarter To Date, Month To Date, 12 Months To Date,
(2) 12 Month Moving Average, 6 Month Moving Average, 3 Month Moving
Average
(3) Year over Year Growth (and %), likewise for Quarter over Quarter,
Month over Month, and Day over Day.

Proceeding, the Wizard modifies the cube Data Source View and Date
dimension to add a new attribute called (in our case) Date.By Fiscal
Year Date Calculations. This new attribute dimension can have a
variety of useful time calculations added as members. This is ideal,
as one could for instance have Measures on rows in a table, and this
'Date Calculations' field on columns.
MDX for the selected Date Calculations is automatically created
(scoped to the cube measures selected).
My problem is that the Moving Averages and Period-over-Period Growth
members work correctly, but none of the 'To Date' calculations work.
They don't seem to work regardless of whether a Year, Quarter, Month,
or Date is selected.
We've constructed the Date hierarchy from a DIM_Date SQL view, with
defined cube dimension levels of Year, Quarter, Month, and Date. We've
set up attribute relationships to link each level to the next highest
(as in the Project REAL best practices document and the SQL2005
Tutorial), and defined the cube dimension as a Time dimension. The
dimension itself looks correct when browsing the dimension and/or cubes
built with the dimension.
But when used for browsing a cube, the Year-to-Date function returns
the default 'NA' for all cells...
Has anyone been able to get this 'Year-to-Date' functionality to work?

The MDX is complicated, and will attach snippets of the auto-generated
MDX below.
/*
Begin Time Intelligence script for the [Date].[By Fiscal Year]
hierarchy.
*/

Create Member CurrentCube.[Date].[By Fiscal Year Date Calculations].
[Year to Date] AS "NA";

Create Member CurrentCube.[Date].[By Fiscal Year Date Calculations].
[Three Month Moving Average] AS "NA";
-- etcetera

-- The following 'Year to Date' calculation does not work,
-- and returns 'NA' for all cells explored so far:

( [Date].[By Fiscal Year Date Calculations].[Year to Date],
{
[Measures].[Total Units],
[Measures].[Total Charges],
[Measures].[Total Visits]
},
[Date].[Fiscal Yr].[Fiscal Yr].Members ) =

Aggregate(
{ [Date].[By Fiscal Year Date Calculations].DefaultMember
} *
PeriodsToDate(
[Date].[By Fiscal Year].[Fiscal Yr],
[Date].[By Fiscal Year].CurrentMember
)
);

--The following 'Three Month Moving Average' MDX works fine

( [Date].[By Fiscal Year Date Calculations].[Three Month Moving
Average],
{
[Measures].[Total Units],
[Measures].[Total Charges],
[Measures].[Total Visits]
},
[Date].[Month].[Month].Members ) =

Avg(
{
ParallelPeriod(
[Date].[By Fiscal Year].[Month],
2,
[Date].[By Fiscal Year].CurrentMember
) : [Date].[By Fiscal Year].CurrentMember
},
[Date].[By Fiscal Year Date Calculations].DefaultMember
);

Interestingly, previously in AS2000 this seemed to be much simpler.
The MDX using our 'Periodicity' dimension (a single-row dimension) was:
Sum(
PeriodsToDate(
[Date].[By Fiscal Year].[Fiscal Yr],
[Date].[By Fiscal Year].CurrentMember),
[Periodicity].[Periodicity].&[1]
)
We have not been able to get this method to work yet in SSAS 2005,
either.

Am probably overlooking something in our implementation. Any advice,
comments, or experiences getting Year-To-Date functionality working in
SSAS 2005?

Thanks,
DuWayne

[Clint]

I am sorry I can't help you as we are suffering the same issues. It appears
date related calculations in AS2005 are now far more difficult to create.

I will watch this thread with interest..

Clint

<duwayne...@utsouthwestern.edu> wrote in message
news:1138577218.8...@g44g2000cwa.googlegroups.com...

> Hi,
> Having a separate dimension for special data calculations
> (Year-to-Date, Prior Year-to-Date, Year over Year increase, etc) is
> highly-desired by most BI users as is well known. We had a separate
> 'Periodicity' dimension in our AS2000 cubes, but have been intrigrued
> by the SSAS2005 'Add Business Intelligence Wizard's possibilities.
> However, I've not been able to get the 'Year to Date' function to
> work...

<snip>

[darrengosbell]

Chris Webb has an article on his blog that might be of interest.

http://spaces.msn.com/cwebbbi/blog/cns!7B84B0F2C239489A!379.entry

There is an interesting thread in the comments which is worth reading
too.

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <uKyQCYVJ...@TK2MSFTNGP09.phx.gbl>, ccpatriot12
@yahoo.com says...

[Clint]

Thanks Darren, very interesting blog.
<Darren Gosbell> wrote in message
news:MPG.1e48a4bb2...@news.microsoft.com...

[duwayne...@utsouthwestern.edu]

Yes, thank you for these pointers and links!
Another link is at:
http://forums.microsoft.com/msdn/showpost.aspx?postid=141020&SiteID=1

With this help, the following two approaches both work correctly for
Year-To-Date and Prior Year-To-Date calculations.
(1) without use of Crossjoin
(2) with use of Crossjoin

Either method gives the same answers. Am not sure if any performance
differences.
Both work drilling up and down the Date.Fiscal hierarchy.
Will also post a working Quarter-to-Date MDX script at the end

I'll post the working MDX code in our cube below for both.
The time dimension hierarchy we are using is called [Date].[Fiscal]
with the following levels:
[Fiscal Year] --> [Fiscal Quarter] --> [Month] --> [Date]

(1) without use of Crossjoin
(a) Fiscal Year To Date MDX:

( [Date].[Fiscal Date Calculations].[FYTD],

{
[Measures].[Total Units],
[Measures].[Total Charges]
},

[Date].[Date].Members,
[Date].[Fiscal Year].[Fiscal Year].Members) =

Sum(
{
PeriodsToDate(
[Date].[Fiscal].[Fiscal Year],
[Date].[Fiscal].CurrentMember
)
},
[Date].[Fiscal Date Calculations].DefaultMember
) ;

1 (b) Prior Fiscal Year-To-Date MDX (without crossjoin):

( [Date].[Fiscal Date Calculations].[PYTD],

{
[Measures].[Total Units],
[Measures].[Total Charges]
},

[Date].[Date].Members,
[Date].[Fiscal Year].[Fiscal Year].Members) =

Sum(
{PeriodsToDate(
[Date].[Fiscal].[Fiscal Year],
ParallelPeriod(
[Date].[Fiscal].[Fiscal Year],1,
[Date].[Fiscal].CurrentMember
)
)
},
[Date].[Fiscal Date Calculations].DefaultMember
) ;

2 With Crossjoin
2(a) Fiscal Year To Date MDX:

( [Date].[Fiscal Date Calculations].[FYTD Crossjoin],
[Date].[Date].Members,
[Date].[Fiscal Year].[Fiscal Year].Members) =
Aggregate(
{ [Date].[Fiscal Date Calculations].DefaultMember } *
PeriodsToDate(
[Date].[Fiscal].[Fiscal Year],
[Date].[Fiscal].CurrentMember
)) ;

2 (b) Prior Year To Date (using Crossjoin)

( [Date].[Fiscal Date Calculations].[PYTD Crossjoin],
[Date].[Date].Members,
[Date].[Fiscal Year].[Fiscal Year].Members) =
Aggregate(
{ [Date].[Fiscal Date Calculations].DefaultMember } *
PeriodsToDate(
[Date].[Fiscal].[Fiscal Year],
ParallelPeriod(
[Date].[Fiscal].[Fiscal Year],1,
[Date].[Fiscal].CurrentMember
)
));

2(c) Quarter-to-Date (using Crossjoin method) MDX

( [Date].[Fiscal Date Calculations].[FQTD],
[Date].[Date].Members,
[Date].[Fiscal Quarter].[Fiscal Quarter].Members) =
Aggregate(
{ [Date].[Fiscal Date Calculations].DefaultMember } *
PeriodsToDate(
[Date].[Fiscal].[Fiscal Quarter],
[Date].[Fiscal].CurrentMember
)) ;

The above is all working code. Hopefully, Microsoft will fix this code
generation in the next update of the Add Time Intelligence Wizard.
I really like the [Date Calculation] field approach in general - am
hoping users will find this relatively straightforward to use.

[glob...@yahoo.com]

I have been trying to get this YTD, MTD to work for some time now. My
problem is that my YTD calculation works fine as long as I am picking a
year, a quarter, or a month. As soon as I pick a specific date and try
to get YTD through that date (YTD with 1/6/2006 selected should show
total from 1/1 - 1/6), my YTD total just equals the total for the
current period (the specific date selected). Thus my current time
total and YTD value become the same as soon as I pick a date. With a
month selected, YTD is working fine.

I first tried using the 'Add Business Intelligence' and have been
trying to refine ever since. I tried the above code and it is also
doing the same thing as above (at least for the cube I am working
with).

I've got a time dimension and a hierarchy of [Year] [Quarter] [Month]
[Date].

Any help would be greatly appreciated.

Best regards,
Shawn

[Deepak Puri]

Hi Shawn,

Here's an AW example of YTD() working at the date level:

>>
With Member [Measures].[YTDQuantity] as
Sum(YTD([Date].[Calendar].CurrentMember),
[Measures].[Order Quantity])

select {[Measures].[Order Quantity],
[Measures].[YTDQuantity]} on 0,
Head([Date].[Calendar].[Month].&[2002]&[1].Children, 6) on 1
from [Adventure Works]
>>

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

[duwayne...@utsouthwestern.edu]

Deepak,
I think that's code for YTD at the Measures level. That's certainly
useful.

However, we've been struggling with getting YTD working in a separate
Date Calculations dimension, completely distinct from the Measures
dimension.
This is so, e.g. a number of different Measures could be on rows, and
various Date Calculations (YTD, Prior YTD, variance, etc) on columns.
This is a harder problem, and the method most of us used in AS2000
doesn't seem to work in AS2005.

Shawn,

The following code (slightly revised from prior post) works for us down
to the individual Date level, showing incremental YTD values day by
day.

You've likely already checked this, but is the Date level in your
hierarchy labeled with Type = 'Date' in the Properties window?

Best wishes,
DuWayne

--------------------
CREATE MEMBER CURRENTCUBE.[Date].[Fiscal Date Calculations].
[FYTD] AS "NA",
FORMAT_STRING = "#,#.00", VISIBLE = 1 ;

( [Date].[Fiscal Date Calculations].[FYTD],

[Date].[Date].Members,
[Date].[Fiscal Year].[Fiscal Year].Members) =
Aggregate(
{ [Date].[Fiscal Date Calculations].DefaultMember } *
PeriodsToDate(
[Date].[Fiscal].[Fiscal Year],
[Date].[Fiscal].CurrentMember
));

---------------------

CREATE MEMBER CURRENTCUBE.[Date].[Fiscal Date Calculations].
[PYTD] AS "NA",
FORMAT_STRING = "#,#.00", VISIBLE = 1 ;

( [Date].[Fiscal Date Calculations].[PYTD],

[Date].[Date].Members,
[Date].[Fiscal Year].[Fiscal Year].Members) =
Aggregate(
{ [Date].[Fiscal Date Calculations].DefaultMember } *
PeriodsToDate(
[Date].[Fiscal].[Fiscal Year],
ParallelPeriod(
[Date].[Fiscal].[Fiscal Year],1,
[Date].[Fiscal].CurrentMember
)
)) ;

-----------------------

CREATE MEMBER CURRENTCUBE.[Date].[Fiscal Date Calculations].
[FQTD] AS "NA",
FORMAT_STRING = "#,#.00", VISIBLE = 1 ;

( [Date].[Fiscal Date Calculations].[FQTD],
[Date].[Date].Members,
[Date].[Fiscal Quarter].[Fiscal Quarter].Members) =
Aggregate(
{ [Date].[Fiscal Date Calculations].DefaultMember } *
PeriodsToDate(
[Date].[Fiscal].[Fiscal Quarter],
[Date].[Fiscal].CurrentMember
)) ;

----------------------
CREATE MEMBER CURRENTCUBE.[Date].[Fiscal Date Calculations].
[Var FYTD vs PYTD]
AS "NA",
FORMAT_STRING = "#,#.00", VISIBLE = 1 ;

([Date].[Fiscal Date Calculations].[Var FYTD vs PYTD]) =

[Date].[Fiscal Date Calculations].[FYTD]

- [Date].[Fiscal Date Calculations].[PYTD]
;

-----------------------

CREATE MEMBER CURRENTCUBE.[Date].[Fiscal Date Calculations].[%Var FYTD
vs PYTD]
AS "NA",
VISIBLE = 1;

([Date].[Fiscal Date Calculations].[%Var FYTD vs PYTD]) =

([Date].[Fiscal Date Calculations].[FYTD]

- [Date].[Fiscal Date Calculations].[PYTD])
/

;

[glob...@yahoo.com]

You guys absolutely rock, what a great group of folks willing to help!
Thank you both very much!

Shawn