BI Dev Studio works in two operating modes. Which mode you use is sometimes based on personal preference and sometimes on necessity. Each mode has its advantages and drawbacks, and it is important to understand the differences between them when working with the product.
Working in Immediate mode is generally a more natural experience for data mining users. When you work in Immediate mode, you are connected directly and continually to an Analysis Services server. When you open an object, such as a mining structure, you are opening the object from the server as you would expect. When you change the object and save it, the object is immediately changed on the server. In Immediate mode, your BI Dev Studio project is a link to a database on your server. In Solution Explorer, you see all objects that are currently in that database. If you close and reopen this project, you automatically reconnect to the database. If objects on the database have changed since the last time you opened the project, those changes are present when you open it again.
Although this mode is very intuitive and easy to understand, you should take some factors into consideration during use. Most importantly, it is truly immediate. If you have a working model in production and you modify and save that model, it is instantly changed and becomes unprocessed, causing any queries against that model to fail. Additionally, other users can modify objects while you have them open. You see a warning when you try to save an object if it has been modified. Saving then overwrites any changes that other users have made. Changes to objects on the server are reflected in your project only when you close and reopen the object. New or deleted objects in the database are reflected only when you close and reopen the project itself.
One huge advantage that Immediate mode has over Offline mode has to do with security. Working in Immediate mode allows you to work within the confines of a single database, allowing users with Database Administrator permissions to create and modify objects. For reasons described in the next section, using Offline mode requires the user to be a server administrator.
Getting Started in Immediate Mode
To get started in the BI Dev Studio with Immediate mode, follow these steps:
1. Launch the BI Dev Studio.
2. From the File menu, select Open/Analysis Services Database; the dialog in Figure 3.2 will appear.
3. Enter the server name and database name to which you want to connect.
4. Click OK.
You can also create new databases on servers where you have server administrator permissions by selecting Create new database and specifying the server and database names.
Figure 3.2: Dialog box to connect to database in Immediate mode
When working in Offline mode, your project contains files that are stored on your client machine. As you make modifications to objects in this environment, the changes are stored in XML format on your hard drive. The models and other objects are not created on the server until you decide to deploy them to the destination server of your choosing. This provides you as the data mining developer/analyst with the ability to design and test your models on a test server before deploying them to a live server. Also these files can be checked into a source control system to track changes in the object metadata over time and to share among a development team. You can view and edit the source code of these files by right-clicking an object in Solution Explorer and selecting View Code.
When you deploy a project, the BI Dev Studio validates the objects in your project, creates a deployment script, and sends that script to the server. The unit of deployment is the entire project, representing an entire Analysis Services database. The tools are smart enough to deploy incremental changes while you are working on a project. However, if you deploy the project to a server that has a database of the same name as the one in your project settings, or if the project is deployed from a different machine, the deployment will completely overwrite the database. Luckily, you will be warned before this situation occurs. Also, because a database is created upon deployment, you must be a server administrator to deploy a project from offline mode to the server.
Getting Started in Offline Mode
To get started in the BI Dev Studio with Offline mode, follow the following steps:
1. Launch the BI Dev Studio.
2. Select New/Project from the File menu. The New Project dialog box appears as shown in Figure 3.3.
3. Open the Other Languages node in the Project Types pane if it is not already open.
4. Select Business Intelligence Projects.
5. Select Analysis Services Project from the Templates pane.
6. Give your project a name, and click OK.
7. Select Build/Deploy to deploy your project to the server, creating your database.
Figure 3.3 New Project dialog
By default, the project will be deployed to the localhost server, that is, the server on the same machine as your tools. To change the target server, select Project/Properties to launch the Configuration Property Management dialog box. In this dialog box, you can create different deployment configurations and specify the target server and database name for each potential configuration, as illustrated in Figure 3.4.
Figure 3.4 Project Properties dialog box
The other important properties to note for deployment are the Processing Option and Deployment mode. By default, deployment automatically processes any objects that have been created or invalidated by the project changes. The Processing Option property controls this behavior. Again, by default, only the incremental changes from the tools are deployed to the server. This can result in errors if the database has been changed from another client or if a database with the same name already exists on the server. The Deployment Mode property controls this behavior.
Tip: You can set a property to change the default deployment server that is set every time you create a new offline project. To do so, go to the Options dialog box by selecting Options on the Tools menu. Drill down to Business Intelligence Designers/Analysis Services Designers/General and set the Default Target Server property to the server instance of your choice. This setting affects only new projects, so you still have to manually change any projects previously created.
Switching Project Modes
You may find yourself in one mode and need to be in the other, for example when making a quick update to a deployed project from a different machine, or when you want to save the object metadata of an operational database to source control. Luckily, it is easy and painless to make the switch. The easiest is to switch from Offline mode to Immediate mode. You simply go to the File menu, select Open/Analysis Services Database, and select the database you previously deployed. To go the other way, that is, to switch from Immediate mode to Offline mode, you create a new project and select Import Analysis Services 9.0 Database, as shown in Figure 3.3. This launches a wizard where you can specify the server and source database name and then extract the metadata into a new project. Note that you still need to set the deployment options to indicate the target server and database name as necessary.
Tip: You can tell if you are in Immediate or Offline mode by looking at the name of the project in the Solution Explorer. If you are in Immediate mode, the project name will be followed by the server instance name in parenthesis. Additionally, all window tabs will have the text (Online) after the object name.
Click here to return to the complete list of book excerpts from Chapter 3, 'Using SQL Server 2005 data mining,' from the book Data Mining with SQL Server 2005.