Neal Waterstreet

Thoughts on SSAS Tabular

by Leave a Comment

I recently wrapped up a project using some of the new(ish) Microsoft tools: SSAS Tabular, Power View and PowerPivot Power Pivot. The project still fresh on my mind, so I wanted to document my experience and offer some praise and criticism.

SSAS Tabular/Power Pivot

I really like the Tabular Model and find it easy to work with. The speed and flexibility of the tools are a nice change from multidimensional. The security model, which could be a problem in multidimensional, is much simpler to implement and modify.

One complaint about the GUI is that it can sometimes be difficult to manage once you get into more complex measures that contain a large number of calculations. I don’t really have a suggestion for how to resolve this, but it would be helpful if it were a little cleaner. It can really get confusing when the development is shared between multiple users.

The ability to import a SSAS Tabular model directly into PowerPivot is needed. Power Pivot can be used as a source, but it does not work the other way around. This would be a great help, especially for business users that are may be trying to learn or want to gain more insight. Marco Russo has created a Connect request for it. Please vote for it if you agree.

I really like the tool’s ability to predict relationships and the Diagram view and think it is a great idea. The only problem is that it is often wrong. After a while you can usually predict when there will be an issue, but I’m not sure how much time it actually saves.

The error messages really need some work. Many of the messages I received did not seem to be related to the underlying issue. Several of these turned out to be just bugs, like duplicate measure names or issues with data source connections. None of them really gave any indication of the real problem. Luckily, most of the messages I encountered were resolved after a search. It would be nice to see a repository for these somewhere.

Power View

The business users like it and so do I, but there are a couple of items I would like to see added on.

There is a need to be able to use expressions in Power View. A very common request is to show the data for yesterday or today. There are workarounds, but there are limitations to this that limit interactivity.

More attention needs to be paid to look and feel including support for HTML5. There is currently not enough control over fonts and colors. This seems to be one of the first issues that users notice and in some companies this is critical.

The ability to export or save to another format is greatly needed. Business users want to be able to see the data in Excel – even after viewing it in Power View. The export to PowerPoint (Power Point now?) is nice, but they need access to the data itself.

DAX

The DAX syntax seems to be relatively easy to work with and is very similar to Excel functions. DAX does have limitations, especially when compared to MDX. I realize they are two different languages for different purposes, but a few new features would go a long way. There are workarounds for custom calendar support and many-to-many, but there is a need for those to be included natively. I would like to see this in the next version.

I would really like to see more functionality for DAX built into SSMS – similar to what is in DAX Studio. If you haven’t checked that out you probably should.

The biggest problem I found with DAX is the lack of examples, but this is beginning to change. In fact, there have been a couple of great documents released in the last few weeks. Alberto Ferrari released Understanding DAX Query Plans and a new article was released on Performance Tuning of Tabular Models in SQL Server 2012 Analysis Services. If you are interested in DAX, both of these are well worth your time to read. That being said, I need to start posting more here.

Conclusion

In general, I think that this is a great start for Tabular. There is a lot here to work with already, but I look forward to seeing what happens next.

DAX: Divide By Zero Error

by Leave a Comment

Like most languages, you will probably run into the divide by zero issue at some point with DAX. I checked around and saw some different solutions people are using. The best example I found was this pattern by Kasper de Jonge.

IF([Measure 2] <> 0, [Measure 1] / [Measure 2], 0).

I also looked at the DIVIDE() function for DAX on MSDN. It has the following syntax:

DIVIDE(numerator, denominator [,alternateresult])

Much less code than before!

I ran a very informal test with 1M rows to compare the two. There doesn’t appear to be any difference in performance based on my test, however I should do it again using more data. I checked into it a little more and noticed that both appear to throw the same errors. For instance, when the numerator or denominator is text both methods result in an error.

Has anyone else come across this? There is not too much information out there about error checking in DAX.