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¶
On the Settings page of your SSAS data source, go to the General Settings tab.
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¶
On the Settings page of your SSAS data source, go to the General Settings tab.
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.
Click Save.
Configure SSL Authentication¶
On the Settings page of your SSAS data source, go to the General Settings tab.
In the Authentication section, select Encrypt.
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.
Click Save.
Configure Firewall Connection¶
To configure the firewall connection, perform these steps:
On the Settings page of your SSAS data source, go to the General Settings tab.
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.
Click Save.
Configure Proxy Settings¶
To configure the proxy settings, perform these steps:
On the Settings page of your SSAS data source, go to the General Settings tab.
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 .
Click Save.
Configure Logging¶
To set the logging level for your SSAS data source logs, perform these steps:
On the Settings page of your data source, go to Logging section of General Settings tab.
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:
On the Settings page of your SSAS data source, go to the General Settings tab.
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¶
On the Settings page of your SSAS data source, go to the General Settings tab.
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.
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.