Connector for Microsoft SQL Server Analysis Services – Features and settings

You can connect to and access data from Microsoft SQL Server Analysis Services. On this page, you can find information about the capabilities, available settings, and things to keep in mind when you work with data connections to Microsoft SQL Server Analysis Services.

Page content

Connector features

The following functionality is available when you access data with the connector for Microsoft SQL Server Analysis Services.


Feature	Supported?
Load methods	Import (in-memory) External (in-database)
Custom queries	No
Stored procedures	No
Custom connection properties	No
Single sign-on with identity provider	No
Authoring in web client	No
Included in Spotfire distribution on Linux	No

Data source properties

The following are the supported data source properties that you can configure when you create a data connection with the connector for Microsoft SQL Server Analysis Services.


Option	Description
Server	The name of the server where your data is located. To include a port number, add it directly after the name preceded by colon. To include an instance name, add it directly after the server name preceded by backslash. Example with port number: `myDatabaseServer:1234` Example with instance name: `MyDatabaseServer\InstanceName`
Authentication method	The authentication method to use when logging in to the external data source. Choose from: Windows authentication Username and password Note: You can use Username and password authentication only if you connect to Microsoft SQL Server Analysis Services over HTTP, for example when you connect to an Azure Analysis Services instance. If you are not connecting over HTTP, the server ignores username and password information and uses Windows authentication instead.
Database	Select the database of interest from the drop-down list.
Cube	Select the cube of interest from the drop-down list.
Preferred language	If localized data exist in the cube, you can select your preferred language to use when analyzing the data in Spotfire.
Command timeout (seconds)	[You can only configure the settings for this option after you have clicked Connect on the General tab.] Specifies the maximum time, in seconds, allowed for a command to be executed. Note: By default, this field is left blank, and the default timeout settings in the data source driver will be used.
Allow sets in MDX where clauses	[You can only configure the settings for this option after you have clicked Connect on the General tab.] Because of limitations in Microsoft SQL Server Analysis Services, errors can sometimes occur in the external data source when queries that include MDX sets are generated from Spotfire. If you encounter such errors in your connection, you can clear this check box. When the check box is cleared, queries from Spotfire to the external data source are formatted differently, in a way that can resolve such errors related to MDX sets. By default, this check box is selected.

Supported data types

When you are setting up a connection to an external data source, Spotfire needs to map the data types in the data source to data types in Spotfire. The following are the data types that the Microsoft SQL Server Analysis Services connector supports.


Database data type	Spotfire data type
Binary	Binary
Boolean	Boolean/Bool
Currency	Currency
Date	DateTime
Integer	Integer
SmallInt	Integer
TinyInt	Integer
UnsignedInt	Integer
UnsignedSmallInt	Integer
UnsignedTinyInt	Integer
BigInt	LongInteger/Long
UnsignedBigInt	LongInteger/Long
Double	Real/Double
Single	SingleReal/Float
WChar	String