Specifying Warehouse Connection Information

A database instance is a MicroStrategy object, created in MicroStrategy Developer by an administrator, that represents a connection to a data source. A database instance specifies connection information, such as the data source name, Login ID and password, and other data source specific information.

The steps to create the required components of a database instance are provided in the following sections: Creating a database instance, Creating a database connection, and Creating a database login.

When a project architect creates a project, the architect assigns a database instance to that project. A project specifies only one warehouse database instance at a time, but a database instance can be assigned to multiple projects. Since only one data source can be included in the project's relational schema, all reports and documents return information from a single data source.

If you have a license for the MultiSource Option feature, you can connect a project to multiple warehouse database instances. There can be multiple data sources that connect to the Warehouse Catalog for the project. Since these data source can be integrated as part of the project's relational schema, all reports and documents can return information from multiple data sources. For information on accessing multiple data sources in a project, see the Project Design Help.

Regardless of whether you have a license for the MultiSource Option, you can also extend a project's access to multiple data sources through other MicroStrategy features. Freeform SQL, Query Builder, and supporting access through MicroStrategy to other MDX cube sources such as SAP BW, Oracle Essbase, and Microsoft Analysis Services allows non-project database instances to be included and used in a project along with the warehouse database instances. For information on Freeform SQL and Query Builder, see the Advanced Reporting Help. For information on MDX cube sources, see the MDX Cube Reporting Help

These non-project database instances can allow a project to connect to the data sources for the various features and additional data sources mentioned above, instead of accessing the data from the project's relational schema. For more information on the Warehouse Catalog, see the Project Design Help.

SQL data warehouses database instances

A SQL data warehouse database instance is any database instance that connects to a database or other data source through SQL queries. More specifically, this covers database instances used for standard MicroStrategy reporting, Freeform SQL, Query Builder, data marts, and any other relational data source. You can also connect to History List and statistics tables through SQL data warehouse database instances. The SQL data warehouse database instances are available in the Project Configuration Editor, as shown below.

Selecting a database instance check box makes that database instance available in the project for standard MicroStrategy reporting, data marts, Query Builder, and Freeform SQL. If you have a license for the MultiSource Option, selecting a check box for a database instance also makes the database instance available from the Warehouse Catalog to be part of the project's relational schema.

Database instances can be created as part of the Import Data feature. A database instance used for the Import Data feature is displayed with the icon. These database instances are created with security permissions for the user that created them while using the Import Data feature. If you select one of these database instances to be included as an available database instance in the project, it is recommended that you change the security permissions to a MicroStrategy user with administrative privileges. This includes taking ownership of the database instance and defining an appropriate access control list. This ensures that no changes are made to the database instance by other users, which could cause a loss of connectivity to the data source. For information on the Import Data feature, refer to the MicroStrategy Web online help.

The shading and color of a database instance in the list of relational database instances reflects how the database instance is being used in the project:

Blue text: This is the warehouse database instance, as selected from the warehouse database instance drop-down list. There can only be one warehouse database instance for a project, because this database instance's data is populated in the Warehouse Catalog to define the project's relational schema. You cannot choose to disable the warehouse database instance for the project without first selecting a different warehouse database instance.
If you have a license for the MultiSource Option, the primary database instance acts as the main source of data for a project and is used as the default database instance for tables added to the project.
For information on the Warehouse Catalog as well as accessing multiple data sources with the MultiSource Option, see the Project Design Help.
Bold text: The project contains objects that are dependent on the database instance. You cannot choose to disable a database instance that has dependent objects for the project.
Normal text: The database instance is not being used in the project.

Clearing the check box of a database instance removes the database instance from the project and deletes any unused Freeform SQL or Query Builder schemas. You can clear a database instance from a project only if there are no dependent objects in the project for the database instance. For more information on removing a database instance and related Freeform SQL and Query Builder schemas from a project, refer to the System Administration Help.

MDX cube database instances

An MDX cube database instance is any database instance that connects to an MDX cube source, such as SAP BW, Oracle Essbase, or Microsoft Analysis Services. For information on connecting to and reporting on these MDX cube sources, refer to the MDX Cube Reporting Help. The MDX cube database instances are available in the Project Configuration Editor, as shown below.

A database instance that has an MDX cube schema is represented with bold text. The shading and color of a database instance in the list of relational database instances reflects how the database instance is being used in the project:

Bold: The project contains objects that are dependent on the database instance. You cannot choose to disable a database instance that has dependent objects for the project.
Normal: The database instance is not being used in the project.

If you remove an MDX cube database instance from a project, you can delete any unused MDX cube schema objects. You can remove database instance from a project only if there are no dependent objects in the project for the database instance. For more information on removing a database instance and related MDX cube managed objects from a project, refer to the System Administration Help.

For additional information on configuring MDX cube database instances, refer to the MDX Cube Reporting Help.

MDX schema loading and maintenance

You can click Schema Maintenance to perform various tasks for an MDX cube schema that is part of your project, as described below:

You can choose when an MDX cube schema associated with a database instance is loaded for a project. By default, MDX cube schemas are loaded as needed when MDX cube reports are executed. You can also choose to load MDX cube schemas when Intelligence Server starts. For information on defining when MDX cube schemas should be loaded, refer to the MDX Cube Reporting Help.
When you integrate MDX cube sources into MicroStrategy, the data is integrated as an MDX cube schema. Once you integrate an MDX cube source into MicroStrategy, you can exchange the database instance used to connect to the MDX cube schema for a different database instance. This allows you to use different database instances with different login and connection information to access an MDX cube schema. For information on exchanging the database instance used to connect to the MDX cube schema, refer to the MDX Cube Reporting Help.

Creating a database instance

Database instances are created and modified in the Database Instance Manager, which can be found by expanding Administration for a project source, then expanding Configuration Managers. When you choose to create a new database instance, the Database Instances Editor opens.

You can also create a new database instance using the Database Instance Wizard that is available in the Database Instance Manager shortcut menu.

The Database Instances Editor has the following tabs:

General—specifies the database instance name, connection type (data source platform or applicable data source), and default database connection.
The database connection type you choose should match your data source and determines whether the database instance is a relational or an MDX cube database instance.
Advanced—specifies the database name for intermediate table storage if a database other than the warehouse is used to store intermediate tables, as well as other options.
The Advanced tab is not available for MDX cube database instances.
Job Prioritization—specifies the job prioritization scheme for the instance and the number of prioritized connections.

To create a database instance

In MicroStrategy Developer, log in to a project source with administrative privileges.
Go to Administration > Configuration Managers > Database Instances.
From the File menu, go to New > Database Instance. The Database Instances Editor opens.
On the General tab, in the Database instance name field, type the name of the database instance.
In the Database connection type drop-down list, select the data source connection type according to the data source hosting your database.
If you have upgraded from a previous version of MicroStrategy, you can click Upgrade to retrieve any database connection types that have been included since the previous version of MicroStrategy that you used.
On the Advanced tab, you can configure various options for the database instance, including:
- Intermediate table storage: You can specify the database name and table name space to use when intermediate tables are created. Intermediate tables are created to support various queries.
- Database gateway support: You can support backwards compatibility for database gateway support from MicroStrategy version 6.x.
  To enable database gateway support, select the Primary database instance check box, and then select a primary database instance from the drop-down list. The primary database instance is the database instance that should be used for element browsing against the selected table and for queries that do not require joins to other tables. For information on database gateway support, see the Project Design Help.
- Data mart optimization: You can support data mart optimization if the data source for the database instance is in the same data source that contains data marts.
  To enable data mart optimization, select the This database instance is located in the same warehouse as check box, and then select a database instance from the drop-down list.
- Table prefix: If the tables in your data source use a table prefix, you can include the table prefix to identify the proper collection of tables. Click Select to select a table prefix or define a new table prefix.
- ODBC Version: You can define which ODBC version to use for the database instance, as described below:
  - Use 2.0 ODBC Calls: ODBC 2.0 was used in pre-9.0 versions of MicroStrategy. You can use this option for backward compatibility if your database management system does not support ODBC 3.x. This also allows you to use extended fetch to retrieve blocks of data from the database into memory, instead of row by row, which is included in the steps To create a database connection.
  - Use 3.x ODBC Calls: The support of ODBC 3.x is introduced in MicroStrategy 9.0. This value is chosen by default for Database Instances in MicroStrategy 9.x and higher.You should use this option if your database management system supports ODBC 3.x. ODBC 3.x will always use extended fetch (SQLFetchScroll) to retrieve blocks of data from the database into memory rather than row-by-row retrieval.
On the Job Prioritization tab, you can configure how jobs are prioritized for the database instance. For information on configuring job prioritization, see the System Administration Help.
On the General tab, in the Database connection (default) pane, select the default data source connection and click OK.
If the necessary database connection does not exist, you can create one by clicking New. For steps to create a database connection, see Creating a database connection below.

Creating a database connection

A database connection specifies the DSN and database login used to access the data source. A database instance designates one database connection as the default connection for MicroStrategy users; however, users and groups can be mapped to other database connections using connection mapping. For more details on connection mapping, see User connection mapping.

You create database connections in the Database Instances Editor by clicking New on the General tab. Any database connection created within the Database Instances Editor is available for use across all database instances in the project source. For more information on creating a database connection for MDX cube sources, refer to the MDX Cube Reporting Help.

When you choose to create a new database connection, the Database Connections dialog box opens:

The Database Connections dialog box has different options depending on the database instance type:

SQL data warehouse database instances
- General: Specifies the database connection name, the warehouse DSN, and the default database login.
- Advanced: Specifies the database driver mode, driver execution mode, and other miscellaneous warehouse connection settings.
MDX cube database instances
- General: Specifies the database connection name, the default database login, and additional connection information that you must provide. For more information on creating a database connection for MDX cube sources, see the MDX Cube Reporting Help.
- Advanced: Specifies the connection settings, additional connection string parameters, and connection caching settings.

To create a database connection

Before moving forward, ensure that a database instance has been created as described in Creating a database instance.

On the General tab, in the Database connection name box, type a name to identify the database connection.
In the Local system ODBC data sources pane, select the data source name for the data source.
On the Advanced tab, you can define various options per your requirements and the requirements of the database you are connecting to, including:
- Database driver mode: Select one of the following database driver modes:
  - Multi-process: Each connection to the warehouse database is spawned as a separate process, identified in Windows Task Manager as M8DBMPE.exe. If one process fails, such as when a database access thread hangs or is lost, other processes are not affected.
  - Multi-threaded: All connections to the warehouse database are maintained inside the Intelligence Server process MSTRSVR.exe. All connections, SQL submissions, and data retrievals from the database are handled within this process.
- Driver execution mode: Define the driver execution mode depending on the ODBC driver being used:
  - Asynchronous Connection: All statements allocated within the connection should be able to run SQL asynchronously.
  - Asynchronous Statement: For each statement, the asynchronous mode is explicitly set.
  - Synchronous: Only one statement executes at a time. This is the default value.
  Many newer ODBC drivers do not support asynchronous mode because the driver is capable of opening a new thread and executing a new query while simultaneously running an earlier query. The MicroStrategy Readme gives recommendations for the driver execution mode options that can be used for different ODBC drivers.
- Use extended fetch: Select this check box to enable Intelligence server to fetch blocks of data from the database into memory, instead of row-by-row. Be aware that this check box is only available if the database instance is defined to use 2.0 ODBC calls, which is included in the steps To create a database instance. When 3.0 ODBC calls are enabled, extended fetch is already enabled and this option is grayed out.
- Use parameterized queries: Select this check box to enable Intelligence server to pass data to the database in blocks instead of row-by-row. For information on how parameterized queries can improve performance in MicroStrategy, see the Project Design Help.
  In live dashboards connected to certified gateways, as well as reports and cubes executed against a warehouse, all SQL executions containing text input from filters, search conditions, and text prompts are secured through parameterized queries. Starting in 2021 Update 4, this functionality is also extended to transaction inputs in transaction server reports and documents. See KB485174 for more information about the transaction part of this security feature and a list of certified gateways. See KB484512 for more information about the non-transaction part of this security feature and a list of certified gateways.
- Maximum cancel attempt time (sec): Defines the maximum amount of time the MicroStrategy Query Engine waits for a successful attempt before it cancels a query. Values of 0 and -1 indicate no limit.
- Maximum query execution time (sec): Defines the maximum amount of time a single pass of SQL can execute on the database. Values of 0 and -1 indicate no limit.
- Maximum connection attempt time (sec): Defines the maximum amount of time Intelligence Server waits to connect to the database. Values of 0 and -1 indicate no limit.
- Additional connection string parameters: Enables you to pass additional ODBC connection parameters to the database as part of the connection string. This is useful if you need to change ODBC defaults. Click Preview to see the entire connection string.
- Table prefix: Defines a table prefix that specifies the schema containing the tables to access.
- Character set encoding for Windows drivers: The options listed below are only relevant when Intelligence Server is running on a Windows machine:
  - Non UTF-8 (default): Select this option if the ODBC driver returns information in a character encoding other than UTF-8.
  - UTF-8: Select this option if the ODBC driver returns information in UTF-8 character encoding. Drivers for Teradata databases may require UTF-8 encoding.
- Character set encoding for UNIX drivers: The options listed below are only relevant when Intelligence Server is running on a UNIX machine:
  - Non UTF-8: Select this option if the ODBC driver returns information in a character encoding other than UTF-8.
  - UTF-8 (default): Select this option if the ODBC driver returns information in UTF-8 character encoding. Drivers for Teradata databases may require UTF-8 encoding.
- Connection Caching: Specify the caching of the database connection using the following options:
  - Connection idle timeout (sec): Defines the amount of time an inactive connection to the database remains cached until it is terminated. You must also set the Connection lifetime, described above, to a value greater than zero for database connections to be used by more than a single job.
On the General tab, in the Default database login name pane, select the default database login and click OK.
If the necessary database login does not exist, you can create one by clicking New. For steps to create a database connection, see Creating a database login below.

Creating a database login

A database login specifies the user ID and password used to access the data source. The database login overwrites any login information stored in the DSN. A database connection designates one database login as the default login for MicroStrategy users, however users and groups can be mapped to other database logins using connection mapping.

Connection mapping is explained in User connection mapping.

You create database logins in the Database Connections dialog box by clicking New on the General tab. Any database login created within the Database Connections dialog box is available for use across all database connections in the project source.

MicroStrategy reporting and analysis features require a general set of database login permissions that can connect to and modify the data source and metadata, as described below:

For the metadata, the Select, Insert, and Update permissions are required. Intermediate tables are created in the metadata for recursive search queries, which requires Create and Drop permissions as well. Updating the schema requires the Delete permission.
For the data warehouse, the Select, Create, Insert, and Drop permissions are required.

When you choose to create a new database login, the Database logins dialog box opens:

To create a database login

Before moving forward, ensure that the following is complete:

A database instance has been created, as described in Creating a database instance.
A database connection has been created, as described in Creating a database connection.

In the Database Login field, type the name of the database login.
Provide the user ID and password required to access the data source, using one of the following methods:
- Type the user ID in the Login ID field, and type the password for that user ID in the Password field.
- Select the Use network login ID check box to connect to the data source using the network user credentials which are also used to run Intelligence Server. If Intelligence Server is running as a service, this is the user that is running the mstrsvr.exe process. To determine this user, in MicroStrategy Service Manager, select MicroStrategy Intelligence Server and click Options. The user is listed on the Service Startup tab, in the Login field. If the Service Account Name is defined as System Account, the Windows user credentials are used to access the data source.
Click OK.

Database logins are passed to the data source any time a user executes a report or browses attribute elements. Therefore, all database logins created in MicroStrategy Developer must be also be created as valid logins in the data source.

User connection mapping

User connection mapping is the process of mapping MicroStrategy users to database connections and database logins. For MicroStrategy users to execute reports, they must be mapped to a database connection and database login.

MicroStrategy users link to database connections and logins using:

The default database connection (and, therefore, default database login)
Specialized maps to a database connection and/or database login (different than the default connection and login) for either a user or user group

You can map users to connections and logins in the Project Configuration Editor or Command Manager. For information about how connection maps are used, see the System Administration Help.

MicroStrategy reporting and analysis features require a general set of database login permissions to connect to and modify the data warehouse and metadata, as described below:

For the metadata, the Select, Insert, and Update permissions are required. Intermediate tables are created in the metadata for recursive search queries, which requires Create and Drop permissions as well. Updating the schema requires the Delete permission.
For the data warehouse, the Select, Create, Insert, and Drop permissions are required.

To create a connection map