Configure the Data Source Connection

Alation Cloud Service Applies to Alation Cloud Service instances of Alation

Customer Managed Applies to customer-managed instances of Alation

After you install the SQL Server Analysis Services (SSAS) OCF connector, you must configure the connection to the SSAS data source.

The various steps involved in configuring the SSAS data source connection setting are:

Provide Access

To set the data source visibility,go to the Access tab on the Settings page of your SSAS data source, set the data source visibility using these options:

  • Public Data Source — The data source is visible to all users of the catalog.

  • Private Data Source — The data source is visible to the users allowed access to the data source by Data Source Admins.

You can add new Data Source Admin users in the Data Source Admins section.

Connect to Data Source

To establish the a connection to data source, you must:

Provide the JDBC URI

JDBC URI Format

ssas://User=myuseraccount;Password=mypassword;URL=http://localhost/OLAP/msmdpump.dll;

Important

We recommend that you provide the values in the corresponding fields on the General Settings page instead of the JDBC URI field in the Datasource Connection section. Leave this field empty if all the connection properties you need are available in the user interface.

Configure Authentication

For metadata extraction (MDE), profiling and sampling, and query log ingestion (QLI), the connector supports the following authentication methods:

  • Basic, NTLM, and Digest (URL, username, and password)

  • Kerberos

  • SSL

Configure Basic Authentication

  1. On the Settings page of your SSAS data source, go to the General Settings tab.

  2. In the Connector Settings section, provide the following details in the Authentication section.

    Parameter

    Description

    Auth Scheme

    Specify an authentication scheme that MongoDB uses to authenticate the connection.

    Available Options:

    • NTLM: Use this to use your Windows credentials to authenticate (Default).

    • Basic: Use this to use HTTP Basic authentication.

    • Digest: Set this to use HTTP Digest authentication. function.

    • Negotiate: Use this option for Kerberos authentication. This option lets the provider negotiate an authentication mechanism with the server.

    • NONE: Default.

    URL

    Specify the URL to connect to the Microsoft SQL Server Analysis Services. Example: http://localhost/OLAP/msmdpump.dl

    User

    Specify the username of Microsoft SQL Server Analysis Services user account.

    Password

    Specify the password of the user account.

If you choose to encrypt using SSL, configure SSL. For details, see Configure SSL Authentication.

Configure Kerberos

  1. On the Settings page of your SSAS data source, go to the General Settings tab.

  2. In the Kerberos section, provide the following details:

    Parameter

    Description

    Kerberos KDC

    Specify the Kerberos Key Distribution Center (KDC) service name. You can locate it with the domain controller.

    If unspecified, the KRB5_CONFIG environment variable uses the default MIT location to fetch the KRB5 config file to get the Kerberos properties.

    • For Windows, C:ProgramDataMITKerberos5krb5.ini

    • For Linux, /etc/krb5.conf

    Kerberos Realm

    Specify the Kerberos realm as defined by the administrator or domain name.

    If unspecified, the KRB5_CONFIG environment variable uses the default MIT location to fetch the KRB5 config file to get the Kerberos properties.

    • For Windows, C:ProgramDataMITKerberos5krb5.ini

    • For Linux, /etc/krb5.conf

    Kerberos SPN

    Specify the service principal name (SPN) for the Kerberos Domain Controller.

    Set this if the URL that you are authenticating to is different.

    Kerberos Keytab File

    Specify the Keytab file name that contains the pairs of Kerberos principals and encrypted keys.

    Kerberos Service Realm

    Specify the service that Kerberos realm uses if you’re using cross-realm Kerberos authentication.

    Kerberos Service KDC

    Specify the service that Kerberos KDC if you’re using cross-realm Kerberos authentication.

    Kerberos Ticket Cache

    Specify the file path to an MIT Kerberos credential cache file.

  3. Click Save.

Configure SSL Authentication

  1. On the Settings page of your SSAS data source, go to the General Settings tab.

  2. In the Authentication section, select Encrypt.

  3. In the Connector Settings section, provide the following details in the SSL section.

    Parameter

    Description

    SSL Client Cert

    Specify the name of the certificate store for the client certificate. Add the content of the certificate file and select the appropriate file type from the options under SSL Client Cert Type.

    SSL Client Cert Type

    Specify the type of key store that contains the SSL client certificate.

    Available Options:

    • USER: (Default) For Windows, this specifies that the certificate store is a certificate store owned by the current user. Note that this store type is not available in Java.

    • MACHINE: For Windows, this specifies that the certificate store is a machine store. Note that this store type is not available in Java.

    • PFXFILE: The certificate store is the name of a PFX (PKCS12) file containing certificates.

    • JKSFILE: The certificate store is the name of a Java key store (JKS) file containing certificates. Note that this store type is only available in Java.

    • PEMKEY_FILE: The certificate store is the name of a PEM-encoded file that contains a private key and an optional certificate.

    • PUBLIC_KEY_FILE: The certificate store is the name of a file that contains a PEM- or DER-encoded public key certificate.

    • SSHPUBLIC_KEY_FILE: The certificate store is the name of a file that contains an SSH-style public key.

    • P7BFILE: The certificate store is the name of a PKCS7 file containing certificates.

    SSL Client Cert Password

    Specify the password for the client certificate. If the certificate store is of a type that requires a password, this property is used to specify that password to open the certificate store.

    SSL Client Cert Subject

    Specify the subject of the client certificate. The subject is a comma separated list of distinguished name fields and values.

    Consider the following points:

    • If an exact match is not found, the store is searched for subjects containing the value of the property.

    • If a match is still not found, the property is set to an empty string, and no certificate is selected.

    • The special value “*” picks the first certificate in the certificate store.

    SSL Server Cert

    Specify the certificate that Alation must accept from the server to connect using TLS/SSL. Specify the full certificate.

    Important

    SSL Server Certificate isn’t supported. If you use a server certificate for connection, contact Alation Support.

  4. Click Save.

Configure Firewall Connection

To configure the firewall connection, perform these steps:

  1. On the Settings page of your SSAS data source, go to the General Settings tab.

  2. In the Firewall section, provide the following information:

    Field

    Description

    Firewall Type

    Specify the protocol to be used by a proxy-based firewall. - NONE

    • TUNNEL — Indicates that the driver opens a connection to SSAS, and traffic flows back and forth through the proxy. The default port is 80.

    • SOCKS4 - Indicates that the driver sends data through the SOCKS 4 proxy specified by the Firewall Server and Firewall Port and passes the user value to the proxy, determining whether to grant the connection request. The default port is 1080.

    • SOCKS5 — Indicates that the driver sends data through the SOCKS 5 proxy specified by Firewall Server and Firewall Port. The default port is 1080.

    Firewall Port

    Specify the TCP port for a proxy-based firewall.

    Firewall User

    Specify a username to authenticate to a proxy-based firewall.

    Firewall Password

    Specify a password used to authenticate to a proxy-based firewall.

  3. Click Save.

Configure Proxy Settings

To configure the proxy settings, perform these steps:

  1. On the Settings page of your SSAS data source, go to the General Settings tab.

  2. In the Proxy section, provide the following information:

    Field

    Description

    Proxy Auto Detect

    Select the Proxy Auto Detect check box to use the system proxy settings. This parameter takes precedence over other proxy settings. Therefore, clear the Proxy Auto Detect checkbox to use custom proxy settings.

    Proxy Server

    Specify a proxy server’s hostname or IP address to route HTTP traffic through. By default, Alation uses the system proxy. To use another proxy, clear the Proxy Auto Detect checkbox.

    Proxy Port

    Specify the TCP port on which the Proxy server runs.

    Proxy Auth Scheme

    Specify the authentication type to authenticate to the Proxy Server proxy. BASIC — Indicates HTTP BASIC authentication.

    DIGEST — Indicates HTTP DIGEST authentication.

    NEGOTIATE - Uses an NTLM or Kerberos token based on the applicable protocol for authentication.

    PROPRIETARY — Does not generate an NTLM or Kerberos token. You must supply this token in the Authorization header of the HTTP request.

    Proxy User

    Specify a user name to use to authenticate. to the Proxy Server proxy.

    Proxy Password

    Specify a password to use to authenticate to the Proxy Server proxy.

    Proxy SSL Type

    Specify the SSL type to use when connecting to the Proxy Server proxy.

    AUTO — Indicates that if the URL is an HTTPS URL, the connection uses the TUNNEL. option. If the URL is an HTTP URL, the component uses the NEVER option.

    ALWAYS — Indicates that the connection

    is always SSL enabled.

    NEVER — Indicates that the connection is

    not SSL enabled.

    TUNNEL — Indicates that the connection is through a tunneling proxy. The proxy server opens a connection to the remote host and traffic flows back and forth through the proxy.

    Proxy Exceptions

    Specify a semicolon separated list of destination hostnames or IPs exempted from connecting through the Proxy Server .

  3. Click Save.

Configure Logging

To set the logging level for your SSAS data source logs, perform these steps:

  1. On the Settings page of your data source, go to Logging section of General Settings tab.

  2. Provide the following information and click Save.

    Parameter

    Description

    Verbosity

    Specify the verbosity level between 1 to 5 to include details in the log file.

    Log Modules

    Includes the core modules in the log files. Add module names separated by a semi-colon.

    By default, all modules are included.

    Max Log File Count

    Specify the maximum file count for log files.

    After the limit, the log file is rolled over and time is appended at the end of the file. The oldest log file is deleted.

    Maximum Value: 2

    Default: -1. A negative or zero value indicates unlimited files.

You can view the connector logs in Admin Settings > Server Admin > Manage Connectors > SSAS OCF Connector.

Configure Schema Properties

To configure schema properties, perform these steps:

  1. On the Settings page of your SSAS data source, go to the General Settings tab.

  2. In the Schema section, provide the following information:

    Parameter

    Description

    Browsable Schemas

    Specify the schemas as a subset of the available schemas in a comma-separated list. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.

    Tables

    Specify the fully qualified name of the table as a subset of the available tables in a comma-separated list.

    Each table must be a valid SQL identifier that might contain special characters escaped using square brackets, double quotes, or backticks. For example, Tables=TableA,[TableB/WithSlash],WithCatalog.WithSchema.`TableC With Space`.

    Views

    Specify the fully qualified name of the Views as a subset of the available tables in a comma-separated list.

    Each view must be a valid SQL identifier that might contain special characters escaped using square brackets, double quotes, or backticks. For example, Views=ViewA,[ViewB/WithSlash],WithCatalog.WithSchema.`ViewC With Space`.

Configure Additional Connection Settings

Apart from the mandatory configurations that you perform to connect to the data source on the General Settings tab, configure the following additional settings:

Configure Additional Data Source Connections

Alation can associate objects in a data source with objects in another source in the catalog through lineage. For example, you can show lineage between your data source and BI sources that use its data.

Provide additional connection information for the data source to see lineage across multiple sources on the Lineage chart.

On the Application Settings section of General Settings tab, provide the host and port information in the Additional data source connection field.

This parameter is used to generate lineage between the current data source and another source in the catalog, for example a BI source that retrieves data from the underlying database. The parameter accepts host and port information of the corresponding BI data source connection.

For more details, see Configure Cross-Source Lineage.

Enable or Disable Automatic Lineage Generation

You can enable or disable the lineage for the data source to be generated automatically during metadata extraction, query log ingestion, and from Data Definition Language queries run by users in Compose.

Go to General Settings > Advanced settings of the Settings page of your SSAS data source and enable or disable the Disable automatic lineage generation toggle.

Disable the Disable automatic lineage generation toggle when you want to automatically generate the lineage.

Enable this option when you do not want lineage to be automatically generated and prefer to create lineage manually or using an API.

By default, automatic lineage generation is enabled.

Configure Miscellaneous Settings

  1. On the Settings page of your SSAS data source, go to the General Settings tab.

  2. In the Misc section, provide the following details:

    Parameter

    Description

    Batch Size

    Specify the maximum size of each batch operation to submit.

    Catalog

    Specify the Analysis Services catalog to use. This may also be known as a Database from within Analysis Services.

    Connection Life Time

    Specify the maximum lifetime of a connection in seconds. Once the time has elapsed, the connection object is disposed.

    Custom Headers

    Specify other headers determined by you (optional).

    Expose Member Keys

    Select to determine if each level should be converted into a measure, allowing calculations to be performed on the measure.

    Expression In Description

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

    Extra Properties

    Specify the additional properties to submit on each MDX request to Microsoft SQL Server Analysis Services.

    Include Join Columns

    Select this to include extra join columns on each table.

    Max Rows

    Specify the limit to the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.

    Other

    Specify other properties used for specific use cases.

    Pool Idle Timeout

    Specify the allowed idle time for a connection before it is closed. The default value is 60 seconds.

    Pool Max Size

    Specify the maximum connections in the pool. To disable, set the value to 0 or less. The default is 100.

    Pool Min Size

    Specify the minimum number of connections in the pool. The default is 1.

    Pool Wait Time

    Specify the maximum seconds to wait for an available connection. The default value is 60 seconds.

    Response Row Limit

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

    Show Hidden Entities

    Select this to include hidden dimensions, measures and levels.

    Split Measures

    Select this to split the Measures table into individual tables.

    Split Measures On

    Specify this property in conjunction with Split Measures to set the priority for how measures should be organized into tables.

    Timeout

    Specify the value in seconds until the timeout error is thrown, canceling the operation. A value of 0 specifies that the operation never times out until completion or failure. Default value is 60 seconds.

    Use Connection Pooling

    Select this to enable connection pooling.

    Use MDX

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

    User Defined Views

    Specify the file path pointing to the JSON configuration file containing your custom views.

  3. Click Save.

Disable Obfuscate Literals

You can hide literal values from queries ingested with query log ingestion and displayed on the Queries tab of a schema and table catalog objects.

Go to General Settings > Obfuscate Literals of the Settings page of your SSAS data source and disable the Obfuscate literals toggle.

When enabled, literal values are substituted with placeholder values. Disable this option when you want literal values in queries to be visible to users.

By default, this option is disabled.

Test the Connection

The connection test checks database connectivity.

After configuring authentication, test the connection.

To validate the network connectivity, go to General Settings > Test Connection of the Settings page of your SSAS data source and click Test.

A dialog box appears confirming the status of the connection test.