CODEWITHSUNDEEP
sql-serversql-server-2005olap
Anne
Anne

Reputation: 407

Distribute OLAP cubes as part of application setup

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?

Upvotes: 2

Views: 1830

Answers (2)

Rob Davenport
Rob Davenport

Reputation: 61

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.

Upvotes: 2

Mitch Wheat
Mitch Wheat

Reputation: 300769

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

Upvotes: 4

Related Questions