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…

AMO Wrappers for Securing Analysis Service Cube


UPDATE: This post has been superseded by SSAS: Using AMO to Secure Analysis Service Cube. Updates include new methods specifically those related to Dimension Data Security and all methods are now in C#

Analysis Management Objects (AMO) allows you to programmatically interact with the object model of Analysis Services. The AMO layer interacts with Analysis Services by issuing XMLA commands. In this post I will explain the steps to automate the process of securing an Analysis Service Cube by building wrappers around AMO objects. Continue Reading

Reporting Action in Analysis Services

Analysis Services allows cube browsers like Excel to fire events which are handled by the Actions defined in the cube. In this blog post, I will explain a specific type of cube action called Reporting Action.  Reporting Action enables information consumers to fire a SQL Server Report when a specific condition is met while browsing the cube. Continue Reading

How to Clone an Excel PivotTable?

Ever came across a situation where you have to compare PivotTable data from same data source but from different environments, for e.g. to compare data between Test and Production environment?  In this post I’ll explain a nifty trick that helps to clone an existing Excel PivotTable and change its data source without having to create the clone PivotTable from scratch. Continue Reading…


IgnoreUnrelatedDimensions is a property of Measure Groups. By default its value is TRUE. So what does that mean? Continue Reading …

Analysis Services Cube Concepts – Made Simple

This post defines the basic terminology of Analysis Services Cube from an Information Consumer/Analyst perspective. Continue Reading …

Comparison of KPI Types in SharePoint Server 2007

SharePoint Server 2007 offers five different methods to create Key Performance Indicators (KPIs) 

  1. Using data from published Microsoft Excel 2007 spreadsheet.
  2. Using KPIs defined in SQL Analysis Service Cube.
  3. Using dashboard created in PerformancePoint 2007.
  4. Using data from SharePoint list.
  5. Using manual data entry.

This post compares the relative merits of methods 1-3 and suggests scenarios where a particular method is suitable over others. Continue Reading …