Hi everyone,

I am very new to MDX.

There is [Time] dimension, with YEAR, MONTH, DAY three levels.

1. How do I query result from the fact table only include most 80 days
record.

SELECT NONEMPTYcrossJoin([Product].Members,
[Region].Members)
ON COLUMNS
FROM ProductCube

2. Can I retrieve data from one particular Partition ? If I can, what's
syntax ? I have a partition in the cube has only 80 days record.

FROM. CubeName.PartitionName DOES NOT WORK.

Thanks in advance !

MiMi

By the way, the time dimension is built basing on TIME FIELD (data type is
date) in the fact table, not seperate time table. So there is no seperate
base table for the time dimension. I can not create VIEW of the base table
to fill out the time I do not need. Also I can not retrieve different time
value from the fact table to create a seperate base table for time dimension
due to huge amount of data in the fact table.

But I have a seperate partition in the cube for 80 days record.

Any suggestion.

Thanks.

"MiMi" <pengpengliu (AT) yahoo (DOT) com> wrote

first, you can't access a particular partition.

So, creating a particular partition doesn't help you.
create a special cube instead-of a partition based on these 80days.

Have you tried the "TopCount" function?
TopCount([Dates].[Days].members,80, Measures.Sales)

This function return a set of dates.


"MiMi" <pengpengliu (AT) yahoo (DOT) com> a écrit dans le message de news:
%237BsdbUyEHA.2892 (AT) TK2MSFTNGP14 (DOT) phx.gbl...

Thanks for pointing me to the function. The origianl fact table has huge
amount of data. I can not create a seperate base table basing on it. It
takes too long to run the query to create seperate table only with 80 days
records in it.

I use the TOPCOUNT in my following query, it return the individual 80 days
measurement. How do I get total measurement of 80 days instead of displaying
individual day's result ?

SELECT NONEMPTYcrossJoin([Product].Members,
[Region].Members) ON COLUMNS,
TopCount([Dates].[Days].members,80) ON ROWS
FROM ProductCube

Any suggestion ?

Thanks,

"Jéjé" <willgart (AT) BBBhotmailAAA (DOT) com> wrote

try:
sum(topcount([Dates].[Days].members,80, measures.sales), measures.sales)

which display the sum of the top 80 days.

To improove the performance, in your cube
create a named set which return the 80 top days:
Top80days = topcount([Dates].[Days].members,80, measures.sales

then use this named set in your sum:
sum(top80days, measures.sales)

you can create a calculated measure:
Top 80 Days Sales = sum(top80days, measures.sales)

"MiMi" <pengpengliu (AT) yahoo (DOT) com> a écrit dans le message de news:
usujTixyEHA.824 (AT) TK2MSFTNGP11 (DOT) phx.gbl...