Distribute OLAP cubes as part of application setup

Question

We currently have our custom application that is being distributed with our database (SQL 2005/2008). It is an easy task, before we release a new version we just pack our database into SQL initialization scripts (these create tables and populate data). We use SQL Management studio to generate these scripts.

As a next step we would like to deploy OLAP cube (along with ETL commands made with Integration Services) that would be used to analyze the data in the original database. .

We know to create and design a cube, but I do not even know how could be generalize all these packages and deploy them as a solution, script or something that our customers could install on their servers. Customers do not have a Visual studio and we need to create "something" in a wizard (with some input required from customer e.g. OLAP cube name, server etc) for them to deploy it.

How do you do that?

Answer 1

From Here:

Microsoft SQL Server 2005 Analysis Services (SSAS) provides three tools for deploying an Analysis Services database onto an Analysis Services server in the production environment:

• Using an XML Script Use SQL Server Management Studio to generate an XML
  script of the metadata of an existing Analysis Services database, and then
  run that script on another server to
  recreate the initial database.
• Using the Analysis Services Deployment Wizard Use the Analysis
  Services Deployment Wizard to use the XMLA output files generated by an
  Analysis Services project to deploy
  the project’s metadata to a
  destination server.
• Synchronizing Analysis Services Databases Use the Synchronize
  Database Wizard to synchronize the
  metadata and data between any two
  Analysis Services databases.

In addition to using one of the deployment tools, you can deploy Analysis Services by using the backup and restore functionality. For more information, see Backing Up and Restoring an Analysis Services Database.

The Analysis Services Deployment Wizard can be found in your start menu under SQL 2005, Analysis Services, Deployment Wizard. This takes the asdatabase file in your bin directory and creates an XMLA script that creates the SSAS database.

Links:

Using the Analysis Services Deployment Wizard

Readme for Ascmd Command-line Utility Sample

Answer 2

Or alternatively, you can use a tool to build the Cubes and Schemas that provide a simple mechanism for deploying initial implementations and a smooth upgrade path.

As you know deployment, isn't just a case of implementing a database even an OLAP database in the target environment. There's also the ETL, and tables to consider, which also involves ensuring that at every step of the way you're creating table/SQL scripts, and all this is fine and dandy until you come to provide an upgrade to your product, and need to upgrade the SSIS/DW Relational Schema Tables and SSAS Cube structures.

What you find is MS is no help at all here. It's helpful for initial deployments, but doesn't provide much in the way of in situ upgrades.

This is a problem that we have faced up to and developed a tool to address, so that we're able to do the things that you are trying to do, but do them smoothly. Leaving our technicians to focus on building high quality Data Warehouses, rather than technologies to do mundane, annoying, fraught with danger but necessary things like "upgrades".

Check out http://www.dataacademy.com, this is the product we've developed to do successfully, just what you are trying to do. Drop me a mail, if you'd like to discuss further.

Cheers and the best of luck.