OWC scans all partitions (SSAS 2005)

Hello,

I have a cube with three partitions. Each partition holds data for the years 2005,2006 and 2007 respectively. In our web application we are creating OWC Pivot Table and putting "date dimension" in the filter area automatically. Altough only "2007-01-01" is included in filter, when we trace the query from profiler we saw that all partitions are read. Is there anyone encountered the same thing?

By the way, storage modes for all partitions are MOLAP.

Nilgun Celikok

Hi Nilgun,

First of all, have you set the Slice property on your partitions? Although in general AS is able to detect what data is in each partition, read the following article for details on when it will and won't work:

http://blogs.msdn.com/sqlcat/archive/2007/03/05/ssas-partition-slicing.aspx

In your case it sounds like it will be quite straightforward to set the slice, and this will also detect whether your partitions hold the data you think they hold (which could be another reason why you're seeing AS scan all partitions).

If you have set the slice, however, you could be running into an issue I've seen before and which I think is fixed in the latest cumulative hotfix build for SP2:

http://support.microsoft.com/kb/936305

Look for the brief description under issue 50000890.

HTH,

Chris

Overlapping partitions and join filters

I am using Sql 2005 and merge replication with push subscriptions. I have several dynamic join filters on some of my tables.

The join filters all use a central table that maps say a server location name (something that is returned from HOTNAME() in my case) to an for a store branch ID. This is a retail system database.

When I add a new new subscription I update this table with the new server location name and it's corresponding branch ID. My filtered tables all have a foreign key in them that is the branch ID. I can then effectively join from the server location name to a Branch ID.

What I have noticed is that if I update one row in the map table, sql server will re-generate all partitioned rows for all subscribers, even for rows that haven't been updated.

The net result is that when I add a subscription, my existing subscriptions all get about 52,000 row updates.

Am I seeing this because I said my partitions will overlap when I created the table articles?

Thanks for any help

Graham

Graham, you always ask the toughest questions

Are you "updating" or "inserting" the central table? If you're updating, are you moving rows from one partition to another?

|||

Hi Greg,

I am updating the central table. The data is not moving partitions either. I see it even when I update the talb eon a row that doesn't have a partition yet, i.e. there are no subscriptions that would cause a match in the filter.

Graham