MicroStrategy ONE

Controlling Access to the Database: Connection Mappings

Connection mappings allow you to assign a user or group in the MicroStrategy system to a login ID on the data warehouse RDBMS. The mappings are typically used to take advantage of one of several RDBMS data security techniques (security views, split fact tables by rows, split fact tables by columns) that you may have already created. For details on these techniques, see Controlling Access to Data at the Database (RDBMS) Level.

Why Use Connection Mappings?

Use a connection mapping if you need to differentiate MicroStrategy users from each other at the data warehouse level or if you need to direct them to separate data warehouses. This is explained in more detail below.

First it is important to know that, as a default, all users in a MicroStrategy project use the same database connection/DSN and database login when connecting to the database. This means that all users have the same security level at the data warehouse and therefore, security views cannot be assigned to a specific MicroStrategy user. In this default configuration, when the database administrator (DBA) uses an RDBMS feature to view a list of users connected to the data warehouse, all MicroStrategy users would all appear with the same name. For example, if forty users are signed on to the MicroStrategy system and running jobs, the DBA sees a list of forty users called "MSTR users" (or whatever name is specified in the default database login). This is shown in the diagram below in which all jobs running against the data warehouse use the "MSTR users" database login.

Creating a Connection Mapping

You define connection mappings with the Project Configuration Editor in Developer. To create a connection mapping, you assign a user or group either a database connection or database login that is different from the default. For information on this, see Connecting to the Data Warehouse.

To Create a Connection Mapping

  1. In Developer, log into your project. You must log in as a user with administrative privileges.
  2. Go to Administration > Projects > Project Configuration.
  3. Expand the Database Instances category, and then select Connection Mapping.
  4. Right-click in the grid and select New to create a new connection mapping.
  5. Double-click the new connection mapping in each column to select the database instance, database connection, database login, and language.
  6. Double-click the new connection mapping in the Users column. Click ... (the browse button).
  7. Select the desired user or group and click OK. That user or group is now associated with the connection mapping.
  8. Click OK.

Connection Mapping Example

One case in which you may want to use connection mappings is if you have existing security views defined in the data warehouse and you want to allow MicroStrategy users' jobs to execute on the data warehouse using those specific login IDs. For example,

  • The CEO can access all data (warehouse login ID = "CEO")
  • All other users have limited access (warehouse login ID = "MSTR users")

In this case, you would need to create a user connection mapping within MicroStrategy for the CEO. To do this:

  • Create a new database login definition for the CEO in MicroStrategy so it matches their existing login ID on the data warehouse
  • Create the new connection mapping in MicroStrategy to specify that the CEO user uses the new database login

This is shown in the diagram below in which the CEO connects as CEO (using the new database login called "CEO") and all other users use the default database login "MSTR users."

Both the CEO and all the other users use the same project, database instance, database connection (and DSN), but the database login is different for the CEO.

If we were to create a connection mapping in the MicroStrategy Tutorial project according to this example, it would look like the diagram below.

For information on creating a new database connection, see Connecting to the Data Warehouse. For information on creating a new database login, see Connecting to the Data Warehouse.

Connection mappings can also be made for user groups and are not limited to individual users. Continuing the example above, if you have a Managers group within the MicroStrategy system that can access most data in the data warehouse (warehouse login ID = "Managers"), you could create another database login and then create another connection mapping to assign it to the Managers user group.

Another case in which you may want to use connection mappings is if you need to have users connect to two data warehouses using the same project. In this case, both data warehouses must have the same structure so that the project works with both. This may be applicable if you have a data warehouse with domestic data and another with foreign data and you want users to be directed to one or the other based on the user group to which they belong when they log in to the MicroStrategy system.

For example, if you have two user groups such that:

  • "US users" connect to the U.S. data warehouse (data warehouse login ID "MSTR users")
  • "Europe users" connect to the London data warehouse (data warehouse login ID "MSTR users")

In this case, you would need to create a user connection mapping within MicroStrategy for both user groups. To do this, you would:

  • Create two database connections in MicroStrategy—one to each data warehouse (this assumes that DSNs already exist for each data warehouse)
  • Create two connection mappings in the MicroStrategy project that link the groups to the different data warehouses via the two new database connection definitions

This is shown in the diagram below.

The project, database instance, and database login can be the same, but the connection mapping specifies different database connections (and therefore, different DSNs) for the two groups.