SQL Analysis Services Connection

See the Connector Marketplace topic. Please request your administrator to start a trial or subscribe to the Premium SQL Analysis Services connector.

SQL Analysis Services is a component of Microsoft SQL Server facilitating online analytical processing and data mining.

This topic describes how to authenticate to SQL Analysis Services and configure any necessary connection properties in the SQL Analysis Services connection connector.

Prerequisites #

Connecting to Microsoft SQL Server Analysis Services.

To connect, set the URL property to a valid Microsoft SQL Server Analysis Services endpoint and provide authentication.

The connector must connect to Microsoft SQL Server Analysis Services instances hosted over HTTP with XMLA access.

See the Microsoft documentation to configure HTTP access to Microsoft SQL Server Analysis Services.

Authenticating to Microsoft SQL Server Analysis Services

The connector supports the major authentication schemes, including HTTP and Windows.

Set AuthScheme to use the following authentication types.

Anonymous Authentication

If anonymous authentication is available, set AuthScheme to none to avoid authentication.

Basic

Set SQL Server database User and Password and set AuthScheme to “BASIC”.

Windows (NTLM)

Set the Windows User and Password and set AuthScheme to “NTLM”.

Connection Configuration #

Each connection property available in the SQL Analysis Services connector is explained below.

Connection Name #

The name of the connection to be created should be provided. This is the name that will display on the list of available connections.

URL #

The HTTP or HTTPS URL used to connect to the Microsoft SQL Server Analysis Services.

The connector must connect to Microsoft SQL Server Analysis Services instances hosted over HTTP with XMLA access.

See the Microsoft documentation to configure HTTP access to Microsoft SQL Server Analysis Services.

AuthScheme #

The scheme used for authentication. Accepted entries are:

• Basic: Set this to use HTTP Basic authentication.

• Digest: Set this to use HTTP Digest authentication.

User #

The Microsoft SQL Server Analysis Services user account used to authenticate.

Password #

The password used to authenticate the user.

BrowsableSchemas #

This property restricts the schemas reported to a subset of the available schemas.

Example: BrowsableSchemas=SchemaA,SchemaB,SchemaC.

Listing the schemas from databases can be expensive. Providing a list of schemas in the connection string improves the performance.

Enable SSL #

Option for creating the connection with SSL enabled.

Upload Keystore File #

Option to upload keystore file.

Advanced Configurations #

This section contains additional configuration parameters.

Custom Headers #

Other headers as determined by the user (optional).

This property can be set to a string of headers to be appended to the HTTP request headers created from other properties, like ContentType, From, and so on.

The headers must be of the format “header: value” as described in the HTTP specifications. Header lines should be separated by the carriage return and line feed (CRLF) characters.

Use this property with caution. If this property contains invalid headers, HTTP requests may fail.

This property is useful for fine-tuning the functionality of the connector to integrate with specialized or nonstandard APIs.

Extra Properties #

Additional properties to submit on each MDX request to Microsoft SQL Server Analysis Services.

When setting UseMDX to true, properties may be specified using this connection property to fill out extra values in the PropertiesList of the XMLA request. Use name=value pairs separated by a semicolon to submit the properties. Example: Catalog=MyCatalog;Cube=MyCube;.

A list of properties may be found by executing SELECT * FROM $System.DISCOVER_PROPERTIES.

ResponseRowLimit #

The number of response rows to allow before erroring. Set to 0 for no limit.

Selecting a lot of columns results in a number of crossjoins occurring under the hood when translated to something that is acceptable for Microsoft SQL Server Analysis Services. This is not intuitive if you are not familiar with MDX. It can easily result in very large responses that time out. The ResponseRowLimit is designed to try and alert the user to understand what can be very expensive requests.

ExpressionInDescription #

Set this to true to report expressions as part of the description on measure columns.

The connector reports the remarks for several types of entities (dimensions, measures, measure groups and heirarchies) as table and column descriptions. By default, the remarks in measure column descriptions will only be included.

If this option is enabled, then the measure expression is included in the measure column description, along with the remarks. The descriptions on other types of entities are not affected.

Show Hidden Entities #

Set this to true to include hidden dimensions, measures and levels.

By default the connector does not report entities that Microsoft SQL Server Analysis Services marks as hidden. Enabling this option allows you to query them.

Split Measures #

Set this to true to split Measures table into individual tables.

All measures are currently grouped into a single table ‘Measures’. Set this to true to split Measures table into individual tables (if a table only contains measures) and include measures into respective dimensions tables.

Use MDX #

Set this to true to pass MDX queries to Microsoft SQL Server Analysis Services as-is.

You can execute SQL-92 SELECT queries to the views modeled by the connector; set this property to instead execute MDX queries directly to Microsoft SQL Server Analysis Services. If you are using MDX queries directly, it is recommended to also specify the Catalog to ensure MDX queries are submitted to the correct catalog.

Add Configuration: Additional properties can be added using this option as key-value pairs.

After entering all the details, click on the TEST button.

If the connection service identification and authentication details are provided correctly, a success message stating “connection available” is generated.

Click on the CREATE button to save the changes.

If the details are incorrect or the server is down, you will get a message “Connection unavailable”.