SSAS with a few thousand calculations. How to reduce time-to-connect?

• VALEK

  SSCarpal Tunnel

  Points: 4206

  More actions

  March 11, 2015 at 12:19 am

  #315363

  We have a cube, which has a few thousand (20K+) calcs created on it. :crazy:

  To quickly eliminate questions why we need so many - we hid one dimension with 20K members and exposed them as calculated measures, organised in folders. This is what users wanted for their Excel pivot table.

  Now, the problem is on our SSAS2008 it takes some 4-5 minutes (!!) to connect to the system from Excel, before you are able to see the cubes.

  Next time you connect, it is very quick.

  I am guessing, it tries to validate the calculations when the person connects for the first time. Cache warming (SELECT FROM [CubeName]) takes 4-5 minutes also, but does not help. Here I am guessing again - the calculations should be validated individually for each and every user/connection.

  Does SSAS have any settings or is there anything we can do to prevent this initial 5-minute freeze time??

  Any help appreciated.

  VAL

  ps: this is a re-post of the same thing in SSAS thread

  http://www.sqlservercentral.com/Forums/Topic1667412-17-1.aspx

  M.Sc.IT, M.B.A, MCTS BI 2008, MCITP BI 2008, MCTS SQL Dev, CSM, CDVDM