Partitions and Slices without query binding

I have a fairly straightforward cube in SQL 2005 SSAS, with date as one

of the dimensions. I created a partition for each year and

quarter, and set the slice appropriately. However, in order to

create the partition, I had to use a source query, because I got an

error about re-using the same table. I then created a single

partition that was based off the table and set a slice and reviewed the

SQL that SSAS issued, and it did not restrict based on the slice.

My question is: Can you make SSAS (2005) work like SQL 2000 where

it automatically generated (albeit not perfectly) a where clause to

restrict the partition?

Thanks,

Doug

Hi Doug,

The way I got this to work is by using AMO to clone a base partition, and then update the underlying view (again in AMO by connecting to a SQL Server). So before the partition is processed, the view is updated.

I used Script Task in SSIS to do the AMO.

This is a nice generic solution.

Hope it helps.

Rahil

|||

Rahil,

Any reason that you didn't use the Analysis Services DDL Task in SSIS?

-Jamie

|||

Hi Jamie:

The AS DDL Task in SSIS will not dynamically create a new partition - whereas in the AMO or XMLA script you can achieve that. I guess - that you could argue that we can break that up into two steps and do the partition creation in code and then subsequent processing in the DDL Task.

Thanks.

Suranjan