December 30, 2014 at 11:53 am
I have a challenge at work to determine whether the additional layer of SSAS will be worth the effort for our organization. What can you do in an SSAS cube that you cannot do with a sql query (or set of sql queries)?
Aside from the potential performance benefits of pre-aggregating data, are there other benefits to SSAS?
Thanx in advance.
Don Simpson
December 30, 2014 at 3:09 pm
In a way, some of the answer depends on the delivery mechanism you're going to use and the experience level of your user base.
SSAS - much more than just having aggregations 🙂 you can have pre-defined drill paths (ie hierarchies) that people can easily use with almost any front end (from Excel upwards). Pro & con - data is (generally) separated from the production system (unlike views) so querying impacts the SSAS service only; you could consider the new tabular engine mode for even faster performance but slightly lessor functionality in some areas; multi-language support; security is (reasonably) easy to implement, down to even the cell level; many to many relationships defined once then user as required; pretty easy to add multi-currency support if required;
Views - pretty easy to pull together; potentially don't even use views, leverage instead columnstore indexed tables (now updatable in 2014); any hierarchies will need to be built, every time, by your end user; no real 'attribute relationship' benefit when users use multiple elements 'naturally' (e.g. country -> state -> city) versus SSAS dimensions understanding (once you tell it) that there is a relationship between these items;
Again, if you're pushing the views into say a qlikview or tableau (or other) engine, then, the view approach will be significantly enhanced by the end user tools but really, how different is this than using SSAS (at least conceptually). You could also get folks to use excel maybe with PowerQuery (native in 2013) and either source (views or SSAS) will be easily consumed and enhanced.
If you're going to make people write TSQL against the views, then personally I'd take that over having to hand-write MDX against a cube 🙂
Steve.
January 5, 2015 at 4:57 pm
stevefromOZ (12/30/2014)
In a way, some of the answer depends on the delivery mechanism you're going to use and the experience level of your user base.SSAS - much more than just having aggregations 🙂 you can have pre-defined drill paths (ie hierarchies) that people can easily use with almost any front end (from Excel upwards). Pro & con - data is (generally) separated from the production system (unlike views) so querying impacts the SSAS service only; you could consider the new tabular engine mode for even faster performance but slightly lessor functionality in some areas; multi-language support; security is (reasonably) easy to implement, down to even the cell level; many to many relationships defined once then user as required; pretty easy to add multi-currency support if required;
Views - pretty easy to pull together; potentially don't even use views, leverage instead columnstore indexed tables (now updatable in 2014); any hierarchies will need to be built, every time, by your end user; no real 'attribute relationship' benefit when users use multiple elements 'naturally' (e.g. country -> state -> city) versus SSAS dimensions understanding (once you tell it) that there is a relationship between these items;
Again, if you're pushing the views into say a qlikview or tableau (or other) engine, then, the view approach will be significantly enhanced by the end user tools but really, how different is this than using SSAS (at least conceptually). You could also get folks to use excel maybe with PowerQuery (native in 2013) and either source (views or SSAS) will be easily consumed and enhanced.
If you're going to make people write TSQL against the views, then personally I'd take that over having to hand-write MDX against a cube 🙂
Hi Steve,
Thanx for the reply.
This is 2008R2, so a lot of the cool tools are not available.
The end users will be a combination of self-service excel wizards and ordinary users that will run canned reports. The data is in a data warehouse (largely Kimble), not the transactional database.
When you describe hierarchies, do you mean the user is tied to an appropriate "order" of relationship, so they don't have to learn and remember?
Don Simpson
January 6, 2015 at 12:18 am
Using pivot tables (in Excel) easily with predefined hierarchies is definately a plus.
Not having to write SQL code can be a plus for certain users. However, if something is not present in the cube they will have to write MDX which is worse than SQL.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply