Microsoft Analysis Services (MSAS) relies on Windows accounts for granting access to cube data as well as for administrative tasks such as processing cubes, altering server-wide configuration settings and modifying dimensional objects.
Before now, you could define MSAS roles at the database or cube level; however, there was no concept of Analysis Services logins, nor could you use SQL Server logins -- MSAS role members must be Windows accounts or groups. Although the Analysis Services security model hasn't changed, Microsoft has resolved some critical security limitations with its latest version, MSAS 2005. In this tip I sum up the security weaknesses in MSAS 2000 and the upgrades in MSAS 2005.
MSAS 2000 security challenges
One common complaint with MSAS 2000 is limited flexibility for defining administrative roles. Once you install the software, a Windows group called OLAP Administrators is added to the server and members of this group can perform any operation against the analysis server. There is no way to create a role that can only process cubes or create objects in a given cube. Therefore, you must grant unlimited MSAS permissions to the administrative users.
Unlike the administrative permissions, Analysis Services 2000 allows a lot of flexibility when it comes to restricting user access to certain dimensions or specific members within each dimension. You can create roles that have read-only or read-write permissions. You can also secure each level within a dimension and even pick individual cells within a cube that the user is permitted to see. However, this flexibility comes with a caveat.
Smaller-scale MSAS installations have a handful of users or groups. You could have a group that reads each cube and perhaps several groups of privileged users who can read sensitive data from certain dimensions. But what if you're building an analytical application for a large financial institution with thousands of customers? Each customer needs access only to her own accounts and shouldn't be able to view the accounts of any other customers. Implementing such MSAS 2000 applications with a large number of roles is a considerable challenge.
When you start MSAS 2000 service, it attempts to load so called replica dimensions into memory. Replica dimensions contain a subset of dimension members that a particular cube role is permitted to see. Since each role will have at least one replica dimension, as the number of roles grows, so will the memory requirement and the time it takes to start the service. This limitation becomes increasingly severe with large dimensions that have many thousands or millions of members. The fact that you can only dedicate up to 3 GB of memory to 32-bit Analysis Services exacerbates the problem. (Normally, MSAS can only use 2 GB of memory, but you can add a switch to the boot.ini file and a Registry entry to advise MSAS to use up to 3 GB of RAM). I've seen implementations where Analysis Services simply cannot start because it lacks enough memory to load all dimensions.
Improvements with MSAS 2005
Fortunately, MSAS 2005 makes administrative permissions more granular. First, MSAS 2005 supports multiple instances of the software on a single server and each instance can have a separate administrator. The new version introduces the concept of a fixed server role, which is analogous to SQL Server's SYSADMIN server role. The fixed server role members can modify server configuration settings (the number of configuration settings increased from about a dozen in MSAS 2000 to almost 200 in MSAS 2005), manage cube data access permissions, run Profiler traces against the server and create databases. Cube structures will no longer be modified directly on the analysis server -- instead you must use Business Intelligence Development Studio (BIDS).
In addition to adding Windows users to the fixed server role, you can grant the following administrative permissions at the individual database level:
- Full control -- Members of this role can perform any operation within the current database but cannot change any server-wide configuration settings.
- Process database -- Members of this role can process the current database and any underlying objects.
- Read definition -- Members of this role can read database object definitions (metadata) but cannot change them.
You can grant each Analysis Services database user permission to process the cube or individual dimensions. In addition to granting permission to view individual dimensions, members can also grant permission to view certain attribute hierarchies and not others.
You'll be glad to learn that MSAS 2005 no longer attempts to load replica dimensions into memory at startup. In fact, with this version there is no concept of replica dimensions at all -- security is implemented using bitmap indexes created on secured attributes. The memory footprint of bitmap indexes will be minimal; furthermore, the security bitmap can be read from memory or disk. With this architecture you can safely create thousands of roles without the fear that MSAS will encounter any memory issues.
Both MSAS 2000 and MSAS 2005 support 64-bit editions, which are not limited to 3 GB of memory. If you run a 64-bit implementation, the amount of memory you can dedicate to Analysis Services is likely to be limited by the amount of total memory available on your server. The upper limit of memory you could dedicate to MSAS is 64 GB if you're using the Enterprise Edition and 512 GB with the Datacenter Edition of Windows Server 2003; it should come as no surprise that such servers are quite expensive. In addition to the large amount of available memory that 64-bit implementations will enjoy, they will also have very large dimension support with Multidimensional OLAP (MOLAP). Other benefits include more parallelism in processing leading to faster cube processing times, better query response times and more.
About the author: Baya Pavliashvili is a DBA manager overseeing database operations that support more than one million users. Pavliashvili's primary areas of expertise are performance tuning, replication and data warehousing.
More on SearchSQLServer.com
Tip: Use cube partitions to improve Analysis Services performance
Tip: Adding time calculations to Analysis Services 2005 cubes
Tip: Business intelligence features in SQL Server 2005
Fast Guide: SQL Server Reporting Services
