Let me start with the background, I was working on a project that needed a back end that was scalable and fast. Having worked quite a bit with SSAS, I thought I'd try SSAS's in memory solution, Relation OLAP. Long story short, it ended very poorly, even simple queries redlined the processor, there was no multithreading, and the amount of memory used constantly grew. Barring ROLAP being a lemon, I must have messed up my implementation, and I had some questions on best practices and how I can avoid the pitfalls in the future.
First up multi-threading, was there a setting I missed to enable multi-threading, because SSAS would nail one processor, and leave the other 7 untouched.
Could it be Hyper V messing us up? All of our development servers are virtual, we knew this could be a problem for an in memory app, so we stayed within a single NUMA node, so the memory shouldn't have been cached, and we didn't get cross node traffic.
Measures from mutiple tables killed performance, this was one of the big selling points for me, OLAP in a microsoft biome without having to first transform the data into a star schema. However as soon as we used queries with measures from multiple tables queries that might have taken two seconds individually, the combined queries had to be stopped after 15 minutes or caused the server to crash. Is there a best practice I missed about only having measures in a single table, or could this be caused by the relations between tables?
Is a many to many relationship getting support in SSAS 2016?
Why does the memory used grow continuously, because we only had four queries, so it's not having to create caches for a ton of queries. We did a default reprocess every night, and a full on weekends. A full process only took 7 minutes (which coming from multi dimensional SSAS was jaw dropping fast) and the intial RAM usage was just over 4 gigs. By the next day it would be between 12 and 16 gigs. Is there a memory setting I missed that capped the growth, or released resources after they were finished being used?
MDX and DAX seemed about equal performance wise (in terms of speed and resource usage), but I'd read that running MDX against ROLAP can cause performance issues, for people who haved used both is there really that big of a performance hit?
I have lots more questions, but I'll start with just these. The data is proprietary so it's hard to give detailed implementation diagrams, every relation was PK to FK with one exception where I was forced to use a composite key due to the restrictions against many to many relations. The composite was handled before the ROLAP in the views that the ROLAP was pulling from. I suspect the size of that key might have been one of the things slowing queries down since it was varchar(24). Any advice would be much appreciated, I loved working with ROLAP, and dax was weird but still easier than MDX.