the following query is working fine with sales cube:
member [Measures].[RecentDay] as
'sum(tail(filter([Newtm].[The Date].members, ([Measures].
[storecount] <>null)), 1), [Measures].[storecount])'
In my Orders cube.Its having huge data..so when i run the
abv query as
with
member [Measures].[Last7Days] as
'sum(tail(filter([OE Date].[Std].[Date].members,
([Measures].[orderQt] <>null)), 7), [Measures].[orderQt])'
select {Measures].[Last7Days]} on columns
It's showing the data for last7days..
But when i Include the [Customer].[customername].members
as rows and add to the abv qry its getting hangged..why??
Actually I want sum(Last 7 days no of orders placed by the
customer).so abv qry is right way?? Here Orderqt is the
measure created that is the count of so qt measure in
the cube..Can u help??

Hi Naveen:

I can’t say why your query is hanging when you add the [Customer].[customername].members on the ROW axis without getting a copy of your cube and doing some investigating. Your MDX is valid, even simple, so the “hang” is probably due to the filter you’re using in the calculated member [Last7Days]. The filter can increase the query time because it is evaluated “on the fly” for every tuple the query contains. When you include the [Customer].[customername].members set on the ROWS – if there are a few thousand members then it could take a long time to return. I bet your query is not “hanging”, but simply taking a long time to evaluate. If you waited long enough it would probably return, but I’m sure that’s not practical.

Since I suspect the FILTER() is causing the delay we need to ask is it really necessary? You sat that you really want the sum of the last 7 days – right? If that’s the case you should be able to use the TAIL function without including the FILTER(). The FILTER you’re applying is really letting you get a sum for the last 7 days that have data. Does your time dimension (at the day level) have future members where the data is empty? If so, then you probably need to keep the FILTER(). If not, then you can take the FILTER out and simply use the TAIL() function.

Here’s a couple of sample that works on the Foodmart 2000 [Sales] cube. I had to use the Month level because there is not a day level in the time dimension of the [Sales] cube. You should be able to use these samples directly against the Foodmart 2000 [Sales] cube. I also used 4 months instead of 7 days like your sample.

Sample (1) – Doesn’t use the FILTER. This works assuming the time dimension leaf level only goes up to current time member you have data for. When applied against the Foodmart 2000 [Sales] cube the SUM() is empty because the most recent 4 months don’t have data for the [Sales Count]

WITH
MEMBER [Measures].[Last4Mos] as
'SUM(TAIL( [Time].[Month].Members, 4), [Measures].[Sales Count])'
SELECT
{ [Measures].[Last4Mos] } ON COLUMNS ,
{ [Customers].[Name].Members } ON ROWS
FROM [Sales]

Sample (2) – Applies the FILTER. When applied against the Foodmart 2000 [Sales] cube the SUM() works because the FILTER gets the most recent 4 months that have data for the [Sales Count] – in this case back in 1997.

WITH
MEMBER [Measures].[Last4Mos] as
'SUM(TAIL( FILTER( {[Time].[Month].Members}, [Measures].[Sales Count] <> 0), 4), [Measures].[Sales Count])'
SELECT
{ [Measures].[Last4Mos] } ON COLUMNS ,
{ [Customers].[Name].Members } ON ROWS
FROM [Sales]