Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Managing Analysis Services Deployment - Part 1

By Shahfaisal Muhammed,

Once you have finished the development of Analysis Services project, there are several methods available for deploying analysis services objects from one server to other server. In this series, we will discuss the most commonly used deployment methods one by one. This article covers deployment using BIDS. The entire series covers deployment using:

Deployment with Business Intelligence Development Studio (BIDS)

This is where you will create most of the Analysis Services objects such as cubes, dimensions, partitions, measure groups etc.  In BIDS, there are again two different ways to deploy Analysis Services objects:

Using project mode

In this mode you will be using an offline project file to make changes to SSAS objects. To work in this mode, go to File -> Open -> Project/Solution as shown in the figure below and then navigate to the location where your project file is stored and click ok.

Open project

Figure 1

After making the required changes, you can deploy those changes to the target server by right-clicking the project name in solution explorer and selecting Deploy as shown below in figure 2. BIDS will detect the differences between the objects in the project file and those that exist on the Analysis Services server and then apply those differences to Analysis Services server.

properties

Figure 2

Deployment settings

Before deploying the project, you will need to configure the deploynment settings.  To do so, right-click the project name in solution explorer and select the properties option. The properties dialog box appears as shown below in figure 3.  

dpeloyment method

Figure 3

Let’s go through the settings/options:

Processing Option

This option dictates what type of processing is performed when the changes are deployed.  If Do Not Process option is selected, only the changes are deployed and no processing is done. If Full option is selected, analysis server will delete all the data from the objects and then processes the deployed objects. If Default option is selected, analysis server will use its intelligence to perform just enough processing to bring the cube to a fully processed state. The Default processing option will save you a lot of time and is the best based on my experience.

Transactional Deployment

This option dictates whether the project is deployed as a single transaction or as multiple transactions. If True is selected, then the deployment is performed as a single transaction – that is, if an object fails to process, then all the changes will be rolled back. If False is selected, then each object is deployed independently.

Deployment Mode

This option specifies how the changes are deployed.  If the Deploy Changes Only option is selected, only the changes are deployed to the target server. In other words, BIDS will detect the differences between the objects in the project file and those that exist on the Analysis Services server and then deploy only those differences. .  If the Deploy All option is selected, then all the objects will be deployed.

Server

This is the name of the target server.

Database

This is the name of the target database.

Using online or connected mode:

In this mode, you are directly connected to the analysis services database and as the name indicates, changes are committed as soon as the object is saved. Obviously, this method is not recommended. Please note that this mode can only be used after you have completed an initial deployment.

To work in this mode, go to File -> Open -> Analysis Services Database as shown in the figure below.

Open project

Figure 4

The Connect To Database dialog box appears as shown below in figure 5. Enter the analysis server and database name to which you want to connect as shown in the figure below. When you click OK, BIDS will create a project file under the location specified in the dialog box.

Location of files

Figure 5

This method has got an inherent problem that I wanted to address here. As the changes are deployed right after you hit the save button, objects in the Analysis Services database will become out of sync with the Analysis Services project that was used for its deployment. Let’s say that you made some quick changes to an analysis services database called FirstSSASProject using connected mode. Later you open up the analysis Services project that was used for its deployment and make some changes. When you try to deploy the changes, you will get the following error. If you select “Yes”, you will lose all the changes that you made in connected mode.

warning about overwritting database

Figure 6

This problem can be fixed by re-importing (re-engineering) the analysis services database into a new project file as follows:

In BIDS, go to File->New->Project, the New Project dialog box appears. If you are on SSAS 2005, you will need to select the Import Analysis Services 9.0 database template and if you are on SSAS 2008, you need to select Analysis Services 2008 database template as shown in the figure 8 below and click ok.

New project.

Figure 8

 

This will launch the Import Analysis Services Database Wizard. At the welcome dialog box, click next. The source database dialog box appears next as shown below in figure 9. Type in the name of the Analysis server and the database you want to import into new project file, and click next.

Soruce database

Figure 9

The wizard will now read all the analysis services objects form the database as shown in the figure below and will create a new project file. Click finish and you are done.

Complete the Wizard

Figure 10

TIP: In BIDS, you can visually identify which mode you are working in by just looking at the tab name you are in as shown in the two figures below.

BIDS deployment 11

Figure 11

BIDSs deployment

Figure 12

When to use BIDS

Use this method when you need to deploy an analysis services database to your local server (development or Test server) or when you want to deploy everything in the AS database (Partitions, Security etc). You are less likely to use this method when promoting your AS database from a Test server to Production server. Why? Because in most of the shops, you will find that security is QUITE different in Test and Production servers and unfortunately, BIDS provides no granularity and overwrites everything on the destination server.

Total article views: 7064 | Views in the last 30 days: 25
 
Related Articles
ARTICLE

Rethinking Analysis Services

Integrate SQL 2005, ASP.NET, Reporting Service and Analysis Services to rapidly develop and deploy a...

BLOG

X-XMLA: iv. Deploying Databases with XMLA

The fourth segment in this series focuses on using XMLA to deploy databases.  When you create SQL Se...

FORUM

Analysis Services deployment wizard

Analysis Services deployment wizard

BLOG

Creating Analysis Services Partitions Using BIDS

As your database grows in size, Analysis Services cubes that use that database grow along with it. A...

FORUM

Analysis Services database RESTORE issue

I am not able to restore Analysis Services database backup due to an error

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones