Post reply

Decelerated partition build in MS Analytic Services 2000

  • 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

  • December 12, 2006 at 7:41 am

    #677468

    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.

  • December 12, 2006 at 7:47 am

    #677471

    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.

  • December 12, 2006 at 9:11 am

    #677514

    hi,

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

    Thanks

    CS

  • December 12, 2006 at 9:25 am

    #677523

    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. 🙂

  • December 12, 2006 at 9:29 am

    #677526

    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.

  • December 12, 2006 at 9:35 am

    #677528

    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

  • December 12, 2006 at 6:30 pm

    #677679

    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

  • December 13, 2006 at 9:07 am

    #677814

    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

  • December 13, 2006 at 12:42 pm

    #677883

    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.

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

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