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.