Re: Has Microsoft Totally Failed on Time Calculations
- From: "Vimas" <Vimas@xxxxxxxxxxxxx>
- Date: Wed, 29 Mar 2006 10:22:56 -0500
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@xxxxxxxxx> wrote in message
news:1143601138.914474.113930@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
From Tom: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.
=======================
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?
.
- 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.
- Re: Has Microsoft Totally Failed on Time Calculations
- From: robzare
- Has Microsoft Totally Failed on Time Calculations
- Prev by Date: Newbie - Unable to find Analysis Service Packs
- Next by Date: Re: Dynamic Cell security
- Previous by thread: Re: Has Microsoft Totally Failed on Time Calculations
- Next by thread: RE: DISTINCT COUNT performance and Snapshot Query?
- Index(es):
Relevant Pages
|
