Sql – Distribute OLAP cubes as part of application setup

olapsql serversql-server-2005

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?

Best Answer

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

Related Topic