May 11, 2004 at 4:29 am
Hi Gurus,
Sql server 2000 ,sp3
Analysis Services 2000,sp3
MOLAP
2 CPU Pentium III 1.2 GHz.
2 GB RAM
Windows 2000 server
At our prod server , We have 1 Million records in our fact table. base RDBMS size is 10GB, Our cube processing is hanging many times and needs restart of OLAP service every time hang.
We process all the shared dimensions first for about 20 cubes and then the cubes.I also observed if we do not process dimension first and directly the cube it gives error and could not process.It will be great if any body can shed light on this also
Quick help is greatly appreciated.
Thanks,
Sheilesh
May 11, 2004 at 9:28 am
In order to process a cube, you must first process the dimensions. I start with the rule of processing shared dimensions first, non-shared dimensions second and the fact table last.
In order to isolate your problem I suggest processing in this sequence manually. You may get lucky and find the dimension that is causing the hanging.
If you manage the whole process manually, then this points to the order that your job is processing them in. Monitor the job and make sure the ordering is correct.
as a side issue, ensure that you have enough disk space to be able to handle the transaction logs. I have seen these fill up like crazy in the past on some systems I have taken over - Never my own creations, of course.
May 12, 2004 at 4:49 am
Thanks jonathan , more or less we are also adopting the same process.
As you said "I start with the rule of processing shared dimensions first, non-shared dimensions second and the fact table last."
What is fact table here u mean cube itself ?
Also we are running in "simple" recovery model instead of "bulk insert" or "full" , what is the recommondation?
I ensured there is enough disk free space (more then 20 GB free).
I have another question cube processing is cpu intesinve or i/o intesive as the same system was was running on 4 cpu of 2.4 GHz system fine but not on 2 cpu 1.2 GHz.
TIA
May 12, 2004 at 7:09 am
Do you have processing logging on? If not, turn it on so you can review the logs and see where the failure/error is being thrown. re: yuor disk space, keep in mind that your SQL will require space (for temp DB expansion) as well as AS requiring space for it's temp files (assuming they're on the same machine). For 10Gb of data, 20Gb may not be enough. If you did run low on disk, this should have hit the event log (prob system log). If they're on separate machines, you need to check both systems logs, because SQL may have stopped providing records to AS for processing. We've run out of disk on different occassions, and also run extremely low on memory, both actions causing SQL (and therefore AS as well when processing) to run *really* slow (to be expected really I guess). It very well could be a lack of RAM on the smaller machine, keep in mind that AS loads all dimensions (that it can) into memory, and for all roles that are created it loads up another image of the dimension. Dave Wickert (MSFT) has posted some excellent documents and presentations on msdn, I have posted links to these recently, if you review the sqlservercentral posts you'll findone from maybe a month ago that has links to the docs on msdn. The key one (IMHO) isthe one focused on managing a AS database, comes from the approach of AS 'databases' requiring the same sort of DBA'ing as SQL DB's.
Steve.
May 12, 2004 at 7:27 am
Thanks Steve, I will try your suggestions and get back to you.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply