Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

CREATE SESSION CUBE and disk usage

with 4 comments

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:

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/175fc61f-676e-4e3d-bed4-283f151641ec/create-session-cube-command-by-excel-grouping-creates-a-shadow-copy-on-the-server-disk-storage?forum=sqlanalysisservices

Here’s a related Connect:

https://connect.microsoft.com/SQLServer/feedback/details/822778/excel-grouping-create-session-cube-breaks-ssas-envirounment-because-of-phyically-copy-the-cube-data-for-every-pivot-table

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!

Written by Chris Webb

April 1, 2014 at 9:36 am

4 Responses

Subscribe to comments with RSS.

  1. […] CREATE SESSION CUBE and disk usage (Chris Webb) […]

  2. 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…

    notaconsultant

    April 9, 2014 at 4:41 pm

    • I don’t know – I don’t think so

      Chris Webb

      April 9, 2014 at 4:47 pm

      • 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

        Nils Rottgardt

        May 6, 2014 at 2:35 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,083 other followers

%d bloggers like this: