none
Processing cube results in longer database backup RRS feed

  • Question

  • I have a cube that I populate with more data every day.  I want the cube and its source database to be a 6 month moving window of data (it can grow to 7 months or so but then I want to shrink it to 6 months - i.e, it doesn't have to be 6 months of data everyday). I have a daily job that deletes data older than 6 months from the database but I'm having trouble doing the same to the cube.  When I periodically do a process full on the cube, it appears to repopulate the cube from the database and I only have the 6 months of data that I want.  However, the last time I did this process full on the cube, my database (not cube) backup went from 3 hours to 20 hours.  The database is about 700GB.  I've since done a backup/restore on the database, formatting the disk and that did not help.  In the past, if I would delete the cube and rebuild it, the database backup would remain the same amount of time. I'm trying to avoid doing the delete/rebuild cube because I only have one server and it's production, so I don't want to screw it up.

    My questions are: why would doing a process full on the cube which reduces the amount of data it has cause the source database backup time to multiply times 7?  Also, does doing a process full really clear out the cube?  I assume doing an unprocess and process full would do the same as a process full?

    SQL Server and Analysis Services are 2005.

    TIA


    Nick Iorio
    Thursday, September 22, 2011 10:14 PM

All replies

  • If you're looking to remove data from a cube the best way is through partitioning, not reprocessing.

    We have a cube with a rolling 95 days of data and every day we'd create a partition for the new day and drop the partition with the then 96th day of data. Very painless, easy to maintain, and entirely automated.

    What backup is taking 20 hours?  SSAS or the relational database? Are you using ROLAP?

     

     

    Thursday, September 22, 2011 11:22 PM
  • I would like to try the partitioning. Can you refer me to a website with info on how to do that?

    It's the relational database backup that went from 4 to 20 hours right after I did a process full.  I'm using MOLAP.

    Does doing an unprocess and then a process full do anything more than doing a process full? (The process full supposedly first deletes the data as the unprocess does.)

    thanks!


    Nick Iorio
    Friday, September 23, 2011 5:33 PM
  • Hi niorio,

    The suggestion from JesseO is very good. Here is an article on how to create partitions in BIDS for your reference:

    http://sqlserverpedia.com/blog/sql-server-bloggers/creating-analysis-services-partitions-using-bids/

    "Does doing an unprocess and then a process full do anything more than doing a process full? (The process full supposedly first deletes the data as the unprocess does.)"

    I didn't do the test for sure. But I guess process full need to scan cube to collect some statistic information before deleting cube data, so maybe a little more than doing a process full. However, you really don't need to do unprocess on the cube and then a process full as it first drops and then reload all data in the object.

    thanks,
    Jerry

    Monday, September 26, 2011 7:11 AM
    Moderator
  • Thanks Jerry.  I'll pursue the partitions solution.

    However, out of curiosity, no one still has answered the question as to why the relational database backup went from 3 hours to 20 hours when the only change was doing a process full on the cube.


    Nick Iorio
    Monday, September 26, 2011 4:49 PM
  • It's the relational database backup that went from 4 to 20 hours right after I did a process full.  I'm using MOLAP.

    Does doing an unprocess and then a process full do anything more than doing a process full? (The process full supposedly first deletes the data as the unprocess does.)

    thanks!


    Nick Iorio

     

    I can't think of anything that a SSAS process (MOLAP, not ROLAP) would do to cause a SQL backup to increase by more that amount after a single process.

    Is the backup nearly the same size? Or just taking longer?

     

    Monday, September 26, 2011 5:38 PM
  • Just taking longer.  As described earlier, the relational database is a 6 month moving window of data.  Every day, I add a day's worth of data and delete a day's worth of data.  The backup was consistently around 3 hours and then jumped to 20 hours after I did the process full on the cube.
    Nick Iorio
    Monday, September 26, 2011 5:55 PM