SSAS: What could be wrong if you get – ‘A fact relationship cannot be defined’ ?

‘A fact relationship cannot be defined. The dimension and measure group must be based on the same table in the same data source view.’

If you get the above message when defining the relationship type as Fact between the measure group and Degenerate/Fact dimension, what could possibly go wrong especially when the second part of the message clearly tells what needs to happen? Continue Reading

SSAS: AMO Coding Tips

Some coding tips when using Analysis Management Objects (AMO) methods
Continue Reading

SSAS: Using AMO to Secure Analysis Service Cube

Analysis Management Objects (AMO) opens up the object model of SSAS cube for programming. With a bit of .NET programing, AMO can be effectively used to automate administration tasks. Securing the cube is one such task that can be easily automated using AMO. The rest of this post explains how to automate cube security using AMO. Continue Reading

SSAS: Consider Cube Browsing when Building Role Playing Dimensions

A Role Playing Dimensions reuses its definition to represent same concept in different contexts. Common example of Role Playing Dimension is date dimension. For instance, date dimension can be used to represent order date, delivery date and shipment date. Currency, staff, organisation hierarchy are other dimensions that are usually role played. While Role Playing Dimensions promote dimension reuse and share dimension data security, the effect on cube browsing must also be considered. Continue Reading

Analysis Service (SSAS) Vs. PowerPivot

This post compares Analysis Services and PowerPivot. Continue Reading…

SSAS: Cube Deployment Issues with Deployment Wizard addressed in SQL Server 2008

If you recently upgraded to SQL Server 2008 or considering an upgrade, you would be glad to know that the Deployment Wizard of Analysis Services works in the way it should (long overdue). In earlier versions, if a new version of the Analysis Service database was deployed over an one, immediately after deployment all the roles would have no permission to the cube even if “retain role and members” option is chosen. Not only that, the role definition especially Dimension Data and Cell Data Access permissions would be lost after deployment. The only saving grace was the role membership remained intact. Continue Reading