query cube at runtime

#1

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 years, terms, 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

************************************************** ********************
Sent via Fuzzy Software @ http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...

#2

Can you post the MDX and give some examples of when it produces the
right and wrong results?

It sounds like what you are trying to do should work, but there is not
enough information to diagnose your issue.

--
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

In article <OcaHhgJ2FHA.684 (AT) TK2MSFTNGP10 (DOT) phx.gbl>, Christina Tabet
(cstabet (AT) hotmail (DOT) com) says...

Quote:

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 years, terms, 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

************************************************** ********************
Sent via Fuzzy Software @ http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...

#3

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

"Darren Gosbell" wrote:

Quote:

Can you post the MDX and give some examples of when it produces the
right and wrong results?

It sounds like what you are trying to do should work, but there is not
enough information to diagnose your issue.

--
Regards
Darren Gosbell [MCSD]
dgosbell_at_yahoo_dot_com
Blog: http://www.geekswithblogs.net/darrengosbell

In article <OcaHhgJ2FHA.684 (AT) TK2MSFTNGP10 (DOT) phx.gbl>, Christina Tabet
(cstabet (AT) hotmail (DOT) com) says...
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 years, terms, 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

************************************************** ********************
Sent via Fuzzy Software @ http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...

#4

What you are trying to does not sound too bad. Although the bit about
the relative start weeks adds a bit of a twist to it - I have ignored
that part for the time being as it sounds like you have figured out how
to handle that.

Setting up a calculated member like the following should work. Provided
week parameter should feeds into the MDX query either on an axis or the
where clause to give the time dimension an appropriate context.

Quote:

Sum(PeriodsToDate(Term,Time.CurrentMember.PrevMemb er) as weeks,

[Measures].[Index] )/weeks.Count

Quote:

--
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

In article <8E15123C-187E-464F-BB07-1B94DEDCF5A1 (AT) microsoft (DOT) com>,
TinaWina (AT) discussions (DOT) microsoft.com says...

Quote:

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

#5

It just occurred to me that there is another way of doing the average
index. My first suggestion was an average of weekly indexes, which is
usually incorrect. The formula below calculates the index over the full
periods to date.

Quote:

Sum(PeriodsToDate(Term,Time.CurrentMember.PrevMemb er) as weeks,

[Measures].[number of absences] )/Sum(weeks,[Measures].[total no of
students])

Quote:

--
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

In article <8E15123C-187E-464F-BB07-1B94DEDCF5A1 (AT) microsoft (DOT) com>,
TinaWina (AT) discussions (DOT) microsoft.com says...

Quote:

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

#6

Thanks

I'll try it and let you know.

"Darren Gosbell" wrote:

Quote:

It just occurred to me that there is another way of doing the average
index. My first suggestion was an average of weekly indexes, which is
usually incorrect. The formula below calculates the index over the full
periods to date.

Sum(PeriodsToDate(Term,Time.CurrentMember.PrevMemb er) as weeks,
[Measures].[number of absences] )/Sum(weeks,[Measures].[total no of
students])

--
Regards
Darren Gosbell [MCSD]
dgosbell_at_yahoo_dot_com
Blog: http://www.geekswithblogs.net/darrengosbell

In article <8E15123C-187E-464F-BB07-1B94DEDCF5A1 (AT) microsoft (DOT) com>,
TinaWina (AT) discussions (DOT) microsoft.com says...
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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

query cube at runtime

microsoft.public.sqlserver.olap microsoft.public.sqlserver.olap

query cube at runtime - 10-24-2005 , 07:40 AM

Re: query cube at runtime - 10-24-2005 , 07:51 PM

Re: query cube at runtime - 10-25-2005 , 01:28 AM

Re: query cube at runtime - 10-25-2005 , 02:55 AM

Re: query cube at runtime - 10-25-2005 , 06:22 AM

Re: query cube at runtime - 10-25-2005 , 08:19 AM