Geeks With Blogs
James Rogers BI from the trenches...


I cut my DBA teeth on IBM's DB2 UDB EEE (enough acronyms) back in the 90's. I have always appreciated the scalability that is available with a MPP architecture. So a couple of years ago I was excited to hear that SQL Server was going to have a MPP architecture available. Now I am at SQLPass Summit 2011 and seeing the PDW (Parallel Data Warehouse) and the hardware offerings by Dell and HP to scale that up and out. However, I was disappointed to learn that this technology does not extend to Analysis Services.
     I spend a great deal of my time tuning SSAS and can say that it scales very well as is and there is a plethora of information out there to facilitate those efforts. However, let's dream a little here. Imagine a multi-node/single-instance/shared-nothing installation of SSAS. You can distribute/partition the cubes across the nodes and then within the nodes in a manner that allows you to isolate hot-spots of data on their own hardware so that data can stay cached. Meanwhile, other queries on data that is less-often accessed or more ad-hoc in nature can process with resources allocated on separate nodes for that and the majority of queries do not suffer as much from cache turnover and the overall system load. Ahhh, it is good to dream. We could take that a step further and wish that would could mix Vertipaq storage for that more frequently used/"hot" data node and traditional Analysis Services storage for the other data where aggregations may have more value in helping the queries. Additionally, processing of mining models could be further isolated from ongoing activities and processing activities could take advantage of the MPP architecture and resulting parallelism to allow for more processing in shorter time than on a similar, single box solution. Could even go further and dedicate a node to ROLAP partitions for more real-time requirements while limiting the burden of such queries on the overall system performance.
     I realize that MS probably doesn't have much of a target market for such dreams but it would be cool nonetheless and provide some real scalability options for big data analytics implementations. Like always, the devil is in the details. OK - back to reality.
Posted on Thursday, October 13, 2011 12:01 PM Analysis Services , SQLServer | Back to top

Comments on this post: SSAS, MPP and PDW

# re: SSAS, MPP and PDW
Requesting Gravatar...
James, with PDW moving to SQL 2012 / BISM / tabular models a lot of what you are dreaming here should be possible...
Left by Gert Fahrnberger on Oct 15, 2011 9:24 PM

# re: SSAS, MPP and PDW
Requesting Gravatar...
After sitting in Marco Russo's session on Vertipaq/Column-store for SSAS a couple of things that concern me about that are the fact that the tabular model for SSAS loads everything into memory. I suppose if you split that on to enough nodes that would be less of a concern. However, complex relationships and business logic would still need to be in a traditional SSAS model for 2012 (according to my interpretation of Marco's session). However, I am not an expert on PDW's capabilities and to-date, Vertipaq has not been an option due to the complex cubes and dimensions as well as data volumes so I haven't used it much.
Left by James on Oct 16, 2011 6:49 AM

Your comment:
 (will show your gravatar)

Copyright © James Rogers | Powered by: