Decelerated partition build in MS Analytic Services 2000

Question

Decelerated partition build in MS Analytic Services 2000

Ivo Solansky

SSC Enthusiast

Points: 106
More actions
December 11, 2006 at 6:23 am

#115393

Hi,
in our DW we have got daily partitioned cube with 550 partitions and daily increase about 3000000 fact records, with 30 dimensions and physical size about 45GB. Last week suddenly decelerated partition building speed from common 3-6 minute per partition to 25-40 minute.
Any changes wasn't passed on this cube nor on related dimensions, any software, hardware nor netware change. Any change in aggregations or data content.
SQL part of DW and reading SQL data part of OLAP cube building, runs with same speed as before. Partition building of other smaller cubes has same speed as before.
Because this deceleration is only associated with building partition of this - largest - cube, it seems like this is behaviour of MS OLAP cube on very large cubes.
Do You have any experience with such behaviour? Or some other idea?
Thanks for any response
Ivo

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Gary Middlemiss Grasshopper Points: 23 More actions · Answer 1

I had a similar problem several years ago. We were creating 16 partitions a day for about 3 months of data - so about 960 partitions, similar number of rows 3M/day but about 130 GB database.

We found that the time increase came because AS seems to re-check (or do something) with each partition in the cube even when nothing had changed in the old partitions and we were just creating a new partition. At some 'magical' number this process would slow way down and the only solution was to archive and remove the older partitions.

Of course, this might not be an option for you.

Gary Middlemiss Grasshopper Points: 23 More actions · Answer 2

I re-read your message. Is it true that your numbers are 3-6 min per PARTITION and 25-30 min per PARTITION? or is this per cube? This would make your 'fast' build take 1 day and your 'slow' build 10 days which seems... slow.

chetana samal Old Hand Points: 388 More actions · Answer 3

hi,

I am new to MS Analytic Services. Can one advise from where to start!

Thanks

CS

Ivo Solansky SSC Enthusiast Points: 106 More actions · Answer 4

You've re-read it correct. 🙂

We don't rebuild whole cube every day, it is build only in incremental way, when all things go well (rare case), then we can build only 1-2 such partition per day. Mostly common is to build about 7-10 such partitions.

Total rebuild is only if our customer wants to change/add some dimensions. Ussualy one times per 2-3 months. And we do it in paralelism 3-4. 🙂

But now seems than our problem is already solved. We found that in unknown way was changed OLAP parameter "Large level defined" upto 500 (when default value is 1000) and started activity of msmdvldm.exe, it's using "is not recommended" in some discussions.

When we set up this parameter to value 2000 and blocked msmdvldm.exe, all is suddenly OK again, as before. 🙂

Ivo Solansky SSC Enthusiast Points: 106 More actions · Answer 5

It depends from Your background in IT or analytics. If You are familiary with DB and especialy SQL, try Microsoft samples. It could help You.

chetana samal Old Hand Points: 388 More actions · Answer 6

Yes, I am comfortable with .Net, sql and SSRS, can you tell me a little more about dashboard and score cards and how Analytic Service can produce it

Thanks

CS

Adi Cohen Old Hand Points: 330 More actions · Answer 7

I have pretty much the same background as you have and I recently decided I should know more about BI.

I recommend a book called "Practical Business Intelligence with SQL Server 2005" by Addison Wesley.

It's by no means a comprehensive guide of SSAS, but it gave me a pretty good idea of what's going on with this technology. It has a chapter about building score cards with the help of SSAS, SSRS and WSS (Windows SharePoint Services).

Good luck,

Adi

chetana samal Old Hand Points: 388 More actions · Answer 8

Thanks Adi, a lot! That's good information. I will look for the book. If you can share more knowledge on the topic, it will be great

-CS

Adi Cohen Old Hand Points: 330 More actions · Answer 9

Sorry Chetana, but that's all I have now. I'm just finishing the book I wrote about myself.

But, since my company pays for all of my books and exams I've took the liberty of ordering almost anything I could find regarding SSAS 2005. I'll keep you posted.