Usually, in an OLAP cube, measures are numeric values and strings are used primarily as descriptions of dimension members.
In Analysis Services, strings are stored in separate structures – called “string stores” – because they have dynamic records size, and are less efficient – much less efficient – than numeric fields.
Furthermore, every string has an overhead of 12 additional bytes for management – so even a 1-byte string takes more room than an 8-byte numeric field.
For these two reasons alone we should limit the use of strings in our projects – that means we should use them only as “user friendly labels” for all dimension members – so, whenever possible, we should prefer integer values.
However, the worst thing about strings in Analysis Services – at least before SQL Server 2012 – is that the string store has an absolute maximum size of 4GB.
This limit applies to every single attribute of every dimension and, more precisely, to any of the following types of string file stores:
This is a “physical” limit of the engine, hence there is no workaround and to avoid this problem, you can try one or more of these potential solutions:
In fact, we can easily estimate the storage needed by our dimension using this simple calculation:
Total storage = regular storage + overhead storage
Where
Regular storage = (members * characters * translations * 2)
Overhead storage = (members * translations * 12)
And
Members = number of members in our dimension
Characters = number of characters in the string
Translations = number of translations including the default language
2 = constant value for conversion to Unicode characters
12 = constant value for strings overhead
Here an example:
2,000,000 members
x 94 characters per member name
x 10 translations
x 2 for conversion to Unicode characters
+
2,000,000 members
x 10 translations
x 12 bytes overhead per string
=
4,000,000,000 bytes
(We can approximate this value to the maximum limit even if the real value is
1GB = 1,0243 bytes = (1,024 * 1,024 * 1,024) bytes = 1,073,741,824 bytes
4GB = 1,073,741,824 * 4 = 4,294,967,296 bytes)
From the above calculations, it is easy to understand that, with a member name that is 94 characters long and 10 translations, our dimension is limited to 2 million members.
Well, the formula is not so complicated, but surely, it would be easier to have a simple utility to calculate the estimated storage occupation of our dimension attributes.
That was exactly my thought a few days ago when I had to do it a lot of time in a very big project.
Therefore, I decided to develop a very simple application during my – very little indeed – spare time.
I called it “SSAS – String Storage Calculator” and you can download it here:
https://skydrive.live.com/?cid=967b33af45c9c3ba&id=967B33AF45C9C3BA!186&authkey=!AAoVjrObyUX5wkQ
source
http://francescodechirico.wordpress.com/2012/07/04/ssas-string-storage-calculator/
In Analysis Services, strings are stored in separate structures – called “string stores” – because they have dynamic records size, and are less efficient – much less efficient – than numeric fields.
Furthermore, every string has an overhead of 12 additional bytes for management – so even a 1-byte string takes more room than an 8-byte numeric field.
For these two reasons alone we should limit the use of strings in our projects – that means we should use them only as “user friendly labels” for all dimension members – so, whenever possible, we should prefer integer values.
However, the worst thing about strings in Analysis Services – at least before SQL Server 2012 – is that the string store has an absolute maximum size of 4GB.
This limit applies to every single attribute of every dimension and, more precisely, to any of the following types of string file stores:
- *.ksstore – Used to store strings representing keys of attribute members
- *.asstore – Used to store the names of members
- *.bsstore – Used to store the blobs of members
This is a “physical” limit of the engine, hence there is no workaround and to avoid this problem, you can try one or more of these potential solutions:
- Do not use strings as key columns for attributes with a large number of members
- Shorten attributes names
- Shorten translations of attributes names
- Switch to a ROLAP dimension
In fact, we can easily estimate the storage needed by our dimension using this simple calculation:
Total storage = regular storage + overhead storage
Where
Regular storage = (members * characters * translations * 2)
Overhead storage = (members * translations * 12)
And
Members = number of members in our dimension
Characters = number of characters in the string
Translations = number of translations including the default language
2 = constant value for conversion to Unicode characters
12 = constant value for strings overhead
Here an example:
2,000,000 members
x 94 characters per member name
x 10 translations
x 2 for conversion to Unicode characters
+
2,000,000 members
x 10 translations
x 12 bytes overhead per string
=
4,000,000,000 bytes
(We can approximate this value to the maximum limit even if the real value is
1GB = 1,0243 bytes = (1,024 * 1,024 * 1,024) bytes = 1,073,741,824 bytes
4GB = 1,073,741,824 * 4 = 4,294,967,296 bytes)
From the above calculations, it is easy to understand that, with a member name that is 94 characters long and 10 translations, our dimension is limited to 2 million members.
Well, the formula is not so complicated, but surely, it would be easier to have a simple utility to calculate the estimated storage occupation of our dimension attributes.
That was exactly my thought a few days ago when I had to do it a lot of time in a very big project.
Therefore, I decided to develop a very simple application during my – very little indeed – spare time.
I called it “SSAS – String Storage Calculator” and you can download it here:
https://skydrive.live.com/?cid=967b33af45c9c3ba&id=967B33AF45C9C3BA!186&authkey=!AAoVjrObyUX5wkQ
source
http://francescodechirico.wordpress.com/2012/07/04/ssas-string-storage-calculator/
No comments:
Post a Comment