Creating Data Source Names

Establishing communication between MicroStrategy and your databases or other data sources is an essential step in configuring MicroStrategy products for reporting and analysis of your data. A data source name (DSN) allows MicroStrategy to connect and communicate to your data sources. For background information on creating and supporting DSNs, see the Installation and Configuration Help.

System Manager allows you to create DSNs for the following types of databases:

• DB2 UDB
• UDB iSeries/DB2 for i
• DB2 z/OS
• Greenplum
• Hive
• Informix
• Informix XPS
• Microsoft SQL Server
• Oracle
• PostgreSQL
• Salesforce
• Sybase ASE

Creating a DSN using System Manager can be successful or unsuccessful for various reasons, which are denoted using exit codes. For information on determining the possible exit codes of creating a DSN using System Manager, see Determining Process Resolution Using Exit Codes.

DB2 UDB

To perform this configuration, in System Manager, from the Connectors and processes pane, add the DB2 UDB process to your workflow. The following information is required to create a DSN for DB2 UDB when running against DB2:

• Data Source Name: A name to identify the DB2 UDB data source configuration in MicroStrategy. For example, Finance or DB2-Serv1 can serve to identify the connection.
• IP Address: The IP address or name of the machine that runs the DB2 UDB server.
• TCP Port: The DB2 UDB server listener's port number. In most cases, the default port number is 50000, but you should check with your database administrator for the correct number.
• Database Name: The name of the database to connect to by default, which is assigned by the database administrator.
• Overwrite: If this check box is selected, the system updates a DSN with the same name with the information provided below. If this check box is cleared and a DSN with the same name exists on the system, no DSN is created, and the DSN is not updated.
• Test Connection: If this check box is selected, the system tests the DSN information provided to determine if a successful connection can be made. If this check box is cleared, no connection test is performed. If this check box is selected, you must provide the following connection information:
  • Username: The name of a valid user for the database.
  • Password: The password for the user name that you provided to connect to the database. You can use the button to the right of the Password field to determine whether the password characters are shown or asterisks are displayed instead.
• Notes: Information to describe this process as part of the workflow.

UDB iSeries/DB2 for i

To perform this configuration, in System Manager, from the Connectors and processes pane, add the DB2 UDB iSeries process to your workflow. The following information is required to create a DSN for UDB iSeries/DB2 for i:

• Data Source Name: A name to identify the DB2 for i data source configuration in MicroStrategy. For example, Finance or DB2fori-1 can serve to identify the connection.
• IP Address: The IP Address of the machine where the catalog tables are stored. This can be either a numeric address, such as 123.456.789.98, or a host name. If you use a host name, it must be in the HOSTS file of the machine or a DNS server.
• Collection: The name that identifies a logical group of database objects.
• Location: The DB2 location name, which is defined during the local DB2 installation.
• Isolation Level: The method by which locks are acquired and released by the system.
• Package Owner: The package's AuthID if you want to specify a fixed user to create and modify the packages on the database. The AuthID must have authority to execute all the SQL in the package.
• TCP Port: The DB2 DRDA listener process's port number on the server host machine provided by your database administrator. The default port number is usually 446.
• Overwrite: If this check box is selected, the system updates a DSN with the same name with the information provided below. If this check box is cleared and a DSN with the same name exists on the system, no DSN is created, and the DSN is not updated.
• Test Connection: Tests the DSN information provided to determine if a successful connection can be made. If this check box is cleared, no connection test is performed. If this check box is selected, you must provide the following connection information:
  • Username: The name of a valid user for the database.
  • Password: The password for the user name that you provided to connect to the database. You can use the button to the right of the Password field to determine whether the password characters are shown or asterisks are displayed instead.
• Notes: Information to describe this process as part of the workflow.

DB2 z/OS

To perform this configuration, in System Manager, from the Connectors and processes pane, add the DB2 z/OS process to your workflow. The following information is required to create a DSN for DB2 z/OS:

• Data Source Name: A name to identify the DB2 z/OS data source configuration in MicroStrategy. For example, Finance or DB2UDBz/OS-1 can serve to identify the connection.
• IP Address: The IP Address of the machine where the catalog tables are stored. This can be either a numeric address such as 123.456.789.98, or a host name. If you use a host name, it must be in the HOSTS file of the machine or a DNS server.
• Collection: The name that identifies a logical group of database objects, which is also the current schema. On DB2 z/OS, the user ID should be used as the Collection.
• Location: The DB2 z/OS location name, which is defined during the local DB2 z/OS installation. To determine the DB2 location, you can run the command DISPLAY DDF.
• Package Collection: The collection or location name where bind packages are created and stored for searching purposes.
• Package Owner: The package's AuthID if you want to specify a fixed user to create and modify the packages on the database. The AuthID must have authority to execute all the SQL in the package.
• TCP Port: The DB2 DRDA listener process's port number on the server host machine provided by your database administrator. The default port number is usually 446.
• Overwrite: If this check box is selected, the system updates a DSN with the same name with the information provided below. If this check box is cleared and a DSN with the same name exists on the system, no DSN is created and the DSN is not updated.
• Test Connection: If this check box is selected, the system tests the DSN information provided to determine if a successful connection can be made. If this check box is cleared, no connection test is performed. If this check box is selected, you must provide the following connection information:
  • Username: The name of a valid user for the database.
  • Password: The password for the user name that you provided to connect to the database. You can use the button to the right of the Password field to determine whether the password characters are shown or asterisks are displayed instead.
• Notes: Information to describe this process as part of the workflow.

Greenplum

To perform this configuration, in System Manager, from the Connectors and processes pane, add the Greenplum process to your workflow. The following information is required to create a DSN for Greenplum:

• Data Source Name: A name to identify the Greenplum data source configuration in MicroStrategy. For example, Finance or Greenplum-1 can serve to identify the connection.
• Host Name: The name or IP address of the machine on which the Greenplum data source resides. The system administrator or database administrator assigns the host name.
• Port Number: The port number for the connection. The default port number for Greenplum is usually 5432. Check with your database administrator for the correct number.
• Database Name: The name of the database to connect to by default. The database administrator assigns the database name.
• Overwrite: If this check box is selected, the system updates a DSN with the same name with the information provided below. If this check box is cleared and a DSN with the same name exists on the system, no DSN is created and the DSN is not updated.
• Test Connection: If this check box is selected, the system tests the DSN information provided to determine if a successful connection can be made. If this check box is cleared, no connection test is performed. If this check box is selected, you must provide the following connection information:
  • Username: The name of a valid user for the database.
  • Password: The password for the user name that you provided to connect to the database. You can use the button to the right of the Password field to determine whether the password characters are shown or asterisks are displayed instead.
• Notes: Information to describe this process as part of the workflow.

Hive

To perform this configuration, in System Manager, from the Connectors and processes pane, add the Hive process to your workflow. The following information is required to create a DSN for Apache Hive:

• Data Source Name: A name to identify the Apache Hive data source configuration in MicroStrategy. For example, Finance or ApacheHive-1 can serve to identify the connection.
• Host Name: The name or IP address of the machine on which the Apache Hive data source resides. The system administrator or database administrator assigns the host name.
• Port Number: The port number for the connection. The default port number for Apache Hive is usually 10000. Check with your database administrator for the correct number.
• Database Name: The name of the database to connect to by default. If no database name is provided, the default database is used for the connection. The database administrator assigns the database name.
• Overwrite: If this check box is selected, the system updates a DSN with the same name with the information provided below. If this check box is cleared and a DSN with the same name exists on the system, no DSN is created and the DSN is not updated.
• Test Connection: If this check box is selected, the system tests the DSN information provided to determine if a successful connection can be made. If this check box is cleared, no connection test is performed.
• Notes: Information to describe this process as part of the workflow.

Informix

To perform this configuration, in System Manager, from the Connectors and processes pane, add the Informix process to your workflow. The following information is required to create a DSN for Informix Wire Protocol:

• Data Source Name: A name to identify the Informix data source configuration in MicroStrategy. For example, Finance or Informix-1 can serve to identify the connection.
• Server Name: The client connection string designating the server and database to be accessed.
• Host Name: The name of the machine on which the Informix server resides. The system administrator or database administrator assigns the host name.
• Port Number: The Informix server listener's port number. The default port number for Informix is commonly 1526.
• Database Name: The name of the database to connect to by default, which is assigned by the database administrator.
• Overwrite: If this check box is selected, the system updates a DSN with the same name with the information provided below. If this check box is cleared and a DSN with the same name exists on the system, no DSN is created and the DSN is not updated.
• Test Connection: If this check box is selected, the system tests the DSN information provided to determine if a successful connection can be made. If this check box is cleared, no connection test is performed. If this check box is selected, you must provide the following connection information:
  • Username: The name of a valid user for the database.
  • Password: The password for the user name that you provided to connect to the database. You can use the button to the right of the Password field to determine whether the password characters are shown or asterisks are displayed instead.
• Notes: Information to describe this process as part of the workflow.

Informix XPS

To perform this configuration, in System Manager, from the Connectors and processes pane, add the Informix XPS (Windows Only) process to your workflow. The following information is required to create a DSN for Informix XPS:

• Data Source Name: A name to identify the Informix data source configuration in MicroStrategy. For example, Finance or Informix-1 can serve to identify the connection.
• Database: The name of the database to connect to by default, which is assigned by the database administrator.
• Server Name: The client connection string designating the server and database to be accessed.
• Host Name: The name of the machine on which the Informix server resides. The system administrator or database administrator assigns the host name.
• Service Name: The service name, as it exists on the host machine. The system administrator assigns the service name.
• Protocol Type: The protocol used to communicate with the server. Select the appropriate protocol from this drop-down list.
• Overwrite: If this check box is selected, the system updates a DSN with the same name with the information provided below. If this check box is cleared and a DSN with the same name exists on the system, no DSN is created and the DSN is not updated.
• Test Connection: If this check box is selected, the system tests the DSN information provided to determine if a successful connection can be made. If this check box is cleared, no connection test is performed. If this check box is selected, you must provide the following connection information:
  • Username: The name of a valid user for the database.
  • Password: The password for the user name that you provided to connect to the database. You can use the button to the right of the Password field to determine whether the password characters are shown or asterisks are displayed instead.
• Notes: Information to describe this process as part of the workflow.

Microsoft SQL Server

To perform this configuration, in System Manager, from the Connectors and processes pane, add the Microsoft SQL Server process to your workflow. The following information is required to create a DSN for Microsoft SQL Server:

• Data Source Name: A name to identify the Microsoft SQL Server data source configuration in MicroStrategy. For example, Personnel or SQLServer-1 can serve to identify the connection.
• Windows: Select this option if you are configuring the Microsoft SQL Server driver on Windows:
  • Server Name: The name of a SQL Server on your network, in the format ServerName_or_IPAddress,PortNumber. For example, if your network supports named servers, you can specify an address such as SQLServer-1,1433. You can also specify the IP address such as 123.45.678.998,1433.

    Additionally, if you use named instances to distinguish SQL Server databases, you can include the named instance along with either the server name or IP address using the format ServerName\NamedInstance or IPAddress\NamedInstance. The following are examples of providing the server name for your SQL Server database:

    123.45.678.998\Instance1,1433

    SQLServer-1\Instance1,1433

  • Database Name: The name of the database to connect to by default. The database administrator assigns the database name.
  • Use Windows NT authentication for login: Select this check box to use Windows NT authentication to pass a user's credentials on the Windows machine to execute against a SQL Server database.

  If you use Windows NT authentication with SQL Server, you must enter the Windows NT account user name and password in Service Manager. For background information on Service Manager, see Running Intelligence Server as an Application or a Service.

• UNIX: Select this option if you are configuring the MicroStrategy-branded version of the Microsoft SQL Server driver for use on UNIX and Linux:

  • Server Name: The name of a SQL Server on your network. For example, if your network supports named servers, you can specify an address such as SQLServer-1. You can also specify the IP address such as 123.45.678.998. Contact your system administrator for the server name or IP address.

    Additionally, if you use named instances to distinguish SQL Server databases, you can include the named instance along with either the server name or IP address using the format ServerName\NamedInstance or IPAddress\NamedInstance. The following are examples of providing the server name for your SQL Server database:

    SQLServer-1\Instance1

    123.45.678.998\Instance1

  • Database Name: The name of the database to connect to by default. The database administrator assigns the database name.
  • Port Number: The port number for the connection. The default port number for SQL Server is usually 1433. Check with your database administrator for the correct number.
  • Enable SQL Database (Azure) support: Defines whether the DSN is created to support SQL Azure. Select this check box if the DSN is used to access a SQL Azure data source.
• Overwrite: If this check box is selected, the system updates a DSN with the same name with the information provided below. If this check box is cleared and a DSN with the same name exists on the system, no DSN is created and the DSN is not updated.
• Test Connection: If this check box is selected, the system tests the DSN information provided to determine if a successful connection can be made. If this check box is cleared, no connection test is performed. If this check box is selected, you must provide the following connection information:
  • Username: The name of a valid user for the database.
  • Password: The password for the user name that you provided to connect to the database. You can use the button to the right of the Password field to determine whether the password characters are shown or asterisks are displayed instead.
• Notes: Information to describe this process as part of the workflow.

Microsoft Access

The MicroStrategy ODBC Driver for SequeLink allows you to access Microsoft Access databases stored on a Windows machine from an Intelligence Server hosted on a UNIX or Linux machine.

Steps on how to perform the necessary configurations on the various machines to support this type of configuration are provided in the Installation and Configuration Help.

To perform this configuration, in System Manager, from the Connectors and processes pane, add the Microsoft Access (Windows Only) process to your workflow. The following information is required to create a DSN for Microsoft Access:

• Data Source Name: A name to identify the Microsoft SQL Server data source configuration in MicroStrategy. For example, Personnel or MicrosoftAccess-1 can serve to identify the connection.
• Database: The name of the database to connect to by default. Click the folder icon to browse to and select a Microsoft Access database.
• Overwrite: If this check box is selected, the system updates a DSN with the same name with the information provided below. If this check box is cleared and a DSN with the same name exists on the system, no DSN is created and the DSN is not updated.
• Test Connection: Tests the DSN information provided to determine if a successful connection can be made. If this check box is cleared, no connection test is performed.
• Notes: Information to describe this process as part of the workflow.

Oracle

To perform this configuration, in System Manager, from the Connectors and processes pane, add the Oracle process to your workflow. The following information is required to create a DSN for Oracle Wire Protocol:

• Data Source Name: A name to identify the Oracle data source configuration in MicroStrategy. For example, Finance or Oracle-1 can serve to identify the connection. A DSN is required for any Oracle Wire Protocol connection. Depending on whether you want to use a standard connection or a TNSNames connection, refer to one of the following lists of options below:

  • Standard Connection: A standard connection is configured through Oracle Wire Protocol with the following connection parameters:
    • Host Name: The name of the Oracle server to be accessed. This can be a server name such as Oracle-1 or an IP address such as 123.456.789.98.
    • Port Number: The Oracle listener port number provided by your database administrator. The default port number is usually 1521.
    • One of the following parameters; which one you choose is up to your personal preference:
      • SID: The Oracle System Identifier for the instance of Oracle running on the server. The default SID is usually ORCL.
      • Service Name: The global database name, which includes the database name and the domain name. For example, if your database name is finance and its domain is business.com the service name is finance.business.com.
  • Alternate Servers: A list of alternate database servers to enable connection failover for the driver. If the primary database server entered as the SID or service name is unavailable, a connection to the servers in this list is attempted until a connection can be established. You can list the servers in SID or service name format, as shown in the following examples:
    • Using an SID: (HostName=DB_server_name: PortNumber=1526:SID=ORCL)
    • Using a Service Name: (HostName=DB_server_name: PortNumber=1526:ServiceName=service.name.com)
  • TNSNames Connection: A TNSNames connection uses a TNSNAMES.ORA file to retrieve host, port number, and SID information from a server (alias or Oracle net service name) listed in the TNSNAMES.ORA file. A TNSNames connection requires the following parameters:
    • Server Name: A server name, which is included in a TNSNAMES.ORA file included in the TNSNames File field below.
    • TNSNames File: The location of your TNSNAMES.ORA file. Make sure to enter the entire path to the TNSNAMES.ORA file, including the file name itself. You can specify multiple TNSNAMES.ORA files.
• Overwrite: If this check box is selected, the system updates a DSN with the same name with the information provided below. If this check box is cleared and a DSN with the same name exists on the system, no DSN is created and the DSN is not updated.
• Test Connection: If this check box is selected, the system tests the DSN information provided to determine if a successful connection can be made. If this check box is cleared, no connection test is performed. If this check box is selected, you must provide the following connection information:
  • User Name: The name of a valid user for the database.
  • Password: The password for the user name you provided to connect to the database. You can use the button to the right of the Password field to determine whether the password characters are shown or asterisks are displayed instead.
• Notes: Information to describe this process as part of the workflow.

PostgreSQL

To perform this configuration, in System Manager, from the Connectors and processes pane, add the PostgreSQL process to your workflow. The following information is required to create a DSN for PostgreSQL:

• Data Source Name: A name to identify the PostgreSQL data source configuration in MicroStrategy. For example, Finance or PostgreSQL-1 can serve to identify the connection.
• Host Name: The name or IP address of the machine on which the PostgreSQL database resides. The system administrator or database administrator assigns the host name.
• Port Number: The port number for the connection. The default port number for PostgreSQL is usually 5432. Check with your database administrator for the correct number.
• Database Name: The name of the database to connect to by default. The database administrator assigns the database name.
• Default User ID: The name of a valid user for the PostgreSQL database.
• Overwrite: If this check box is selected, the system updates a DSN with the same name with the information provided below. If this check box is cleared and a DSN with the same name exists on the system, no DSN is created and the DSN is not updated.
• Test Connection: Tests the DSN information provided to determine if a successful connection can be made. If this check box is cleared, no connection test is performed. If this check box is selected, you must provide the following connection information:
  • Username: The name of a valid user for the database.
  • Password: The password for the default user name that you provided. You can use the button to the right of the Password field to determine whether the password characters are shown or asterisks are displayed instead.
• Notes: Information to describe this process as part of the workflow.

Salesforce

To perform this configuration, in System Manager, from the Connectors and processes pane, add the Salesforce process to your workflow. The following information is required to create a DSN for Salesforce:

• Data Source Name: A name to identify the Salesforce data source configuration in MicroStrategy. For example, Finance or Salesforce-1 can serve to identify the connection.
• Host Name: The host name to connect to Salesforce.com. You can keep the default value of login.salesforce.com.
• Overwrite: If this check box is selected, the system updates a DSN with the same name with the information provided below. If this check box is cleared and a DSN with the same name exists on the system, no DSN is created and the DSN is not updated.
• Test Connection: If this check box is selected, the system tests the DSN information provided to determine if a successful connection can be made. If this check box is cleared, no connection test is performed. If this check box is selected, you must supply the following information to test the connection:
  • Username: The user name of a user account for Salesforce.com. The user name syntax is UserName@salesforce.com, where UserName is the specific user account.
  • Password: The password for the Salesforce.com user account that was supplied. The password syntax is PasswordSecuritytoken, where Password is the password for the user account and Securitytoken is the additional security token required to access Salesforce.com. Do not use any spaces or other characters to separate the password and security token.

    As part of configuring a connection to your Salesforce.com system, you can include the password and security token as part of the database login, which is a component of a database instance used to access the DSN in MicroStrategy. For steps to create a database login, which you can use to provide the Salesforce.com password and security token, see the Installation and Configuration Help.

• Notes: Information to describe this process as part of the workflow.

Sybase ASE

To perform this configuration, in System Manager, from the Connectors and processes pane, add the Sybase ASE process to your workflow. The following information is required to create a DSN for Sybase ASE:

• Data Source Name: A name to identify the Sybase ASE data source configuration in MicroStrategy. For example, Finance or SybaseASE-1 can serve to identify the connection.
• Network Address: The network address, in the format ServerName_or_IPAddress,PortNumber. For example, if your network supports named servers, you can specify an address such as SybaseASE-1,5000. You can also specify the IP address such as 123.456.789.98,5000. Contact your system administrator for the server name or IP address.
• Database Name: The name of the database to connect to by default. The database administrator assigns the database name.
• Enable Unicode support (UTF8): Select this check box if the database supports UNICODE.
• Overwrite: If this check box is selected, the system updates a DSN with the same name with the information provided below. If this check box is cleared and a DSN with the same name exists on the system, no DSN is created and the DSN is not updated.
• Test Connection: If this check box is selected, the system tests the DSN information provided to determine if a successful connection can be made. If this check box is cleared, no connection test is performed. If this check box is selected, you must provide the following connection information:
  • Username: The name of a valid user for the database.
  • Password: The password for the user name that you provided to connect to the database. You can use the button to the right of the Password field to determine whether the password characters are shown or asterisks are displayed instead.
• Notes: Information to describe this process as part of the workflow.