Databases Reference
In-Depth Information
Choose Small and Appropriate Data Types and
Sizes
Choosing integers as keys for your tables helps improve processing and
query performance. Using single integer keys (rather than strings or
composite keys) results in faster processing time due to decreased size,
decreased network usage, and the handling of keys internal to the rela-
tional data source, and Analysis Services can be done with simple native
machine instructions. Analysis Services looks up the keys of dimension
members while processing dimensions as well as cubes. The key lookup
routines used by Analysis Services can run tens or hundreds of times
faster for integer data types compared to other data types that are used
as the key. In general, we recommend you set appropriate keys and
or consider a design using integer surrogate keys in the relational data
source in advance of building your UDM.
SQL Server and Analysis Services Installations
When you install SQL Server 2005, you can have SQL Server and Ana-
lysis Services installed on the same machine or a different machine.
There are some trade-offs that you might want to consider for pro-
cessing when you have UDMs retrieving data from the SQL Server.
If you have both installations on the same machine, SQL Server and
Analysis Services may compete for resources. You need to make sure
you have sufficient processors and memory configurations on your sys-
tem. Whenever your Analysis Services dimensions are large (millions of
members) there will be an impact on processing speed. If SQL Server
and Analysis Services are competing for memory, you might have signi-
ficant paging of data to disk, which could slow down operations dramat-
ically.
For 32-bit versions of Windows we recommend turning on the /3GB flag.
By default each process running in Windows can access a maximum of
2GB. By turning on the /3GB flag, you allow the Analysis Services pro-
cess to access up to 3GB of addressable space. This increases the ac-
cessible memory and facilitates large dimension processing and aggreg-
ation building. To enable the /3GB option, open your boot.ini file on your
Search WWH ::
Custom Search