October 14, 2004 at 12:13 pm
Does anyone have a good way, code examples, plan in place about how they go about executing MDX to Warm the Cache after restarting the server or rebuilding cubes/dimensions? A code example, the platform, etc would be fantastic.
Also, how did you go about acquiring the MDX.
Any pointers would be so appreciated. I am having trouble finding anything on google or SQLServerCentral regarding this. I imagine it is commonplace. Seems everyone with a large AS implementation would need this type of process.
Mindy
October 14, 2004 at 12:58 pm
To rephrase and clarify:
Are you pre-warming the cache?
If so, how are you going about doing it? - reading MDX from the Meta Data DB periodically and coming up with often issued MDX, hardcoding that MDX into a DTS Active X task? Putting that MDX into a .vbs using ADOMD and issuing via DOS? Scheduling the .vbs? When/How?
What best practices have you come up with for this problem?
Any code examples would be greatly appreciated, but I am first looking for a process that is tried and true.
Mindy
October 15, 2004 at 2:28 am
We pre-heat the cache for some things not for others. It's all specific to users/ cubes and response times.
Each company will query cubes differently throughout the day so there is no hard and fast rule for a pre-heating strategy, nor is there any set code.
I suggest the first thing you should do is get some idea or who’s looking at what and at what time. Fortunately a nice person has written an article about how to do this, check out
http://www.sqlservercentral.com/columnists/rbalukonis/analysisaboutanalysisservices.asp
I’ve got something similar, however, I split the start time into a day and hour column, it’s up to you.
This should give you a good idea of cube usage throughout the day and a great starting point for deciding which cubes to pre-heat. If lots of users constantly log on at 07:30 and use a daily sales cube heavily then pre-heat the sales cube 7:00. Then at 12:00 the usage of stock spikes then pre-heat at 11:30, and so on.
The next step would be to see how the users use the cube if all the 07:30 users just look at customers sales for yesterday them construct a batch of MDX statements that return data on customers. There’s no point in returning data for sales regions if nobody looks at it, you’d just be taking up cache memory that could hold customer data.
Finally once you have an idea of users data needs write a batch of MDX code that will return the kind of data the user will want and run it before they do, preferably during a latent period so not to disrupt actual users. To run the script you can use DTS SQL task/ ActiveX or VB or even an automated Excel book if you like, personally I schedule a DTS package with an OLAP connection and MDX statements in SQL tasks.
There are other factors to consider like overall cube response times, over-heating on one cube, office politics (e.g. is the MD’s response times more important that 5 sales reps) and pre-heat time (if pre-heating takes 20 mins run approx 30 mins before users do)
Hope this helps.
Howard.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply