hi. i designed a measure based on the cube's time dimension using the
periodstodate function. it works fine when all the members of the dimension
are present in the query.

the dimension contains acad year, term, and weeks.

now i want to perform a query to get the measure for specific weeks. the
problem is that the periodstodate is not working correctly anymore.i noticed
that all the previous weeks should be present for it to give correct results.

is there a way to let the cube have the measure already calculated and me
selecting it instead of it doing the calculation at run time and giving me
inaccurate results?

i hope i explained right

thanks

Christina

Hmm - I would guess that your data tables below the OLAP cubes are current
snapshots of data (since you're using the PTD function). To have your cube
redesigned so that they will already calculate the data so that you do not
need to do this during run time, you would create a cummulative table vs. a
snaphot table.

What I mean is that instead of the row within the table for that particular
week to have the measures for that week (e.g. for week 28, there were 5
purchases), you would have that row contain the cummulative measure (e.g.
for week 28, there were 500 purchases from week 1 to week 28).

The problem with this approach is that you can only do cummulative
calculations against this cube based on a point in time. That is, you need
to specifically choose a date (e.g. week 28) vs. no date or a range of dates
because the measures do not make sense when spanning across multiple dates.

Saying this, could you clarify why the PTD function isn't working for you
for weekly queries? There may be another way to approach this calculation
without necessarily doing all of this work.

--
HTH!
Denny Lee
<dennyglee_at_hotmail_dot_com>
space: http://spaces.msn.com/members/denster/



"TinaWina" <TinaWina (AT) discussions (DOT) microsoft.com> wrote

yes this is an example: (my cube is about student absences)
time dimension contains acadyear, term and weeks
i have 2 measures:
index (number of absences/total no of students),
index avg up to last week (is avg of index starting week 1 of the current
term to the previous week). in this measure i'm using the PTD on the "index"
measure.
both's values depend on filters done on other dimensions invovled such as
school, absence type,excused status..etc..

after making sure that the results are all ok.. i have been asked to perform
a query to get the index avg on a specific week..let's say term 1, week 4. in
this case, i filter the weeks and put term1, week 4. this is where i'm having
a problem. the calculation of the "index avg" is not taking into
consideration the previous weeks "index" because they are not displayed after
i filtered the cube and hence giving me a value equal to the current week's
"index" because it's the only one displayed.

i'm doing that because i want to get a report of the absence situation of
the current week. i don't want to display all the weeks in my report, rather
just a snapshot of what took place this week and a comparison with the "index
avg" to know the progress status.
my other problem (i don't want to complicate things.it's just i can't figure
them out).. the current week's number is not necessarly the same in all the
schools. since we're going by school fiscal year and some schools start
before others. at a certain date it could be week 2 in a school and week 4
in another school.
for this reason i can't put a filter at the matrix's level in the report
for the current week because it's not just one value. (i'm using RS 2005). i
have made a dimension specially for the weekly (containing school/week) and
i'm joining it with the other dimensions to get the values of the "index" and
"index avg". maybe it's not the best solution but i don't know of a better
one. in the results, the "index" is correct since it's self based but the
"index avg" is wrong..because the previous indexes are not present.

Thanks for your help.
Christina


"Denny Lee" wrote: