Dynamically Add Partitions to a SSAS Cube

The environment here is SSIS ETL feeding a Fact Table. The Fact Table is pulled into SSAS as a cube and reporting services are handled there. I am on the ETL side and don't pretend to know all the processing that happens with cubes, etc.

What we are trying to accomplish is to add partitions to a Cube via the ETL processing. The partitions should be incremented by Day, i.e. 20070501, 20070502, etc.

This is currently processed manually by the Reporting developer and we are looking for an automated process to reduce errors and hand work.

I have explored the following objects: Partition Processing DataFlow Destination and did not find much documentation or examples on it's use. If you have any information on this stage, please reply with such.

The other option is the Analysis Services Processing Control Flow. I understand that we can process Analysis Server objects as part of our package. Is there a way to incorporate a Partitioning Script in this object? If so, how. Again, I did not find detailed examples on the use of this object.

If you have experience with either of these, please feel free to reply. I appreciate any and all comments.

Use the "Analysis Services Execute DDL Task". This allows you to fire XMLA statements against Analysis Services.

I can't help you with what the XMLA statement needs to be I'm afraid, you'll need to go for the Analysis Services forum for that.

You may wish to check out these two guys for assistance:

Darren Gosbel - http://geekswithblogs.net/darrengosbell/Default.aspx

Chris Webb - http://cwebbbi.spaces.live.com/

-Jamie

|||

Jamie, thanks I will explore this task...our SSAS developer is checking out how to generate the XML so I will forward these links to him. Our research was leading us toward the XMLA approach but we were lacking in how to fire it off.

Thanks again!

|||

I am also looking into this and have my xmla created and all, but when firing the task i get this error:

[Analysis Services Execute DDL Task] Error: Errors in the metadata manager. Either the cube with the ID of 'xxxx' does not exist in the database with the ID of xxxx', or the user does not have permissions to access the object.

Anyone can help with this error?

|||

Hi JOER_DK,

CUBE ID is nothing but the name of the cube you want to process from an Analysis Services Database. You can generate the code for processing a cube by right clicking the cube -> Process-> This open a window for processing -> In this window, you have a button to script out the xmla for processing.

Thanks

Subhash Subramanyam