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

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