Project Description

SSAS Operations Templates includes SSIS packages, scripts and code samples for automating maintenance of SSAS in a production environment. Includes operations such as backing up current state of cube designs, scripting paritition creation, etc.

"I read that I should script Analysis Servcies partition creation rather than creating years of partitions in advance, but how do I do that?"

"I don't want to backup my cubes completely, but I need to have a complete backup of the designs as they are in production in case I need to restore them for any reason.  Is there an easy way to do that?"


If you're asking questions like these, this project may help you!  This project was created to consolidate various templates and scripts that help productionize your SSAS solutions.  These templates aren't necessarily perfect!  But they are a good starting point for solving these kinds of issues in your solution and will help save you time recreating the wheel. 

Templates in the Project

  1. BackupAllDatabaseDesigns.dtsx -- An SSIS package that will backup the design of every database within an SSAS instance.  This package backs-up the "empty shell" of your database (not the data).  It's purpose is to preserver at a point in time what the SSAS database design was so it can be restored if needed.  This package is self-maintaining--it backs up all database designs as .xmla scripts in the folder you specify.  Run this package in a nighly job, and if ever a database needs to be restored to that point in time, just open the .xmla file in Management Studio, run it, then execute a full process on the database.
  2. AddMissingPartitionsMonthly.dtsx -- An SSIS package that will add missing partitions to a monthly partitioning scheme.  The package is driven by variables passed into the package that control the start and stop dates for partition creation, cube name, database name, etc.  This partitioning tool is non-destructive--it adds what's missing and leaves alone what's already present.  To use this package, you'll need to make a few changes to a script task to match your cube and DW design--but the basic building blocks are here!

Hope these templates are helpful.  More to come...

Contribute 

Want to contribute your templates?  Drop me a line!

Blogs

blog.robkerr.com

 

 

Last edited Mar 6, 2011 at 4:10 AM by robkerr, version 4