Loading Data from a Relational Database - Microsoft SQL Server 2008 Analysis Services - page 310

sensitive and private. Storing such information in clear text in memory and on the disk in
the metadata files can be a security risk.
To store sensitive information safely and securely, Analysis Services encrypts the Password
and ConnectionString properties. Analysis Services will not return the Password of the
data source object to the user. When you script a data source object in SQL Server
Management Studio, you discover that Analysis Services does not return the Password
property. The same applies to the Password property of the ConnectionString property.
Analysis Services always tries to parse the value of the ConnectionString property and
strip any password from it. BI Dev Studio stores the DDL for data sources in .ds files in
the project folder, but it does not store passwords in those files. If you look in a .ds file,
you will see the ImpersonationInfoSecurity property in place of the password:
<ImpersonationInfoSecurity>PasswordRemoved</ImpersonationInfoSecurity>
That property indicates that BI Dev Studio removed the password when it saved the
project files. Therefore, when you open such projects in BI Dev Studio and deploy the
database to the server, BI Dev Studio might prompt you for a password.
Connection Timeouts
Data source definitions include the capability to define a timeout —the amount of time
that the server waits for an activity to occur in response to some sort of request. Two types
of timeout are associated with connections to external data sources: connection timeouts
and request timeouts.
A connection timeout is the amount of time that Analysis Services waits for the establish-
ment of a successful connection before it reports an error. For example, when you request
a connection to a SQL Server relational database, how long should you expect to wait for
Analysis Services to indicate that the database is not available at this time?
The Analysis Services DatabaseConnectionPoolConnectTimeout property defines the length
of the connection timeout. It is set to 60 seconds by default. You can use the connection
string's ConnectTimeout property to set it to a lesser value for a particular data source.
The ExternalCommandTimeout server configuration property determines the length of the
query timeout. It is set to 3,600 seconds (1 hour) by default. To set it to a lesser value,
specify that value for the Timeout property on your data source object.
Connection Pooling
Analysis Services does not create a connection object every time it has to connect to the
same relational database. It keeps existing connection objects in a pool and tries to find a
matching connection every time there is a new request for a connection. (See Figure 17.3
for an illustration of this practice.) Requests for data might come frequently. Creating a
connection is quite expensive from a performance point of view, and doing so might take
a toll on the overall performance of the system.