We have so far been using the BI Development Studio to develop the project and deploy it to the server. After the database has been deployed, the emphasis switches from developing a solution to managing the deployed database, and you will be using the same SQL Server Management Studio that you used to manage SQL Server databases.
To connect to an Analysis Services database from SQL Server Management Studio, select Connect Object Explorer from the File menu, choose Analysis Services as the server type, and select the server name before clicking the Connect button.
As you have seen while developing the cube in the "Technical Solution" section, you can deploy an Analysis Services database directly from within the BI Development Studio. This proves to be very useful during the development phase; when you need to move the database to a test or production environment, however, a more structured approach is required.
Using the Analysis Services Deployment Wizard
Analysis Services 2005 includes a Deployment Wizard that can either interactively deploy the database to a specified server or create a deployment script that can then be executed on the target server using SQL Server Management Studio. You can access the Deployment Wizard from the Start menu, under the Microsoft SQL Server 2005, Analysis Services folder (see Figure 5-12). The source file that the wizard uses is a file with an .asdatabase extension that is created in your project's bin folder when you build the project in BI Development Studio.
Figure 5-12. Deployment Wizard
The wizard also gives you control over the configuration settings for your Analysis Services database. For example, if you are deploying to a production server that uses a different database from development, you can specify a different data source connection string and impersonation setting for the deployment. You can also choose not to overwrite any settings such as role members or connection strings (in case these have been modified on the target server by the system administrator).
That takes care of getting your new database definitions on to the target server, but what about initially loading the data? The wizard also enables you to specify that objects must be processed after they have been deployed, including an option to use a single transaction for processing objects and roll back all changes if any errors are encountered.
Managing Security for Administrators
Analysis Services server administrators can perform any task on the server including creating or deleting databases and other objects, administering roles and user permissions, and can also read all the information in all the databases on the server. By default, all members of the server's Administrators group are Analysis Services server administrators, and you can also grant additional groups or individual users server administration rights by right-clicking the server in SQL Server Management Studio, selecting Properties, and then adding them to the server role in the Security section.
You can change the default behavior that grants local administrators full Analysis Services administration rights by changing the BuiltInAdminsAreServerAdmins setting to False, but this is not a worthwhile exercise because this setting is stored in the configuration file for Analysis Services, which local administrators would probably have access to anyway. Also, because you can actually have multiple Analysis Services instances on a single server, technically speaking a server administrator is actually an "instance administrator" because the permission is granted for an instance, not necessarily the whole server.
If you have an environment where you want to grant administrative privileges only to specific Analysis Services databases rather than all of them, you can create a role in a relevant database and select the Full Control (Administrator) permission, as shown in Figure 5-13. The groups or specific users who need to administer that database can then be added to the role. Database administrators can perform tasks such as processing the database or adding users to roles.
Figure 5-13. Creating a database administration role
If you add roles to a database using SQL Server Management Studio, you need to be aware that they will get overwritten if you redeploy the project from BI Development Studio. You must either add the roles to the project in BI Development Studio so that they always exist, or deploy the project using the Deployment Wizard with either the "Deploy roles and retain members" or the "Retain roles and members" option selected. If you select "Deploy roles and members" in the wizard, any roles that you manually created using SQL Server Management Studio are removed.
Every BI solution that we have ever delivered has required some enhancements, usually soon after deployment. The reason for this is intrinsic to the way people use BI applications, in that every time you show them some interesting information, they immediately come up with a specific aspect that they would like more detail on.
Using Source Code Control
You can use the BI Development Studio to make changes to the project files and redeploy them to production using the method described in the "Deployment" section, but it is a great idea to start using a source code control system for these files. In addition to making it easier to work in a team environment, source control enables you to keep track of the versions of the solution that you have created.
Changes to the Underlying Data Sources
At some point, you are probably also going to have to make changes to the underlying source database, such as adding new columns or new fact and dimension tables. Because we have recommended that you build the Analysis Services databases from views rather than directly on the tables, this will probably provide you with some protection because you can make sure that the same schema is presented if required.
However, if you want to include these new database objects in your Analysis Services project, you must update the database views and then open the relevant DSV in BI Development Studio and choose Refresh from the Data Source View menu. The Refresh feature has a nice user interface that shows you what (if anything) has been changed in the source objects, and then fixes up the DSV to match.
One important caveat applies, however: Any objects based on the changed database object will not automatically be fixed, and you need to manually address these. For example, if you changed the name for a database column, the corresponding attribute will still have the old column name and must be updated manually.
The major operations tasks that you need to perform for Analysis Services are processing the databases and making backups.
Processing the Cube
As you have seen when using the BI Development Studio to create a cube, after Analysis Services objects have been deployed to a server, they need to be processed before users can access them. This kind of processing is known as full processing, and it will usually take some time in production systems because all the data for dimensions and facts is read from the source systems and then used to create the Analysis Services databases.
Full processing must be performed whenever you have changed the database definition in certain ways, such as adding an attribute hierarchy. In most cases, however, you only really need to load the new data for the period, such as changes to the dimension records or additional fact rows. For dimensions, you can select the Process Update option to read all the dimension records and create any new members, or update existing ones when they have changed.
Measure groups are a bit more complex because you usually only want to add the new records from the fact table, so you need to supply a way to enable Analysis Services to identify those records. The Process Incremental setting shown in Figure 5-14 enables you to either select a separate table that contains the new records you want to load or to specify an SQL query that only returns the relevant records.
Figure 5-14. Incremental processing options
Because the Analysis Services processing usually needs to be synchronized with the ETL routines that load the data warehouse, a common approach is to add an Analysis Services Processing Task into the Integration Services packages for the load process.
Backing Up the Analysis Services Database
If a disaster happens to your Analysis Services database, one way of recovering is to redeploy from the original project source code and then reprocess the database. So, a key part of your backup strategy is to always have an up-to-date backup of the solution files or the source control repository. However, reprocessing can be very time-consuming, so the usual approach is to make a backup of the Analysis Services database, too.
You can back up the database from SQL Server Management Studio by right-clicking the database and choosing Back Up. Figure 5-15 shows the options that you can specify. The backup process creates a single .abf file containing the metadata and data for your database, and this can optionally be compressed.
Figure 5-15. Backup Analysis Services database
Because these databases often contain sensitive information, you can also specify a password that is used to encrypt the file. It is worth being careful with this password, however, because you will need it to restore the backup if necessary and the file cannot be decrypted if you lose the password.
If you need to recover the database, you can restore the backup by right-clicking the Databases folder in SQL Server Management Studio and selecting Restore.