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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
Figure 11
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.
