Please Heeeeeeelp - Over Scan on partition

Question

I tried running a simple MDX query on my system one of them using a filtered member the other with the same member but no filters.

here is the code I wrote:

<ClearCache xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>NewOLAP</DatabaseID>
</Object>
</ClearCache> 
go
 WITH
MEMBER [Measures].[Test]
 AS ([Measures].[Black Listed],[Dim Rtb Types].[Rtb Type Id].&[7])
 
SELECT 
{ 
[Measures].[Atok Size Not Approved],  
[Measures].[Global Capped], 
[Measures].[Bid Maximized],
[Measures].[Test]
}
ON COLUMNS
,   
{[Dim Time].[Date].&[2012-01-09T00:00:00]}  on rows  
FROM [New OLAP]

go

<ClearCache xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>NewOLAP</DatabaseID>
</Object>
</ClearCache> 
go
 WITH
MEMBER [Measures].[Test]
 AS ([Measures].[Black Listed])
 
SELECT 
{ 
[Measures].[Atok Size Not Approved],  
[Measures].[Global Capped], 
[Measures].[Bid Maximized],
[Measures].[Test]
}
ON COLUMNS
,   
{[Dim Time].[Date].&[2012-01-09T00:00:00]}  on rows  
FROM [New OLAP]
 

 When I run it I encountered something strange. using the profiler I noticed that the filtered query scaned the partition twice, while the non filtered query scaned the partition only once (see attached screenshot)
 
 did anyone encounter such performance and can explain why?
 
 

Answer 1

what if you change the order of execution, do you still see the same behaviour?

Are you partitions based on dates or RTB types?

Answer 2

Yes , i still see the some behaviour,

there is 1 partition on this measuregroup (Default),

2 Dimensions Date, RTB types,

Can you check in you cube the some scenario

Ofer

Answer 3

I think since the scope of the member is different from the one used on select query there are 2 scans of paritions for filtered query.

In non filtered query, the member function is just getting the measure without applying any filter, this is same as getting the measure in select clause.

Try the following n check the scans happening. It could also be that aggregrates are being cached by Formula Engine, I'm not sure if Clearcache clears FE Cache or SE Cache..

<ClearCache xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
< Object>
< DatabaseID>NewOLAP</DatabaseID>
< /Object>
< /ClearCache>
go
WITH
MEMBER [Measures].[Test]
AS ([Measures].[Black Listed],{[Dim Time].[Date].&[2012-01-09T00:00:00]})

SELECT [Measures].[Test]
 ...

Answer 4

Sorry I did not understand you,

what should i do ?