March 11, 2015 at 12:18 am
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
M.Sc.IT, M.B.A, MCTS BI 2008, MCITP BI 2008, MCTS SQL Dev, CSM, CDVDM
March 18, 2015 at 4:20 pm
I do think there is some caching (or cache warming) going on the first time you connect, but I don't think all the calculations have to be verified individually. Do you have custom security?
In my opinion, your best bet here is to run a trace and see exactly what is happening (and taking such a long time).
March 18, 2015 at 8:44 pm
Martin Schoombee (3/18/2015)
I do think there is some caching (or cache warming) going on the first time you connect, but I don't think all the calculations have to be verified individually. Do you have custom security?In my opinion, your best bet here is to run a trace and see exactly what is happening (and taking such a long time).
It freezes on DBSCHEMA_TABLES command.
Yes we have dimension and cube security.
Do you think this is do to with the custom security?>
M.Sc.IT, M.B.A, MCTS BI 2008, MCITP BI 2008, MCTS SQL Dev, CSM, CDVDM
March 19, 2015 at 9:35 am
VALEK (3/18/2015)
It freezes on DBSCHEMA_TABLES command.
Yes we have dimension and cube security.
Do you think this is do to with the custom security?>
Could be. According to TechNet that command "Identifies the measure groups and dimensions exposed as tables within Microsoft SQL Server Analysis Services"
Run this on your SSAS instance and see what it comes back with:
SELECT * FROM $SYSTEM.DBSCHEMA_TABLES
I am sure that the results have to be based on your permissions, so if you have a lot of custom permissions it could take some time. To test this, create a role with no custom permissions and see if the behavior is the same.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply