The Physical Data Model - Microsoft SQL Server 2008 Analysis Services - page 344

information appended to the end of the file, which enables Analysis Services to load the
data store from a file into memory.

Bit Store Structure

For cases in which Analysis Services needs to store only 1 bit per record, it uses a special
data structure, called a bit store. A bit store enables Analysis Services to optimize its access
to small and frequently used data. Analysis Services writes the records one after another,
disregarding byte alignment, so that they occupy the whole page. You can calculate the
number of records per page by dividing the page size, in bytes, by eight. Using a bit store
when the page size is large and the number of records is small enough ensures that all
records are stored on a single page, which drastically increases data access speed.

String Store Structure

When Analysis Services performs operations on multidimensional data, it's very important
that the data be stored in records with fixed sizes. However, strings usually don't have a
fixed size, and multidimensional operations are typically executed on numeric, not string,
data. Therefore, Analysis Services stores string data in a different format and location from
data using other data types.

Analysis Services creates an additional store, called a string store, to save text. Therefore,
Analysis Services usually creates two stores: a main store for numeric data and a string
store for text data. Although Analysis Services supports strings of all data types—such as
char , nchar , and varchar —all string data types are internally converted and stored as
Unicode strings. A main store can be either a data store or a file store. When the main
store is a file store, the string store is also a file store. The page size in a string store is typi-
cally larger than that in the main store because strings are typically longer than numbers.
Each null-terminated string has at least 8 bytes allocated to it, with the following structure:

. Bytes 1 to 2 —The original data type of the string

. Bytes 3 to 6 —The size, in bytes, of the string

. Bytes 7 to n - 2 —The string data

. Bytes n - 1 to n —The Unicode NULL character, which terminates the string

The strings are stored in the string store consecutively. The main store represents a string
as a 4-byte field that contains the offset location of the string data in the string store. In
other words, Analysis Services requires 12 additional bytes to store a single string: 4 bytes
to store the position in the string store, 4 bytes to store the size of the string, 2 bytes to
store the type of the string, and 2 bytes to store the string's NULL terminator character.
Therefore, storing strings in Analysis Services—especially short strings—is expensive and
lowers the performance of the system. You can see how Analysis Services stores strings in
Figure 20.2.

When a store contains string data, the server stores a collation property for each string
column in the metadata portion of the data store. The Collation property defines how
Analysis Services compares strings to each other when it performs comparison or ordering
operations.