Re: 'Last month' in Time dimension (named sets?)

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Michael Vardinghus (michaelvardinghus_at_notexisting.com)
Date: 05/20/04 

Date: Thu, 20 May 2004 11:51:11 +0200

Want to make another comment (before finishing my breakfast):

One of the problems I had with the approach below was not having total
control af the exact number of columns that
one of my clients required in the same cross tab. Example:

Year to date
Last Year same periodod
Last Year total
Budget this Year total

This is examples of date coding where I need them at once in the same
report - as i see the periodicity dimension is more for changing the value
in this dimension and seeing a new time frame .. not for combining different
time frames in the same cross tab.

This doesnt mean you can't do it....you can just make a calc member in the
measures which references a calc member in the time dimension - as i recall
.. but then i will make a calc meauser member which was the one I wanted to
avoid in the first place.

Someone would argue that you can accomplish this by making multiple
selections in a page / criteria field ... but to my knowledge this means
that drillthrough won't work...

So for the time being i'm not using periodicity - when the need for more
time frames and a more interactive use of the data is required i will
reconsider.

\Michael

"Michael Vardinghus" <michaelvardinghus@notexisting.com> skrev i en
meddelelse news:efV7DDkPEHA.3124@TK2MSFTNGP12.phx.gbl...
> Won't look for the other sample because the microsoft link below describes
> it
> very well...
>
> It's a bit odd at first ... you have to put a dummy value into the fact
> table to have something to map the new dimension with.
>
> But I believe this is what you're looking for.
>
> "Michael Vardinghus" <michaelvardinghus@notexisting.com> skrev i en
> meddelelse news:u0I5GAkPEHA.3804@TK2MSFTNGP12.phx.gbl...
> > Will get back to you with another example...
> >
> > Here's deepaks' reply to something similar:
> >
> >
> > You need to create a dimension table - see examples below:
> >
> > - Tom Chester's web-site has this sample database, which creates a
> > calculation dimension with a YTD member:
> >
> > http://www.tomchester.net/articlesdownloads/calculationdimension.html
> >
> >
> > - Here is a Microsoft support article, using Foodmart 2000 Sales cube:
> >
> > http://support.microsoft.com/default.aspx?scid=kb;EN-US;q304118
> > >>
> > INF: How To Perform Time Series Calculations (Q304118)
> > ..
> > >>
> >
> >
> > - And here is a post from George Spofford, author of "MDX Solutions":
> >
> > http://groups.google.com/groups?q=spofford+utility+dimension&hl=en&lr=&i
> > e=UTF-8&oe=UTF-8&selm=3D66285F.F69BE1E5%40dsslab.com&rnum=1
> > >>
> > A time analysis utility dimension has no all level and 1 real member in
> > a dimension table named something
> > like "Current" with a key value like 0 or 1. You can create and process
> > the dimension as usual. Bring the
> > dimension into the cube, join it to the fact table on any column of the
> > fact table, and set the member
> > key in the cube the constant 0 or 1 (matching Current's key).
> >
> > Add calculated members to the cube on this dimension:
> >
> > CREATE MEMBER [Time Series].[YTD] AS
> > 'Aggregate (
> > PeriodsToDate (
> > [Time].[Year],
> > [Time].CurrentMember
> > ),
> > ([Time Series].[Current])
> > )'
> >
> > CREATE MEMBER [Time Series].[QTD] AS
> > 'Aggregate (
> > PeriodsToDate (
> > [Time].[Quarter],
> > [Time].CurrentMember
> > ),
> > ([Time Series].[Current])
> > )'
> >
> > A query for YTD sales at August 2002 is a query for the tuple
> > ([Measures].[Sales], [Time Series].[YTD], [Time].[Aug 2002])
> >
> > HTH
> > >>
> >
> >
> > - Deepak
> >
> > *** Sent via Developersdex http://www.developersdex.com ***
> > Don't just participate in USENET...get rewarded for it!
> >
> >
> > "dejan" <dejan@nospam> skrev i en meddelelse
> > news:%23MMw6ufPEHA.2580@TK2MSFTNGP09.phx.gbl...
> > > Hi,
> > > Thanks for your reply.
> > >
> > > Most solutions I see here are talking about using a measure in the MDX
> > > query. I don't need this. I simply want the user to be able to select
> Last
> > > Month from the date dimension. This should filter whatever measure the
> > user
> > > is looking at by showing only values for the last month. Another
> > requirement
> > > is that the 'Last month' item in time dimensions be dynamic, that is
it
> > > should calculate what the last month period is so that i don't have to
> > make
> > > changes to the MDX every day.
> > >
> > > I've managed to solve this problem by doing the following:
> > > 1. Added LastMonth bit filed to time dimension table
> > > 2. Wrote stored procedure that uses GetDate function to set the
> Lastmonth
> > > field to true for the dates in the last month. Stored procedure runs
> > before
> > > cube build in DTS
> > > 3. In the AS manager added LastMonth as memeber proeprty for the day
> level
> > > of time dimension.
> > > 4. Created a virtual dimension using this memeber property.
> > > 5. added virtual dimension to the cube
> > >
> > > This is working but is very complicated and clumsy solutions.
especially
> > as
> > > user want other special periods other than Last Month such as MTD,
YTD,
> > QTD,
> > > last quarter, parallel quarter, etc.
> > >
> > > It would be much better to show these special periods as a special
item
> in
> > > the time dimension instead of a separate dimension. Is this possible
> using
> > > calcualted member for a time dimension? I couldn't find an example
that
> > does
> > > not use a measure. i want this to work for all the measures in the
cube,
> > not
> > > one specific measure.
> > >
> > > Regards,
> > > Dejan
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > "Michael Vardinghus" <michaelvardinghus@notexisting.com> wrote in
> message
> > > news:ez2rMOdPEHA.2468@TK2MSFTNGP11.phx.gbl...
> > > > Don't think you should use named sets - it seems natural - true -
but
> as
> > > you
> > > > point out not many front ends
> > > > support it.
> > > >
> > > > These a the normal approaches
> > > >
> > > > a) making a periodicity dimension - you can catch some input here
> about
> > > how
> > > > to go about doing this - have some materials on paper
> > > > on this as well - the link below doesnt mention time in particular
so
> > > > perhaps it's a little to abstract. If so I can find the materials
and
> > give
> > > > further input.
> > > >
> > > > b) making a calc measure for each of the time frames you need - this
> is
> > a
> > > > quick way and you get the exact number of columns you want - not as
as
> > > > elegant as a and if you have lots of measures it can be difficult to
> use
> > > the
> > > > cube - if you have a Budget measure in the cube here's examples of
> using
> > > it
> > > >
> > > > - sum(ytd(),([Measures].[Budget])) - gives you the amount total per
> the
> > > > chosen time dimension value
> > > > - sum(ytd(parallelperiod(Time.[Year], 1, Tid.currentmember)),
> > > > [Measures].[Budget])- gives you the amount total last year per the
> > chosen
> > > > time dimension value
> > > > - iif([Time].currentmember.level is
> > > > [Year],[Measures].[Budget],ancestor([Time].currentmember,
> > > [Time].[Year])) -
> > > > gives you the total year amount regardless of time dimension value
> > choice
> > > >
> > > > I'm no expert on this so I have only the sentences for what I've
> needed
> > so
> > > > far...
> > > >
> > > > In both cases you need to do something with mdx in as.
> > > >
> > > > However in the bi accelerator microsoft has used named sets but I
> think
> > > > that's proclarity supports these...they have used a combination of a
> > > > periodicity dimension dn named sets as I see it.
> > > >
> > > > \Michael V.
> > > >
> > > >
> > > > "dejan" <dejan@nospam> skrev i en meddelelse
> > > > news:uIor54LPEHA.3884@TK2MSFTNGP12.phx.gbl...
> > > > > Hi,
> > > > > I am new to OLAP and need help with one feature.
> > > > >
> > > > > I am using SQL 2000 analyses services on back end and users
connect
> to
> > > > cubes
> > > > > using Excel XP pivot tables and Cognos PowerPlay on the front end.
> > > > > Cubes have standard time dimensions (Year, Qtr, Month, Day) and a
> > number
> > > > of
> > > > > other dimensions (customers, products...). There are several
> measures
> > as
> > > > > well. Pretty basic stuff.
> > > > >
> > > > > Where it gets complicated is when I need to provide users ability
to
> > > > choose
> > > > > things like "last month", "month to date", "last quarter" and the
> like
> > > > from
> > > > > the time dimension. They had this ability in the previous version
of
> > the
> > > > > cube done in Cognos PowerPlay Transformer but I cannot figure out
> how
> > to
> > > > do
> > > > > it in MSAS.
> > > > >
> > > > > As far as I can see only way to implement this in MS AS is with
> named
> > > > sets.
> > > > > I could write a dynamic expression that uses Now() function and
MDX
> to
> > > > > create a set of time dimension memebers corresponding to current
> > month,
> > > > last
> > > > > month, etc. However, named sets I create in the cube are not
visible
> > in
> > > > > either Excel Pivot tables nor in Cognos PowerPlay.
> > > > >
> > > > > Is it possible to make named sets available to users of pivot
tables
> > and
> > > > > other front end tools like PowerPlay? (it seems to me that this
> should
> > > be
> > > > > the case, otherwise what is the point of them!?).
> > > > >
> > > > > Is there any other way that this functionality can be developed on
> the
> > > > back
> > > > > end for easy access by the end user.
> > > > >
> > > > > Help with this would be highly appreciated.
> > > > >
> > > > >
> > > > > Dejan
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Anyone gotten Year-to-Date to work using SSAS 2005 Define Time Intelligence wizard?
    ... Having a separate dimension for special data calculations ... 'Periodicity' dimension in our AS2000 cubes, ... the Wizard modifies the cube Data Source View and Date ... The MDX using our 'Periodicity' dimension was: ...
    (microsoft.public.sqlserver.olap)
  • Re: MDX Solutions Second Edition
    ... It seems a section that expands on the concept of a Periodicity ... dimension or a Calculations dimension would be very worthwhile. ... and another time dimension that tracks when something was paid. ...
    (microsoft.public.sqlserver.olap)