Database and Backup size of SQL Server Analysis Services 2008

Can someone tell me the approximate ratio between:

SSAS 2008 database size: SSAS 2008 backup size without compression
SSAS 2008 database size: SSAS 2008 backup size with compression

I know that it all depends on the how much data present in the database and how much full the database is. I just need the approximate values assuming that the SSAS database has the complete data in its allocated space.
Srinivas_VengalaAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Ryan McCauleyConnect With a Mentor Data and Analytics ManagerCommented:
It shows that my database size (in properties) is 115MB, so you're right - the uncompressed backup is slightly larger. It does say "Estimated size", which seems odd, but maybe it's somehow only able to ballpark the size of the all the objects, and it doesn't know for sure until you try to back things up.

53MB -> 63MB seems a bit extreme - to have a backup that's 20% larger than the file - but it seems to be less of an issue if you compress it or zip the uncompressed backup. Also, if that's really just an "estimated" number (and the backup includes database objects and properties that aren't part of the "database size" number), then perhaps it makes more sense.

How large a database are you talking about here for your instance? At these sizes, it's not much in the big scheme of things, unless you're worried about saving a few dozen MB. We have years worth of data in our SSAS database, and while our list of facts isn't extensive, it's large enough that I'd never worry about our SSAS database getting over a few GB, and it's still dwarfed by the 75-100GB transaction databases that these cubes are built from.
0
 
John ClaesSenior .Net Consultant & Technical AnalistCommented:
Source :
http://www.mssqltips.com/tip.asp?tip=2025


Normal backup with no data compression  :
the backup size is just about the same size as the database since the backup is not a compressed backup.

Compressed backup with no data compression  :
the backup size is reduced to a third of the previous backup, which is common in the case of compressed backups in SQL Server 2008.

Normal backup with row-level compression
The size of this backup file (performed in this step) is smaller than the normal backup file of the same database that we performed in Step 1. The main reason the backup file size is reduced is the decrease in the number of data pages required to store the table due to row level compression applied on the table.

Compressed backup with row-level compression
After successfully taking the backup, the below image displays the file size of the new backup along with the old ones.  As you can see we got a little more compression.

Normal backup with page-level compression
The size of this backup file (performed in this step) is drastically reduced when compared to normal backup file size of the same database that we performed in step 1. The main reason the backup file size is reduced drastically is because page level compression reduces the number of data pages required to store the table more than with row level compression.   So from this we can see that page level compression get better compression than row level compression.

Compressed backup with page-level compression
After this backup and comparing backup file sizes with those of the previous backups, backup compression does not significantly compress the backups when the database is already enabled with page level data compression.



Conclusion
Backup compression reduces the backup size by approximately 30% when compared to a regular non-compressed backup of the same database when page level compression is enabled. When row level compression is enabled, it reduces somewhat better, approximately 60 %.

Some points to be remembered
    * The results shown in this article might differ with another database with same size due to differences in the datatypes of the columns and the data occupancy in the records.
    * When page level compression is enabled on one table and row level compression is enabled on another table, the backup compression will be proportional in size with respect to the amount of data that exists in those tables.
    * On a partitioned table, one partition can be compressed using row level compression and another using page level compression, i.e both types of compression can be enabled on a single table.
0
 
Srinivas_VengalaAuthor Commented:
poor_beggar: I am asking for SQL Server Analysis Services (SSAS) not for SQL Server Database Services/Engine.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Ryan McCauleyData and Analytics ManagerCommented:
I don't know if our data follows the general rules, but I just did a test backup of our SSAS database with the "compressed" option set both ways, and it came out:

Uncompressed: 119MB
Compressed: 54MB

So it looks like compression would save us about 55% of the space. Again, not sure if that's typical, or how the size of our databases compares to yours, but there's real-world. Though honestly, between 55MB and 120MB, there's not effectively much difference.

Also, it's worth noting that the uncompressed backup was smalled once I put it in a ZIP file - 40MB vs 43MB (for the compressed one), so if you're going to be zipping the results, you're better off with the uncompressed backup.
0
 
Srinivas_VengalaAuthor Commented:
ryanmccauley: can you tell me the size of the SSAS database you have backed up?

When I backed up the sample SSAS database 'Adventure Works DW' which is of 53MB, it has taken 62.7MB for uncompressed and 32.9MB for compressed backups. Just wondering why the uncompressed backup has taken more size than the actual database size.

First thing, do you see the estimated size in the SSAS database properties to check the size of an SSAS database?
0
 
Srinivas_VengalaAuthor Commented:
OK
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.