September 11, 2015 at 11:00 am
Good morning
We are going to be building a multidimensional analysis infrastructure. I expect to have tons of questions going forward, since I've never had to support this type of environment as a DBA. My first question is this - how do I choose a server size for SSAS? We have about 200 GB of production data (pretty small). I have not seen any Microsoft guidance on how to size the server. Can anyone provide a link or advice?
I am going to be creating a cost sheet on SSAS versus Oracle Essbase, so I need to include the cost of the server, even though I'd guess both products would probably use the same size server. If anyone has an opinion about contrasting these two products, I'd love to hear it.
Thank you!
September 11, 2015 at 11:34 am
Hi Dave,
In order to size an Analysis Services server, you'd need to follow a similar process to what you will do with the sizing of a database server. In light of that, do you have some more clarity around:
September 11, 2015 at 11:59 am
I'll answer as best I can:
We are an online retailer, so I can think of probably 3 main cubes - POs, Orders, and Items. The largest cube (orders) could have 10 Mil records.
I would expect the server to either just have SSAS or SSAS and the ODS/data warehouse.
To allow for growth, I'd say we'd want to be able to support 50 users, though I can't imagine that many simultaneous users - no more than 25 simultaneous.
We are still searching for a good front-end app, but I'd guess we'd have some SSRS reports as well as Excel. We're also looking into options such as Tableau or Qlickview.
I'm not really sure how to describe the level of complexity. I would expect it'd be kind of standard among retail shops - orders will have associated customers, order details, shipping info, etc.
Hope that helps!
September 14, 2015 at 9:44 am
A little tough to accurately spec a server, given that some of the details are still in flux. Here's my general recommendations though:
SSAS requires a lot of resources, especially with many concurrent users. Although it would be possible to host in on the same server as the ODS/Data Warehouse, I wouldn't recommend it. Host it on a separate server and bump up the resources as much as you can, especially the memory. I'd go for no less than 32 GB RAM. Physical would of course be preferred.
In terms of storage, I'd recommend SSD's if you can afford it. Hope this helps at least a little 🙂
September 14, 2015 at 9:54 am
Thanks, that does help but also raises some additional questions:
1. If I'm putting this on a separate server than the ODS, I'm going to need to license 2 additional servers, one for the ODS and one for SSAS. That's a lot of extra $. Since an ODS would usually just be holding data that is loaded to it every X minutes, couldn't it be small enough to sit on the same server? If not, would it make sense to run the ODS of standard edition? Wouldn't I want some of the enterprise features such as columnstore indexes on the ODS?
2. I was under the impression that SSAS does everything in memory, so when you reference the SSDs, would that be for the ODS?
Thank you!
September 14, 2015 at 10:09 am
david.gugg (9/14/2015)
Thanks, that does help but also raises some additional questions:1. If I'm putting this on a separate server than the ODS, I'm going to need to license 2 additional servers, one for the ODS and one for SSAS. That's a lot of extra $. Since an ODS would usually just be holding data that is loaded to it every X minutes, couldn't it be small enough to sit on the same server? If not, would it make sense to run the ODS of standard edition? Wouldn't I want some of the enterprise features such as columnstore indexes on the ODS?
2. I was under the impression that SSAS does everything in memory, so when you reference the SSDs, would that be for the ODS?
Thank you!
The main issue is with resource allocation and utilization, when hosting both on the same machine. SQL Server would want to reserve as much memory as it needs for its workloads, and try hold on to it for as long as it can. This means contention when your SSAS instance is looking for some resources, and it could negatively affect performance.
If you have lots of RAM (let's say 64 GB) and you can restrict SQL Server to ensure that SSAS also gets its share, then it could be ok. If not, it will be worth the extra $$ to host on a separate machine. Splitting SSAS onto its own dedicated server is also a Microsoft best practice.
Columnstore indexes are useful, but only when you have random access type queries directly against your relational database. If you're planning on building MOLAP cubes, the only time SSAS will access your back-end database would be during processing. Columnstore indexes wouldn't necessarily be useful for that purpose alone.
Most SSAS workloads are memory bound, but remember that the data for your cubes are still stored on disk. Fast disks (and disk access) would still be beneficial in my opinion, if you can afford it.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply