Analysis Services

Microsoft Analysis Services
The case of the vanishing KPIs
I was contacted today with an interesting issue, we had a tabular model that had some KPIs which were not showing up in Power View. The first thing I checked was the version setting on the model. KPI support was not added to tabular models in SP1. If your model is set to a compatibility version of RTM (1100) Power View will detect this and will effectively not ask for metadata about the KPIs. However in this case when we checked the database properties from SSMS the compatibility setting appeared ......

Posted On Thursday, July 3, 2014 7:33 AM | Comments (0)

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 (2)

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 (7)

#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)

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)

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)

I agree - It really is all about MDX (baby)
Nick Barclay did an interesting post recently, advocating learning MDX if you are going to be working with Performance Point Server. This is due to the fact that Performance Point builds SSAS cubes on the back end and SSAS which lives and breathes MDX. So the same advice really also extends to anyone using SSAS. You really need at least one MDX guru on your team - someone that can write MDX select statements, if you want to get the most out of SSAS. A couple of resources that Nick did not mention ......

Posted On Friday, July 27, 2007 10:24 AM | Comments (0)

Full Analysis Services Archive