SSAS Articles Connectivity Analysis Services 2000 and 2005 Connection string properties
Analysis Services 2000 and 2005 Connection string properties
User Rating: / 6
PoorBest 
Written by Vidas Matelis   
Saturday, 28 April 2007 11:06
This article decribes Analysis Services 2000 and 2005 connection string properties.

In most cases to connect to SQL Server Analysis Services 2005 database it enough to use basic connection string that looks similar to:

Provider=msolap.3;Datasource=MySSASServerName;Initial Catalog=MySSASDBName;

But there are additional connection properties that sometimes could be usefull.

In Analysis Services 2000 list of connection properties is well documented in BOL :

Property name

Description

ArtificialData PropertyReserved for future use
Authenticated User PropertyReserved for future use
Auto Synch Period PropertyControls the frequency (in milliseconds) of client/server synchronization
Cache Policy PropertyReserved for future use
Cache Ratio PropertyReserved for future use
Client Cache Size PropertyControls the amount of memory used by the client cache
CompareCaseNotSensitiveStringFlags PropertyAdjusts case-insensitive string comparisons for a specified locale
CompareCaseSensitiveStringFlags PropertyAdjusts case-sensitive string comparisons for a specified locale
Connect Timeout PropertyDetermines the maximum amount of time the client application will attempt to connect to the server before timing out
CreateCube PropertyThe CREATE CUBE statement to create a local cube file
Data Source PropertyThe name of the server computer or local cube file
Datasource Connection Type PropertyDescribes the type of connection that is currently active
Default GUID Dialect PropertyControls the precedence in which language dialects are applied when resolving queries
Default Isolation Mode PropertyControls whether the isolation level is isolated or determined by the cursor type requested by the rowset properties
Default MDX Visual Mode PropertyDetermines the default behavior of visual totals
Distinct Measures By Key PropertyReserved for future use
Do Not Apply Commands PropertyReserved for future use
Execution Location PropertyDetermines the location of query resolution: the client application, server, or a combination
Initial Catalog PropertyThe name of the initial database (catalog)
InsertInto PropertyThe INSERT INTO statement used to populate a local cube file created with the CREATE CUBE statement
Large Level Threshold PropertyDetermines the definition of large level for client/server handling of level members
Locale Identifier PropertyThe locale ID of preference for the client application
Log File PropertySpecifies a file name for logging queries
MDX Calculated Members Mode PropertyReserved for future use
MDX Compatibility PropertyDetermines how empty members are treated for ragged and unbalanced hierarchies
MDX Object Qualification PropertyDescribes how object names are qualified in Microsoft SQL Server™ 2000 Analysis Services
MDX Unique Name Style PropertyDetermines the technique for generating unique names
Mining Execution Location PropertyDetermines the location of query resolution for data mining queries
Mining Location PropertyDetermines the directory in which a local data mining model will be created
Mining Persistence Format PropertyDetermines how data mining models are saved
OLE DB for OLAP Version PropertyIndicates the version of the OLE DB provider
Password PropertySpecifies the password to use when connecting using HTTP
Provider PropertyA predefined string containing other initialization properties
Read Only Session PropertyReserved for future use
Restricted Client PropertyRestricts PivotTable Service from creating local cubes or running deeply recursive queries.
Roles PropertySpecifies a comma-delimited string of the role names by which a client application connects to the server
Safety Options PropertyDetermines how security for user-defined functions is handled
Secured Cell Value PropertyDetermines the type of return value that results from a reference to a secured cell
Show Hidden Cubes PropertyReserved for future use
Source_DSN PropertyThe OLE DB connection string, ODBC connection string, or ODBC data source name (DSN) for the source relational database; used only when creating a local cube file
Source_DSN_Suffix PropertyUsed to specify DSN properties for creating local cubes that should not be stored as part of the local cubes structure, such as the user ID and password for the local cube's data source
SQL Compatibility PropertyReserved for future use
SSPI PropertyDetermines the security package to use during the session
UseExistingFile PropertyDetermines whether a local cube file is overwritten if the connection string contains CREATE CUBE and INSERT INTO statements
User ID PropertySpecifies a valid user name, such as a valid domain logon or local logon
Writeback Timeout PropertyDetermines the maximum amount of time the client application will attempt to communicate updates to a writeback table on the server before timing out

Note: With SQL Server 2000 SP3 and SP4 Microsoft introduced some additional properties, that are not listed in the list above.

In SSAS 2005 connection property list changed. Some of the properties listed above will be ignored.
Additional propertes were introduced or some behavior changed:

Parameter

Description

Source

Cache PolicyValues: 1..9
You can use the Cache Policy connection property in Analysis Services to control the algorithm that is used by the client to determine which data members to request to resolve an MDX query. Typically, the default value provides optimal performance for standard queries. However, the database administrator may sometimes decide that a different Cache Policy value should be used for selected queries.
Sometimes AS server chooses a cell by cell calculation plan over a bulk evaluation plan. Setting Cache Policy=9 forces AS server to always use a bulk evaluation plan if one is available. This is a workaround in the rare cases where the AS decision hurts performance.
Example: Cache Policy=9;
You can also define in in msmdsrv.ini file:
<CalculationCoverPolicy>9</CalculationCoverPolicy>		Forum
Mosha Blog
Disable Prefetch FactsValues: True/False
Optional new parameter to disable the Formula Engine heuristic that sends queries for possibly more data than it is requested.
Example: Disable Prefetch Facts=true;		Blog
Cache RatioValues: 0..1
Optional parameter to control the conversion from a specified set of members to the subcube query sent to the SE Storage Engine. Use 1 to send only the specified list. Example: Cache Ratio=1
Cache ratio example - Cache ratio=0.15, total members at a particular level: 100:
  • If a query requests 2 members, the request ratio is 0.02 (< 0.15). The provider will request each member individually
  • If a query requests 30 members, the request ratio is 0.30 (> 0.15). The provider will request all members at that dimension level instead of 30 members individually.

Example: Cache Ratio=1;

Blog

Webcast

Cache Ratio2Values: 0..1
Works the same way as parameter "Cache Ration" but applies to NonEmptyCrossJoin function or NON EMPTY clause.
Example: Cache Ratio2=0.4;		 
RolesValues: Comma separated string list
Specifies a comma-delimited string of the role names under which a client application connects to an Analysis Services instance. This property lets the user connect using a role other than the one he or she is currently using. For example, a server administrator may want to connect to a cube as a member of a role to test permissions granted to that role. This user must be a member of the role specified in order to connect using this property.
Note: Role names are case-sensitive, and spaces should not be used between the comma-delimited role names. Otherwise errors and unexpected results may be returned by queries to secured cell sets.
Example: Roles=Role1,Role2,Role3;		Forum,
BOL
CustomDataValues: Any string
Specifies information that can be retrieved in SSAS scripts by using MDX function CustomData(). Example: CustomData=appuser1;		Forum
Blog
Default MDX Visual ModeValues: 0,1
If you enable visual totals for the session by setting the default MDX Visual Mode property of the Analysis Services connection string to 1, the Query Execution Engine uses the query cache for all queries issued in that session.
Excel and OWC by default show the visual totals in the Pivot Table, i.e. if some of the hierarchy members are hidden, then the totals reflect only the selected members. Excel before 2007 version achieved that by setting "Default MDX Visual Mode=1" on the connection string, and then let Analysis Services to automatically determine when visual totals are needed.
Example: Default MDX Visual Mode=1;		Mosha Blog
Perf Guide
Cache AuthenticationExample: Cache Authentication=False; 
Impersonation LevelExample: Impersonation Level=Impersonate; 
ModeExample: Mode=ReadWrite; 
MDX CompatibilityExample: MDX Compatibility=0; 
MDX Unique Name StyleValues: 0, 1, 2, 3
For Analysis Services 2000 info here and here.
Not sure about SSAS 2005.
Example: MDX Unique Name Style=0;		 
Non Empty ThresholdExample: Non Empty Threshold=0; 
SQLQueryMode

Values: Calculated/Data
SQL queries return either calculated results or lowest pass results. The lowest pass mode is used by local cubes to process the local cube from server cube's lowest pass data. You need extra permission to fetch lowest pass data.
Example: SQLQueryMode=Calculated;

Forum
Compression LevelExample: Compression Level=0; 
Real Time OlapValues: True/False
Default is false -- if you turn this on, then the query will ignore the in-memory caches of storage engine data and go directly to the source data for each partition (MOLAP will go to MOLAP data and ROLAP will execute relational queries).
Example: Real Time Olap=False;		Forum
Packet SizeExample: Packet Size=4096; 
Integrated SecurityExample: Integrated Security=SSPI; 
Protection LevelExample: Protection Level=Pkt Privacy; 
TimeoutDefault value: 0.
Optional number of seconds for command timeout. This property also determines the maximum time that the instance should wait for an update to a writeback table to be successful before returning an error, equivalent to the connection string property, Writeback Timeout.
This property can help you to avoid very long runs until the queries are optimized to run faster.
Example: Timeout=100;		Blog
BOL
EffectiveUserNameAllows impersonating another user when privilege is available.
Specifies the name of an account to use to override the user name when connecting to an Analysis Services instance. The value of the property is not normalized, in that the MDX UserName function returns the literal value if this property is used. This property can only be used by server administrators.
This property supports the following SID types: User, Group, Alias, WellKnownGroup, Computer.		Forum
MDX Missing Member Mode

Values: Default/Ignore/Error
Indicates whether missing members are ignored in MDX statements. Default option uses value generated by Analysis Services service.
Example: MDX Missing Member Mode=Error;

Forum
BOL
Format

Values: Tabular,Multidimensional,Native
Determines the type of result set that is returned.
Tabular - Returns a result set using the Rowset data type.
Multidimensional - Returns a rowset using the MDDataSet data type.
Native - No format is explicitly specified. Analysis Services returns the appropriate format for the command. The actual result type is identified by the namespace of the result.
Default value is Native
Example: Format=Native;

BOL
Safety OptionsDetermines whether unsafe libraries can be registered and loaded by client applications. The value of this property also determines whether the PASSTHROUGH keyword is allowed in local cubes.BOL
Secured Cell ValueValues: 0..5, Default value: 0
Specifies the error code and the values for the Value and Formatted Value cell properties to be returned when it tries to access a secured cell.		BOL
Visual ModeDefault value 0. This property is equivalent to the OLE DB property, MDPROP_VISUALMODE.
Values:
MDPROPVAL_VISUAL_MODE_DEFAULT - Default mode, provider-specific
MDPROPVAL_VISUAL_MODE_VISUAL - Visual totals on
MDPROPVAL_VISUAL_MODE_VISUALOFF - Visual totals off		BOL
PasswordFor backward compatibility, this property is ignored without generating an error BOL
Encryption Password

Use instead of Password to protect local cube.
Example: Encryption Password=myPassw@rd;

Blog
Application

Specifies client application name.
Example: Application="My custom application name";

 
Extended Properties

Use to make OLE DB Provider to return Extended Cell properties.
Example: Extended Properties="ReturnCellProperties=true"

Blog

Blog2

   
   
   

Note: I did my best to collect information about connection properties in this document, but there are not guarantees that no erros exists here. Please let me know if you find any errors and I'll update this file. This will help other users.
 
Strategy Companion