The following system error occurred: The file exists. . (Microsoft SQL Server 2005 Analysis Services)

This blog post has been moved to http://shahfaisalmuhammed.blogspot.com/2011/09/following-system-error-occurred-file.html

Storage Modes in Analysis Services

Storage mode in SQL Server Analysis Services(SSAS) lets you decide where you want to physically store the data(both aggregations and cube data). There are three types of storage modes:

MOLAP (Multidimensional OLAP) – This mode stores both cube data and aggregations in Analysis Services server. This is the the default storage mode in SSAS and is the fastest of the three modes in terms of query performance. However, this mode requires more disk space(to duplicate the detail-level data) and will obviously increase the cube’s processing time. Also, in this mode, once the cube is processed, the relational data source is not accessed when users query the cube and so, any changes made to the relational data source after the processing of the cube will not be reflected in the cube until the cube in processed again.

ROLAP (Relational OLAP) – Unlike MOLAP, in this mode, both cube data and aggregations are stored in the relational data source. To store aggregations, Analysis Services will create additional tables in the relational data source. When users run MDX queries, Analysis Services will generate SQL statements against the relational data source to retrieve the data requested. Since the data has to be brought over from the relational data source(which in most cases resides on a different server), this mode is the slowest of the three modes in terms of query performance but it supports real-time and is better than MOLAP in terms of storage and processing.

HOLAP (Hybrid OLAP) – This mode is a hybrid between MOLAP and ROLAP and offers the best of the two modes. This mode stores cube data in the relational data source and aggregations & indexes in Analysis Services server. You need to be careful with HOLAP because since the aggregations are on the Analysis Services server, you will get wrong results when the leaf level data(in the relational store) is updated but the cube is not processed. So it is critically important to keep aggregations and the leaf level data in sync.

Managing Analysis Services Deployment

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. The entire series covers deployment using:

* BIDS
* Analysis services Deployment Wizard
* Backup and Restore
* The Synchronize Database Wizard

BuiltinAdminsAreServerAdmins property in Analysis Services

BuiltinAdminsAreServerAdmins is a server property in Analysis Services which is set to true by default as shown in the figure below.

What this means is, all the local administrators on your windows machine are going to be server administrators for analysis services. In other words, windows local administrators will be members of analysis services built-in Administrator role. But the point to note here is that, when you look at the security page in the server properties, you will only see users who have been explicitly added to the Administrator role. For example, on my server, only the windows Administrator is explicitly added to the analysis services built-in Administrator role as shown in the figure below.

However, I have a couple of users who are members of the windows local administrators group as shown below. So in my case, users zack and GMiller will have implicit but absolute control over analysis services.

This is a bit risky, especially on a production server. I recommend turning this property to false to prevent unwanted users from administering analysis services. Hang on, before you go and change this property to false, please note that when you change the BuiltinAdminsAreServerAdmins to false, all windows local administrators will loose admin privileges to the analysis services server. However, if you have explicitly added a user to the analysis services built-in Administrator role, those users will still continue to have admin rights.

Creating SSRS Report against Analysis Services Cube

This blog post has been moved to http://shahfaisalmuhammed.blogspot.com/2011/09/creating-ssrs-report-against-analysis.html

Thanks!