Partitions and Slices without query binding

Question

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

Answer 1

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

Answer 2

Rahil,

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

-Jamie

 

Answer 3

 

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