Wednesday, December 28, 2005 8:22 PMI 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?
Friday, July 14, 2006 10:41 AM
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.
Friday, July 14, 2006 1:00 PM
Any reason that you didn't use the Analysis Services DDL Task in SSIS?
Monday, July 17, 2006 4:05 PM
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.