Management Issues - SSAS 2005

Do you have management issues (SSAS 2005)?

As opposed to the Analysis Services 2000 environment, where you designed, developed, edited and maintained an Analysis Services database in one place only: the Enterprise Manager, in SSAS 2005, the situation is totally different.

Some may think this is good, some may think this is bad, but any way you think of it, you have to deal with it, otherwise, you may get into a whole lot of trouble and head into a production disaster. Sounds interesting (intimidating)? Then read on.

Using SQL Server Management Studio

There are three ways you can access an SSAS 2005 database. The first one is simply to connect to an SSAS server using the SQL Server Management Studio (SSMS).

This provides the user with almost the same experience the user had using the Enterprise Manager in previous versions, minus the development features. When you connect to a server using SSMS, you get a tree view of the databases, cubes, partitions, dimensions, mining models, roles and assemblies included in the server. You can also perform most operational activities, required from a DBA. Activities like backing up and restoring databases, processing dimensions, cubes, specific partitions and mining models, creating and managing roles and security. You can also browse the database objects and have a look at its content (dimensions and cubes), and you can query the database using MDX, manipulate database objects using XMLA and query mining models using DMX.

For anyone not familiar with any of the options described, the main point to illustrate here is that the SSMS provides the administrator and the operator with a convenient “one stop shop” for all administration and operational activities required on an SSAS database. You can add new partitions and design aggregations but you can’t view the underlying database schema, you can’t add or modify dimensions or cubes (you can delete them though) and you can’t change the database data source and/or data source view, just to name a few features which are more oriented at the developer then the administrator.

Using Business Intelligence Development Studio

This brings us to next access point: the Business Intelligence Development Studio (BIDS).

Using this Visual Studio environment, the user has two options: one is to connect to an online SSAS database. Choosing File -> Open -> Analysis Services Database will let you connect to an online SSAS server and choose a specific database. By connecting to an online database, any action the user performs in the database in this environment will actually propagate the changes to the online database immediately (once you click the ‘save’ button). In terms of development features, the user essentially has all the options available, such as creating new objects, designing aggregations, modifying data sources and data views etc.

The third option to edit an SSAS database is to import the online database to an offline SSAS project. Choosing File -> New -> Project and choosing the “Import Analysis Services Database” option will prompt you with a dialog box, asking you to choose a server and a database to import and a target destination to place the project files in. The wizard will generate all the required files for the project, and the end result is an identical database project to the online database. There is one main difference here, compared with the two previous methods described. This project is an offline project meaning any changes you perform here will not propagate to the online database (and the database users, for that matter) until you actually deploy the database onto the server.

Problems anyone?

As you can see, this may cause serious conflicts. For example, let’s say a developer imported a database and was working on the offline project for a while, making various changes to various database objects. In the meantime, the DBA, using SSMS, has created a new cube partition, to accommodate for new data for the new month/quarter/year. When the developer tries to deploy the new database to the server, he will receive a message that the online database is different from the database being deployed, and, when deployed, the database will miss the new partition, because the newly deployed database has overwritten the online database design.

Another issue which may occur is having two developers, making changes to two separate offline database projects and deploying it to the same online database. You can imagine what may happen next…

What can be done...

The first thing an organization has to do, in order to tackle the new management challenges this new environment poses, is to establish a methodology of development, the same way there is a process which controls development and deployment of any new code and/or apps to a production environment. Analysis Services developers and administrators need to realize that the era of the “one central service deals with conflicts” is over.

One approach could be to implement an “all offline” development process. Development and deployment of databases will be controlled by a source control software/mechanism. This way, only the most updated and “checked out” code will be deployed and later can be used for further development. This is quite similar to many code development environments. Even within this strict framework, the administrator, which probably does not have, and may not need access to a development environment, needs to be aware that some changes he makes on the server, using SSMS, may conflict with a future deployment (as illustrated previously). To prevent this, certain administrative operations should be coordinated with the development team and the “off line” project files are to be refreshed from the online database source.

Another approach may be to enforce a strict “online database” development environment. Developers will connect to an online database using BIDS and implement the changes “on the fly”. Administrators will still use SSMS for most operational activities. While this approach may solve some of the issues of code conflicts (when one environment tries to modify an already modified database the user will receive an error message, stating the database has been changed), it falls extremely short in terms of good development practice. Just consider implementing new database changes directly on a production database… This issue can be solved by having developers share a development online database. Once the development is completed, the development database is being synchronized with the production database (database synchronization is a subject for a separate article altogether).

Conclusion

There isn’t one way of dealing with these conflicts and resolving them, but what’s important is that in each organization a methodology should be implemented, in which strict guidelines should be enforced as to how to manage an SSAS environment.

It is important to realize the new ways of managing the SSAS 2005 environment and the various pitfalls which may pop up on the way.