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

I am looking to do something similar, except I am writing the front-end
application in .net and ADOMD.

Is there any way you can look at the MDX queries that Cognos was
generating??

If you find any info, please post it back to this thread.

TIA,
Scott


"dejan" <dejan@nospam> wrote

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 (AT) TK2MSFTNGP12 (DOT) phx.gbl...

"Scott Riehl" <scott_riehl (AT) remove (DOT) b-f.com> wrote


Dejan --

Cognos Power Play, to the best of my knowledge, has difficulty reading
MS Analysis Services Cubes, at the present time, with respect to
advanced date stuff. I bleieve this is a known issue.

ProClarity and Panorama do not have this difficulty because they were
designed to work with Analysis Services and are MDX compliant.

Pete Hohenhaus
Portland, Oregon

Notice: This posting is provided "AS IS" with no warranties, and
confers no rights.

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 (AT) notexisting (DOT) com> wrote

Hi,
I am not having any difficulties with Cognos PowerPlay. It reads the AS
cubes just fine.

The problem I am having is with implementing Last month in time dimension in
MSAS.

Regards,
Dejan

"Pete Hohenhaus" <cam_pete (AT) hotmail (DOT) com> wrote

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/articlesdo...dimension.html


- Here is a Microsoft support article, using Foodmart 2000 Sales cube:

http://support.microsoft.com/default...;EN-US;q304118
...

- And here is a post from George Spofford, author of "MDX Solutions":

http://groups.google.com/groups?q=sp...on&hl=en&lr=&i
e=UTF-8&oe=UTF-8&selm=3D66285F.F69BE1E5%40dsslab.com&rnum=1
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 (AT) TK2MSFTNGP09 (DOT) 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 (AT) notexisting (DOT) com> skrev i en
meddelelse news:u0I5GAkPEHA.3804 (AT) TK2MSFTNGP12 (DOT) phx.gbl...

maybe u can try custom member in time dimesnion,
and see more detail in Microsoft SQL Server Accelerator for BI.


Hi,
I am not having any difficulties with Cognos PowerPlay. It reads the AS
cubes just fine.

The problem I am having is with implementing Last month in time dimension in
MSAS.

Regards,
Dejan

"Pete Hohenhaus" <cam_pete (AT) hotmail (DOT) com> wrote

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 (AT) notexisting (DOT) com> skrev i en
meddelelse news:efV7DDkPEHA.3124 (AT) TK2MSFTNGP12 (DOT) phx.gbl...