Analysis Services

Microsoft Analysis Services
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 (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 (2)

How to build your own SSAS Resource Governor with PowerShell
A few weeks ago I posted a way to manually find and kill long running SSAS queries. In this post I’ll take it a step further and show you an automated solution. The idea behind this technique is inspired by a blog post Chris Webb did some years ago. Chris implemented his solution using SSIS while this version uses PowerShell. You might ask - why create a Powershell version? Well it does a little bit more in that it logs the cancelled queries and sends the user an email. It also uses membership in ......

Posted On Monday, March 31, 2014 6:04 AM | Comments (0)

SSAS – Listing Active Queries with PowerShell
Recently one of our production Tabular servers was being hit with a number of extremely large queries that were causing excessive load and impacting the performance for other users. These queries were pretty much attempts at extracting detail level information. Some of these were due to the fact that some of the users had their own alternate hierarchy which we had not been informed of, so we were able to extend the cube design to rectify this. Other users were trying to build 10+ page reports in ......

Posted On Monday, March 3, 2014 7:20 AM | Comments (2)

SSAS: Clearing the MDX Script for a session in SSMS
Sometimes when troubleshooting performance issues you may want to comment out the MDX Script in your cube in order to quickly isolate whether the MDX Script is a significant contributor to the issue. So if you can reproduce your issue in your dev environment you can open up your project in BIDS / SSDT, comment out the script and re-deploy. But what happens if you can't reproduce the issue against dev. You may not have the same server specs, or the same data volumes or you may even have design changes ......

Posted On Saturday, February 2, 2013 8:52 AM | Comments (0)

An Analysis Services 2008 nugget
I was experimenting with some of the PerformancePoint APIs today, but I was doing something wrong as I kept getting ERROR! back. I assumed that some of the parameters that I was using was resulting in invalid MDX being generated. I was running against a test database on SSAS 2005 and as I suspected there was a syntax error in the MDX. Unfortunately Profiler against SSAS 2005 showed me the error, but not the offending MDX, which was not much help. On a whim I decided to move my test database to SSAS ......

Posted On Tuesday, May 12, 2009 11:18 PM | Comments (0)

MDX equivalent of a filtered GROUP BY in SQL
Does that title make sense? I don't know if it does, but I can't think of another description for this problem. If anyone can think of a better title I would love to hear it. It's hard to explain in words so let's jump into some code examples. Consider the following SQL statement against the AdventureWorksDW relational database. The requirement is to select a list of 4 cities and then want to see the order quantity grouped at the country level. SELECT g.EnglishCountryRegionName Country ,sum(OrderQuantity) ......

Posted On Wednesday, January 28, 2009 6:54 AM | Comments (21)

SSAS: Are my Aggregations processed?
You have designed Aggregations for your cube, but how do you know that they are currently processed? Hopefully you have your processing routines setup in production so that your indexes are always kept processed. But maybe you are working in a development environment or you are performance tuning that you want to double check that your aggregations are currently processed. It is not immediately obvious how you can figure if the indexes for a partition or a set of partitions are processed as this ......

Posted On Tuesday, December 2, 2008 10:17 PM | Comments (6)

SSAS: Executing MDX Scope statements using SSMS
So usually when I want to test an MDX expression in SSMS I'll write a query with a "WITH MEMBER…" clause to create a query scope calculated measure. But sometimes you may want to test a scoped assignment before putting it in your cube script. The following steps show you how to do this. 1. Click on the button to open an new MDX window, enter your server name and then click on the "Options >>" button 2. You must then specify the database that you are using 3. Then under the "Additional Connection ......

Posted On Wednesday, December 19, 2012 6:52 PM | Comments (5)

SQL PASS Summit & MVP Deepdives Volume 2
I'm currently in Seattle enjoying the start of some of the activities around the SQL PASS Summit. One of the activities that I'm looking forward to is the book signing session for the MVP Deepdives Volume 2 at lunchtime on Wednesday. I was fortunate to be one of the 60 or so authors this time around with a chapter on using Powershell to manipulate Analysis Services databases. All of the proceeds from this book go to support Operation Smile. You'll find the book's website here: http://www.manning.com/dela... ......

Posted On Tuesday, October 11, 2011 9:24 AM | Comments (1)

Full Analysis Services Archive