Report Portal

Analysis Services 2000 and 2005 Connection string properties

Written by Vidas Matelis on . Posted in Connectivity

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 Property Reserved for future use
Authenticated User Property Reserved for future use
Auto Synch Period Property Specifies the OLE DB consumer-requested frequency, in milliseconds, of synchronization between the OLE DB provider and the server. The default frequency is 10000 milliseconds.
Cache Policy Property Reserved for future use
Cache Ratio Property Reserved for future use
Client Cache Size Property Controls the amount of memory used by the client cache
CompareCaseNotSensitiveStringFlags Property Adjusts case-insensitive string comparisons for a specified locale
CompareCaseSensitiveStringFlags Property Adjusts case-sensitive string comparisons for a specified locale
Connect Timeout Property Determines the maximum amount of time the client application will attempt to connect to the server before timing out
CreateCube Property The CREATE CUBE statement to create a local cube file
Data Source Property The name of the server computer or local cube file
Datasource Connection Type Property Describes the type of connection that is currently active
Default GUID Dialect Property Controls the precedence in which language dialects are applied when resolving queries
Default Isolation Mode Property Controls whether the isolation level is isolated or determined by the cursor type requested by the rowset properties
Default MDX Visual Mode Property Determines the default behavior of visual totals
Distinct Measures By Key Property Reserved for future use
Do Not Apply Commands Property Reserved for future use
Execution Location Property Determines the location of query resolution: the client application, server, or a combination
Initial Catalog Property The name of the initial database (catalog)
InsertInto Property The INSERT INTO statement used to populate a local cube file created with the CREATE CUBE statement
Large Level Threshold Property Determines the definition of large level for client/server handling of level members
Locale Identifier Property The locale ID of preference for the client application
Log File Property Specifies a file name for logging queries
MDX Calculated Members Mode Property Reserved for future use
MDX Compatibility Property Determines how empty members are treated for ragged and unbalanced hierarchies
MDX Object Qualification Property Describes how object names are qualified in Microsoft SQL Server™ 2000 Analysis Services
MDX Unique Name Style Property Determines the technique for generating unique names
Mining Execution Location Property Determines the location of query resolution for data mining queries
Mining Location Property Determines the directory in which a local data mining model will be created
Mining Persistence Format Property Determines how data mining models are saved
OLE DB for OLAP Version Property Indicates the version of the OLE DB provider
Password Property Specifies the password to use when connecting using HTTP
Provider Property A predefined string containing other initialization properties
Read Only Session Property Reserved for future use
Restricted Client Property Restricts PivotTable Service from creating local cubes or running deeply recursive queries.
Roles Property Specifies a comma-delimited string of the role names by which a client application connects to the server
Safety Options Property Determines how security for user-defined functions is handled
Secured Cell Value Property Determines the type of return value that results from a reference to a secured cell
Show Hidden Cubes Property Reserved for future use
Source_DSN Property The 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 Property Used 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 Property Reserved for future use
SSPI Property Determines the security package to use during the session
UseExistingFile Property Determines whether a local cube file is overwritten if the connection string contains CREATE CUBE and INSERT INTO statements
User ID Property Specifies a valid user name, such as a valid domain logon or local logon
Writeback Timeout Property Determines 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 Policy Values: 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 Facts Values: 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 Ratio Values: 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 Ratio2 Values: 0..1
Works the same way as parameter "Cache Ration" but applies to NonEmptyCrossJoin function or NON EMPTY clause.
Example: Cache Ratio2=0.4;		  
Roles Values: 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
CustomData Values: Any string
Specifies information that can be retrieved in SSAS scripts by using MDX function CustomData(). Example: CustomData=appuser1;		 Forum
Blog
Default MDX Visual Mode Values: 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 Authentication Example: Cache Authentication=False;  
Impersonation Level The default value is “Impersonate”.
Example: Impersonation Level=Impersonate;		  
Mode Example: Mode=ReadWrite;  
MDX Compatibility Example: MDX Compatibility=0;  
MDX Unique Name Style Values: 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 Threshold Example: 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 Level

Specifies the OLE DB consumer-requested level of compression when the value of the Transport Compression key is “Compressed”, where “0” specifies minimum compression and “9” specifies maximum compression. The valid values are integers between 0 and 9, inclusive. When the value of the Transport Compression key is not “Compressed”, the value of the Compression Level key MUST be ignored if present.
Example: Compression Level=0;

  
Real Time Olap Values: 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 Size Example: Packet Size=4096;  
Integrated Security The valid values are “SSPI” or an empty string. The default value is “SSPI”. The value “SSPI” specifies that Security Support Provider Interface [SSPI] is used as the Authentication Service (AS) for this connection.
Example: Integrated Security=SSPI;		  
Protection Level The supported values are “None”, “Connect”, “Pkt Integrity” and “Pkt Privacy”. The values “Call” and “Pkt” are not supported. The default value is “Pkt Privacy”.
Example: Protection Level=Pkt Privacy;		  
Timeout Default 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
EffectiveUserName Allows 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 Options Determines 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 Value Values: 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 Mode Default 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
Password For 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";
Note: See comments left below:
The property 'Application' is incorrect, it is 'Application Name'. If you use Application by default another property is added to the connection string, called Extended Properties, as well as Application Name. So you end up with 3: Application (your original), Application Name and Extended Properties.

  
Extended Properties

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

Blog

Blog2
Update Isolation Level (For SSAS 2008) When updated cells do not overlap, the Update Isolation Level connection string property can be used to enhance performance for UPDATE CUBE Blog
BeginRange
EndRange		 Using the BeginRange and EndRange connection string properties, you can limit the cells in a cellset that actually get populated with data.  Blog
SSPI Specifies the type of Security Support Provider Interface requested by the OLE DB consumer. For more information about Security Support Provider Interface, see [SSPI]. The valid values are the following: “Negotiate”: Negotiate authentication.
“Kerberos”: Kerberos authentication.
“NTLM”: NT LAN Manager (NTLM) Authentication Protocol authentication. “Anonymous”: No authentication. The default value is “Negotiate”.		  
Protocol Format
 Specifies the OLE DB consumer-requested encoding format for XML messages on this connection. The valid values are the following: “Default”: Binary encoding. “XML”: Text encoding. “Binary”: Binary encoding.  
Transport Compression Specifies whether the OLE DB consumer requests messages on this connection to be compressed. The valid values are the following: “Default”: Messages are compressed. “None”: Messages are not compressed. “Compressed”: Messages are compressed.  
SessionID Specifies the ID of the session to use in this connection  
General Timeout Valid values are unsigned integers between 0 and 65534, inclusive. A value of 0 specifies an infinite time-out. The default value is 0.  
Locale Identifier The LCID specified MUST be supported by the operating system on which the data source object resides.  
Persist Security Info If “true”, the provider MUST persist the value of the Password key if requested to persist the connection information. If “false”, the provider MUST NOT persist the value of the Password key.  
Initial Catalog If a value for this key is not specified in the connection string, the provider MAY default to any database on the data source.  
Data Source The name of the Microsoft SQL Server Analysis Services instance to connect to. The value for this key MUST be specified.  
Connect Timeout Valid values are unsigned integers between 0 and 65534, inclusive. A value of 0 specifies an infinite time-out. The default value is 60 seconds.  
     
     
     
     

SSAS 2008 connection string properties can be found here.

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.

Tags: connectivity

Similar Articles

 

2007-2015 VidasSoft Systems Inc.