I’m a fan of SSAS, so you should know that up front. But I’m also working with clients who (a) need to keep their data on premise (health care, banking), and who (b) are large enterprises. If I had to move those clients to a Desktop-only platform, I’d feel it was a step backward. Notice that’s how I’d feel, an important distinction from how I’d reason. Desktop, if anything, is out ahead in many respects. If you want to build an SSAS tabular model, you’ll have to use Visual Studio, an interface that’s not nearly as smooth as Desktop. But I’m getting ahead of myself, let me progress more logically.
Desktop vs. SSAS – the answer is, of course, it depends. There are millions of use cases for Desktop, and only a few, really for SSAS. But those few are important, and that’s what I’d like to convey in this blog.
You need to keep data on premise
Cloud is scary to lots of organizations, even today. Build a tabular model in SSAS, created Desktop tools that are “live connect” to the SSAS model, set up an Enterprise Gateway and you’ve got this covered.
You’re big
Big means (a) you’ll have lots of users (b) those users may need to be protected from themselves (i.e., from making up their own definitions of revenues (c) you’ll want to integrate data from lots of sources.
This all translates into the need for curated models. And not too many of them. The less models you maintain the better. If you can spin multiple Desktop tools off one model, do that. Reconciliation, consistency, minimizing DAX – all of that works in your favor. If, instead, you create Desktop models with imported data, then have to duplicate to make variations. I have a single SSAS model with one client that has three separate Desktop tools connected to it. (One trick for that, by the way, is to create dummy tables that just exist to hold the metrics that will go into one Desktop tool. Under Import, you simple write a SQL string that looks something like this: Select ‘ ‘ as dummy, ‘ ‘ as dummy1, ‘ ‘ as dummy2. then hide the dummy columns and just make the metrics topical to the tool you’ll build visible.)
Big also means you have lots of systems. Lots of systems, lots of users and high risk of error means you’ll want the classic design, which includes the all-important (and terribly named) “semantic layer.” Desktop crunches all levels of Data Warehousing into one tool – great for simple situations – but a separate semantic layer makes rigor much easier.
Source Data -> Dimensionally optimized database -> Semantic Layer -> User tools
This also means you’ll need depth of expertise in data warehousing. There is no getting around that if you’re a large enterprise. But the controls of having SSAS as a separate tool are worth it.
You need to manage your source code robustly
If you’re SQL Server is 2016, Git can handle source code updates well on the tabular models, giving you all the advantages of merging, branching and source code tracking. A Desktop file on the other hand, doesn’t play as well with Git – you simply overwrite what you had before with the new version.
DAX Studio is nice to use with SSAS
Having said that, I’ll add that DAX Studio works just fine with Desktop models. I haven’t tested the limits of that, but DAX Studio has an SSAS add-in called DAX Editor that is great. It lets you pull the measures out into a file that is easy to edit, then plug those measures back into the .bim file. This saves a ton of time.
You’re small but you really need control
See all the reasons above.
Downsides? You’ll have to learn Visual Studio and, of course, if you don’t have SQL Server you have to buy it (ideally SQL Server 2016). You’ll also miss the nifty monthly releases of new DAX that you get with Desktop. (Keep in mind, since you’ll use Desktop as your visual layer, you’ll get those monthly release visualization benefits). Another downside is you can’t do mash ups if you’re connecting live to an SSAS model.
Considering I could only come up with one paragraph of downsides and all those paragraphs of upsides, I guess my bias is clear. But even if you put that aside, check it out. And as you do, take a look at these final points – they may just be the tipping points.
Why it’s getting even more compelling
- Power Query will be part of SSAS. Of course this won’t happen until you have SQL 2016, but this will eliminate one big disparity between Desktop and SSAS
- SSAS is now available in Azure!!
