Re: Has Microsoft Totally Failed on Time Calculations
- From: robzare@xxxxxxxxx
- Date: 28 Mar 2006 18:58:58 -0800
Now here's the real kicker about this work around. It does not correctFrom Tom:
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:
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:
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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8289DD5D-EA82-432C-97A2-FB81DAEC7A64@xxxxxxxxxxxxxxxx
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/ArticleID/46157/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?
.
- Follow-Ups:
- References:
- Has Microsoft Totally Failed on Time Calculations
- From: Tom L.
- Re: Has Microsoft Totally Failed on Time Calculations
- From: Vimas
- Re: Has Microsoft Totally Failed on Time Calculations
- From: Tom L.
- Has Microsoft Totally Failed on Time Calculations
- Prev by Date: Re: AS2005: Implementing a Static filter in a perspective?
- Next by Date: Re: AS2005: Implementing a Static filter in a perspective?
- Previous by thread: Re: Has Microsoft Totally Failed on Time Calculations
- Next by thread: Re: Has Microsoft Totally Failed on Time Calculations
- Index(es):
Relevant Pages
|
