You learn all kinds of interesting things on the forums! Recently I saw this post from Nils Rottgardt about a problem with the CREATE SESSION CUBE statement and SSAS Multidimensional:
Here’s a related Connect:
Basically, when you run a CREATE SESSION CUBE statement SSAS creates a shadow copy of the cube on disk – and if you have a very large cube, with very large dimensions, this could use up a lot of storage space on the server. When you use the custom grouping functionality in a PivotTable Excel generates a CREATE SESSION CUBE statement behind the scenes, so it’s possible to cause a lot of mischief just by using custom grouping in Excel.
Incidentally, this isn’t a problem for SSAS Tabular because it doesn’t support the CREATE SESSION CUBE statement.
I always knew this particular functionality could be very slow and always warned people against using it, but I didn’t know about the disk space issue… if your users haven’t found out about custom grouping yet, I recommend you don’t tell them about it!
We had several server crashes because of some users running CREATE SESSION CUBE statements from Excel. What happened was that SSAS used all the available storage space on the dedicated SSAS server.
During our user trainings we forbid the users to use the custom groupings in Excel, because if you do not mention it, there will always be a user who finds out about it and starts using it with the mentioned effects.
@Chris: Is there a way to turn off the CREATE SESSION CUBE “feature” in SSAS? That would be nice…
I don’t know – I don’t think so
Hi.
I done some investigation on this issue and also disscussed it with Microsoft Support. There is no official feature to avoid shadow cube creation by the end user. So the system could be shutdown by every user that wants to have some free time. 🙂 As a workaround we spend some thoughts to detect long running CREATE SESSION statements with DMVs and close the session of the user using a background job to stabilize the service availlability. But at the end we decided to tell our users not to use the custom grouping first which works fine until now.
Cheers,
Nils
Hi Chris
Any reccomendations for alternatives to create custom groups in Excel? Users tend to need the ability to group dimension members on ther own. I was looking into named sets, but does’t see this as a viable workaround.
regards
Thomas
Not really, no, I’m afraid. If you have Excel 2013 and SSAS Multidimensional you could use calculated members to create summary totals for groups of members, but that involves writing some (simple) MDX.