Perhaps I should change the subject to "MSOLAP row limit problem". I ran an openrowset query using SQLOLEDB and I can get over the 5000 record limit. It...

The data is coming from MSOLAP (ssas 2008). When I change the filter so that less than 5000 rows are returned it works fine. Anything overthat and I...

Just an FYI for anyone in this situation. I upgraded to SSAS 2008 and my exact same query runs in seconds.

Don

I decided to try creating the null measure with scope. here is what I have:

CALCULATE;

CREATE MEMBER CURRENTCUBE.[MEASURES].[LY_TOT_QTY]

AS sum(parallelperiod([ServerTime].[Fiscal Year].[Fiscal Year],1,[ServerTime].[Fiscal Year].Currentmember),[Measures].[QTYINV]),

FORMAT_STRING = "#,#.00",...

Thanks again. I have been contemplating the hidden measure that you discussed. That may be the only way to really "fix" it. I'll update you on how...

Another thought I had. When you say "caclulating for every date" could this be due to the granularity attibute being set to "Date" on Dimension usage for the servertime...

There are only 2 1/2 years of data in the fact table. I left "

where ([ServerTime].[Fiscal Month].&[2010-04-01T00:00:00])" of of the query. I think you are probably right...

Thanks Leo. Your query was somewhat faster. About 4:30 to 5:00 minutes instead of 6 to 7. that got me thinking though. I think the current...

I have indeed tried usage based optimizaiton. Every time I run it doesn't suggest any aggregations. I think that is because instead of one long running query I...

Steve,

FYI, even without restricting the Warehouse this is a major improvement. down to 37 seconds. I can use this.

Thanks again.

Don

Steve,

Thanks again. I'll work on the Non empty behavior. Basically if all the measures are zero then the row should be suppressed, however if ANY of them are...

Steve, I have run the Aggregations based on Usage and you're right, that did not help much. The point of the reprot is to show a snapshot of...