SSAS: Membership in Multiple Roles Could Throw Unexpected Surprises

Analysis Service supports role based model to secure cube data. Data can be secured at object level, cell level (Cell Data Access) and at member level (Dimension Data Access). There are relative merits of each approach, which isn’t the topic of this post but more details can be found here. A user can be a member of more than one role and it is important to understand the behaviour of Analysis Services role permissions in such a scenario. Analysis Services role permissions are additive in nature. This means if you are a member of a role that has been granted access to an Analysis Service object; you have access to that object even if you belong to another role which denies access to the same object. In other word the least restrictive roles permissions override the most restrictive role permissions. This could throw some nasty surprises that you never expected.  

Continue Reading

Pre-Installation Options for Analysis Services in SQL Server 2008

Some time back I published a post on post-installation steps for Analysis Services. That post was written with SQL Server 2005 in mind. In SQL Server 2008 – Data Dir, Log File Dir, Temp dir and Backup dir can now be specified as a step BEFORE installation of Analysis Services. There is no need for post-installation steps in SQL Server 2008, unless you missed configuring this step during installation. Continue Reading

Minimal Post-Installation Steps for Analysis Services

installDuring installation of Analysis Services, most of the properties are configured with a default value. These properties can be reconfigured any time after installation from SQL Server Management Studio. For most properties the default values works just fine. However, there are few properties that are best reconfigured immediately after installation to prevent down-time in production environment. Continue Reading

Convert to Formulas

Excel is the first choice browser to create reports from an Analysis Service cube. The cube data is displayed as a pivot table format which is extremely convenient and flexible format to slice and dice information. Pivot tables, however can only display data that exist in the data source. There may be instances where you might need additional calculations to be displayed in your report that could be derived from existing cube measures. For e.g. having a different method of determining sales quota for a particular region, calculation of Averages and Variances, what-if analysis and so on. In these circumstances, the Convert to Formula feature of Excel comes really handy. Continue Reading

Securing Role Playing Dimensions in Analysis Services

In Analysis Services, dimension data can be secured at either Shared Dimension or Cube Dimension. A Shared Dimension is an object of Analysis Service Database which is available to all the cubes in the database. The Cube Dimension on the other hand is an object of the cube and is an instance of the Common Dimension which is available only to a specific cube
Continue Reading

Cell Data Access Vs Dimension Data Access in Analysis Services

Analysis Services security model is role based. Within a role, the cube data can be secured by either Cell data or Dimension data. In this post, I compare the relative merits of each method.
Continue Reading

Testing/Troubleshooting Analysis Service Cube Security from SQL Server Management Studio

security  Analysis Service Security model is role based and relies on Windows authentication. This could pose a challenge for development and support teams to test/troubleshoot role permissions because of sensitivities around windows password. Thankfully Microsoft SQL Server Management Studio (SSMS) provides a feature to test/troubleshoot role permissions and the best thing is – you don’t need to know the password of the windows account you are testing.    Continue Reading

Offline OLAP – Working with Disconnected Analysis Service Cube

Microsoft introduced a cool feature in Excel 2007 called Offline OLAP that enables you to work with an Analysis Services Cube in disconnected mode. The Offline OLAP feature basically snapshots a version of the cube into a local cube file which can be used by Pivot Tables. Continue Reading

Deploying Report Actions in Analysis Services

This post is an extension to a popular post Reporting Action in Analysis Services on this blog . In this post, I explain the steps to deploy a report action using Analysis Services Deployment Wizard. By using the Analysis Service Deployment Wizard, Report Actions can be deployed effortlessly without hard coding the report URLS in your Analysis Services project. Continue Reading

How to Automate Analysis Services Database Backup?

Analysis Service Database can be backed up from SQL Server Management Studio (SSMS). This process is pretty much UI based. In this post, I explain two different techniques to automate the backup of an Analysis Service Database. Continue Reading…