Analysis Services

Microsoft Analysis Services
SSAS Tabular–per table LastProcessed dates
I saw a question yesterday on the MSDN forums ask how to go about exposing the LastProcessed datetime per table. Marco has a great post here (https://www.sqlbi.com/arti... about a number of ways to get the last processed date at the database level. But none of these techniques work at the table level. Having a look through the various DMVs that are available and none of them seemed to have this information apart from DISCOVER_XML_METADAT which returns a large ......

Posted On Wednesday, May 13, 2015 9:52 PM | Comments (0)

DAX Studio 2.1 Released
Today I am pleased to announce the release of the latest update to DAX Studio – v2.1.0 You can get it from the releases page on codeplex: http://daxstudio.codeplex.c... Below is an outline of what’s new in 2.1. A big thanks to Daniele Perilli for his assistance with the graphics and Marco Russo for his work on the Query Plans and Server Timings tabs plus his help with testing this release. UI Refresh Thanks to assistance from Daniele Perilli on the graphics side we now have a lot more consistency ......

Posted On Wednesday, March 18, 2015 7:17 AM | Comments (4)

The perils of calculating an Average of Averages
I've seen questions around issues calculating averages come up a few times in various forums and it came up again last week and I feel that there is some benefit in walking through the details of this issue. For many of you the following will be nothing new, but I'm hoping that this may serve as a reference that you can point to when you get requests for this sort of calculation. The core issue here is really a fundamental mathematical one. Personally I see it surfacing most often in DAX and MDX ......

Posted On Monday, July 28, 2014 7:18 AM | 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)

SSAS: Listing Attribute Relationships
Occasionally questions come up about how to extract certain pieces of metadata from Analysis Services. In general all the metadata that you would need on a day to day basis is pretty well covered by the standard schema rowsets. And in SSAS 2008 you can use the system DMVs to get at most of this data. For example, if you want to get a list of the current user sessions on the server you can do the following... SELECT * FROM $System.DISCOVER_SESSIONS ...and in SSAS 2005 you can use the same syntax with ......

Posted On Wednesday, November 26, 2008 10:58 PM | Comments (5)

Teaser: SSAS Cache Warming with PowerShell
You may be thinking oh-no, not another one. Allan Mitchell recently posted an example of an SSIS package that would warm the SSAS cache which is an updated version with a slightly simpler SSIS data flow from something Chris Webb originally blogged. Being a PowerShell fan as I read Allan's post I realised that most of the tasks mapped to native PowerShell cmdlets and I already had PowerShell code to execute an MDX command, so all I was missing was someway of reading in the trace data. What I ended ......

Posted On Sunday, November 16, 2008 11:41 PM | Comments (5)

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

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)

Full Analysis Services Archive