April 2014 Entries

Implementing Column Security with #SSAS Tabular and #DAX
Out of the box Analysis Services (both Tabular and Multi-dimensional) has great support for horizontal or row based security. An example of this is where you would give User1 access to all data where the Country is “Australia” and give User2 access to all data where the country = “United States”. This covers a large percentage of the security requirements that most people have. But neither technology has great support for vertical or column based security. This sort of requirement is most common ......

Posted On Tuesday, April 22, 2014 11:20 PM | Comments (19)

Running MDX Studio against SQL 2012
Even though MDX Studio has not been updated since SQL 2008 it’s still a fantastic tool for working with MDX. However if you have only installed SQL 2012 (or later) on your machine then you may get errors like the following: System.IO.FileNotFoundExcep... Could not load file or assembly 'Microsoft.AnalysisServices, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080... or one of its dependencies. The system cannot find the file specified. File name: 'Microsoft.AnalysisServices, Version=10.0.0.0, ......

Posted On Thursday, April 17, 2014 6:19 AM | Comments (1)

#DAX – Joining to a Slowly Changing Dimension
The following is one of the scenarios that I showed during my “Drop your DAX” talk at SQL Saturday #296 in Melbourne. Currently SSAS Tabular and PowerPivot models can only have a relationship based on a single column. So what do you do when you need to join based on multiple columns? Ideally you would solve this during your ETL. With a type 2 slowly changing dimension you typically want to insert the surrogate key for the dimension into the fact table. As you may know, “type 2” dimensions can have ......

Posted On Wednesday, April 9, 2014 7:18 AM | Comments (4)