I have been advocating our company's adoption of SSAS 2005 for over 6 months
now. For the most part, this has been a good choice. But in my personal
opinion MS has really dropped the ball on time calculations, one of the most
important features of any BI suite. I am completely frustrated with their
failure in this regard.

I have read all the posts related to time not working and potential
workarounds in this newsgroup and various articles on the web:

As far back as June 2005 the Time Intelligence Wizard was being touted (See:
Analysis Services Brings You Automated Time Intelligence, SQL Server
Magazine, http://www.sqlmag.com/Articles/Artic...57/pg/1/1.html )

There was only one small problem: the wizard only calculates the year to
date aggregations at the year level.

Chris Webb published an excellent summary of the problem last November ( see
http://spaces.msn.com/cwebbbi/blog/cns!7B84B0F2C239489A!379.entry ) In the
comments, you can see a big discussion between him, Jon, and Mosha. Jon and
Chris basically say: your year-to-date/time calculations have to work with a
variety of different time attributes in your mdx/pivot table, they can't just
work for the time hierarchy you've associated them with. Mosha provided an
MDX example that served as the basis for the work around listed below but
that did not solve the problems outlined by Chris.

So, Microsoft publishes a supposed fix to this problem (based on Mosha's
post on Chris' blog) (See http://support.microsoft.com/?kbid=912136 ).

Now here's the real kicker about this work around. It does not correct all
year-to-date calculations! Try putting your time hierarchy in the filter
area of a pivot table and filter for a particular month (my time hierarchy is
at the month grain). Now put your time calculations attribute on the rows or
columns. What do you get? A nice "NA" for your year-to-date calculation.

For the users I support this is a very common scenario. We look at
accounting data. We will filter for a particular month and then want to see
the Current Month and Year-to-Date data.

The thing that really irks me about this is that time calculations are at
the heart of most BI solutions. And for Microsoft to drop the ball on
something this big makes them look like a 2nd tier BI player, which I don't
think they are.

So Microsoft, WHAT ARE YOU GOING TO DO ABOUT THIS?

Hi Tom,

As I can see you are really frustrated by this problem. I had similar
problem some time ago that I reported in this newsgroup and as answer I was
pointed to article http://support.microsoft.com/?kbid=912136. At first it
looked like it fixed my problem, but later I found that it not.
So basically I decided to ignore this problem during development and see
what is going to be in SP1.
I am not ignoring problem by pretending it is not there - as that would
affect my testing. I simply using workaround and I will get rid of it when
Microsoft will release fix that works.

My workaround is - to introduce another time level that is not visible to
end users.
Lets say you have dimension:
Year
Qtr
Mth

I would use hierarchy:
Year
Qtr
Mth
Mth Key - and this is the key attribute!

Mth Key level is not visible to user. For me this approach works and I have
many and very complicated time calculations that now works.
I know adding extra level will affect performance, but this way Microsoft
bug does not interfere with my development.
When fix will be available, I just will get rid of that lowest level.

I am not saying that this is ideal solution, but you have to admit - new
Microsoft features are cool and it is worth to have some pain during
migration to get all other benfits.

Regards






"Tom L." <TomL (AT) discussions (DOT) microsoft.com> wrote

Vimas,

Thank you for taking the time to reply to my post. Your suggestion has
fixed the year-to-date calculation. I appreciate your willingness to share
your workaround. Let's hope MS can develop a permanent fix for this in SP2.

Thanks,

Tom

"Vimas" wrote:

all
year-to-date calculations! Try putting your time hierarchy in the
filter
area of a pivot table and filter for a particular month (my time
hierarchy is
at the month grain). Now put your time calculations attribute on the
rows or
columns. What do you get? A nice "NA" for your year-to-date
calculation.

=======================

The fix mentioend in the KB article is in SP1. That said, I cannot
repro your issue on SP1 and I don't see why you would need the
workaround from Vimas.

Do you have a repro that can be demonstrated on Adventure Works?
Otherwise, if you can send me a sample a DB, I can verify.

-rob



Tom L. wrote:

I am missing a message in between, so I am not sure if Rob is saying that
Microsoft fix does not work or my workaround does not work?

Vimas


<robzare (AT) gmail (DOT) com> wrote

Because the company I work for has an extensive process for testing SPs
before they are applied, I was only able to read the fixes in the release
notes of SP1. See: http://support.microsoft.com/kb/913090

I don't see any mention of my particular problem or a time intelligence fix
in these release notes.

Again, the problem I have is: I have a time dimension with a grain of
month: Year > Quarter > Month. After you manually apply the fix related to
time dimensions that MS released (see earlier post), put the month level of
your hierarchy in a row or column and then add your time calculations to the
filter area. You will see "NA" for your year-to-date slice.

Vimas provided an interim fix: add a second month attribute to your Time
Dimension, make it the key, hide it, and then put this new key in your
Year-to-Date MDX scope, replacing the original month key. Then when you
browse your original month in the above scenario, you will see year-to-date
calculations for the month grain.

Thanks,

Tom

I am trying to build my own SSAS 2005 cube, and I'm going through the
same issues that everyone seems to be having. I've read Chris Webb's
blog, I've read the 912136 KB article, I've tried to read through
everything that is posted on here, etc. I still can't get the simplest
YTD stuff to work. I'm new to this BI stuff, so I'm not very familiar
with what I'm looking at. Here's the details of my work (it's a pretty
simple cube).


/*
Begin Time Intelligence script for the [Transaction Detail].[TrnYear
- TrnMonth] hierarchy.
*/
Create Member CurrentCube.[Transaction Detail].[TrnYear - TrnMonth
Transaction Detail Calculations].[Year to Date] AS "NA"; -- this is
all one line

Scope({[Measures].[Net Sales Value]});

( [Transaction Detail].[TrnYear - TrnMonth Transaction Detail
Calcs].[Year to Date]) =

Aggregate(
{ [Transaction Detail].[TrnYear - TrnMonth Transaction Detail
Calcs].DefaultMember } *
PeriodsToDate
(
[Transaction Detail].[TrnYear - TrnMonth].[TrnYear]
, [Transaction Detail].[TrnYear - TrnMonth].CurrentMember
)
);
End Scope;


My data only has a "Year" (TrnYear) and a "Month" (TrnMonth) component.
My dimension is [Transaction Detail]. My hierarchy is [TrnYear -
TrnMonth] (it just has TrnYear and TrnMonth). The name of this
calculation is [TrnYear - TrnMonth Transaction Detail Calcs]. Can
anyone look at this code and tell me what is wrong?

The cube processes fine, but when I try to look at this data in the
cube browser, I get weird results.
I put the [TrnYear - TrnMonth Transaction Detail Calcs] in the
"Column".
I put the [Net Sales Value] in the "Totals" area.
I put the [TrnYear - TrnMonth] hierarchy in the "Rows" area.

When I look at a specific year, I expand it out to see all the months.
In the "Totals" area, it displays two columns, "Current Transaction
Detail" and "Year to Date". Both columns have values. The Current
Transaction Detail is calculating correctly, but the Year to Date
column is showing incorrect numbers (the numbers are way too high).
January shows correctly, but after that month, it looks like it's
trying to sum all of the years data. For example, the "February Year
to Date" value isn't just January.2005 + February.2005, it is
January.AllYears + February.AllYears.

Can anyone look at this MDX and tell me what is wrong?

I'm also curious as to what the values represent in the MDX code. For
example, what is the "DefaultMember" referring to?


Tom L. wrote: