I'd like to partition our cubes here by months and I'm trying to figure out
the best way (and least amount of work) to do so. We have about 100 million
fact rows per month and 36 months of data to process. (the data is not
partitioned)

With each partition, as I figure it, there are three different ways to
restrict the data: 1. Filter 2. Slice 3. Data view

What works for you guys?

100mil a month? That's a fair bit bigger than us, but the approach we have
taken is to create a view for each partition we intend to create in AS. The
views are essentially:

create view vw_SalesALL as
select * --no, we don't use *, but you see what I mean
from Sales

create view vw_Sales2001_Jan as
select *
from Sales
where DateField between 20010101 and 20010131

create view vw_Sales2001_Feb as
select *
from Sales
where DateField between 20010201 and 20010228

etc...

vw_SalesALL is then used as the view for the cube as a whole, and each
partition we create uses its own 'sub' view. Because we are filtering out at
the DB layer, we do NOT set filters in the paritition in AS. Not 100% sure
why, but having the filter in the view rather than the partition seems to
work faster at process time.

In each partition, you MUST set the slice - this is what is gonna tell the
query optimiser where to look for your data, and as such is the key component
to getting considerably faster query response times than with a
non-partitioned cube.

3.6billion rows... I hope you don't have to process that every night!

Matt.

"Jesse O" wrote: