Connection mappings allow users to assign a user or group in the MicroStrategy system to a specific 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 architects may have already created.
Connection mapping is useful in differentiating MicroStrategy users from each other at the data warehouse level or if they need to be directed to separate data warehouses. These two scenarios are described below:
- Connecting to the data warehouse using different database logins
In this scenario, the project, database instance and data warehouse is the same but the connection mapping specifies different database logins for the two groups.For example, the CEO of the company can access all data while all other users have limited access. In the diagram below, the two different logins used are “MSTR users” and “CEO”.
- Connecting to different 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 there exists a data warehouse with domestic data and another with foreign data and users should be directed to one or the other based on the user group to which they belong when they log in to the MicroStrategy system. In the diagram below, the two different database connectionsused are “US ” and “Europe”. Although, the data warehouse login ID in both the database connections is “MSTR Users”, in MicroStrategy the users belong to two different groups – ‘USA Users’ and ‘Europe Users’ respectively.In this scenario, the project, database instance and database login is the same but the connection mapping specifies different databaseconnections (and therefore, different DSNs) for the two groups.
The following steps demonstrate the second scenario where two different data warehouses are used within the same project:
- Create two different database connections – one that points to the data warehouse for the European users and the other that points to the data warehouse for USA users as shown below:
- Select Europe as the default database connection for the database Instance as seen below:
- Go to Project Configuration > Database Instances > Connection Mapping as seen below:
- Click on New to create a new Connection Map and fill in the Criteria for the USA users for their connection mapping, as shown below:
Note: When creating a new connection map, the users can choose from the following two types of database instances.Generally, users or groups are mapped to the data warehouse that has been defined as the default source of data for the project. However, users or groups in the project can also be mapped to another data source (for instance, SAP), since that source of data can also be accessed in the project for specific reporting needs.
For this scenario, make sure to select “Connection Mapping for warehouse” under database Instance..
Connection Mapping for warehouse – Select this option to map users or groups to the default warehouse that contains the data used for this project.
Connection mapping for other database instance – Select this option to map a user or group to a data source that is different from the default data warehouse selected for the project.
- Now under Connection Mapping, both the user groups – Europe and USA will be able to use their own specified database connectionsfrom Step 1 when they access the database instance “Tutorial WH”, as shown below: