Connection Mappings in Microstrategy

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.
external image Connection_mapping_default.gif

external image Connection_mapping_default.gif

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

In Developer, log into your project. You must log in as a user with administrative privileges.

From the Administration menu, point to Projects, and select Project Configuration. The Project Configuration Editor opens.

Expand the Database Instances category, and then select Connection Mapping.

Right-click in the grid and select New to create a new connection mapping.

Double-click the new connection mapping in each column to select the database instance, database connection, database login, and language.

Double-click the new connection mapping in the Users column. Click ... (the browse button). The Add Members dialog box opens.

Select the desired user or group and click OK. That user or group is now associated with the connection mapping.

Click OK to close the Project Configuration Editor. The new connection mapping is saved.

Connection mapping example

One case in which you may wish to use connection mappings is if you have existing security views defined in the data warehouse and you wish 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 his or her 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.”
external image Connection_mapping_CEO_vs_users.gif

external image Connection_mapping_CEO_vs_users.gif

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.
external image Connection_mapping_screen_shot.gif

external image Connection_mapping_screen_shot.gif

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.
external image Connection_mapping_US_vs_Europe_users.gif

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.

Comments

MicroStrategy URL API Parameters

MicroStrategy URL Structure The following table summarizes the root URL structure used for every request to MicroStrategy Web. Environment Main Application URL Administration URL J2EE http://webserver/MicroStrategy/servlet/mstrWeb http://webserver/MicroStrategy/servlet/mstrWebAdmin .NET http://webserver/MicroStrategy/asp/Main.aspx http://webserver/MicroStrategy/asp/Admin.aspx Every request sent to MicroStrategy Web calls a central controller. Parameters are appended to Main.aspx or mstrWeb (in a .NET and J2EE environment, respectively) to indicate to the controller how the request should be internally forwarded and handled. The following examples show a URL for accessing a MicroStrategy folder when the user does not have an existing session. The URL contains not only the parameters needed to connect to MicroStrategy Web, but also the parameters needed to log on and create a session. J2EE environment: <a href="http:...

Case functions Microstrategy

Ca se functions Microstrategy Case functions return specified data in a SQL query based on the evaluation of user-defined conditions. In general, a user specifies a list of conditions and corresponding return values. Case This function evaluates multiple expressions until a condition is determined to be true, then returns a corresponding value. If all conditions are false, a default value is returned. Case can be used for categorizing data based on multiple conditions. This is a single-value function. Syntax Case ( Condition1 , ReturnValue1 , Condition2 , ReturnValue2 ,..., DefaultValue ) Example Case(([Total Revenue] < 300000), 0, ([Total Revenue] < 600000), 1, 2) sum(Case (Day@DESC in (“Sat”,”Sun”), Sales, 0) {~+} Sum(Case(Category@DESC In("Books","Electronics"),Revenue,0)){~+} CaseV (case vector) CaseV evaluates a single metric and returns different values according to the results. It can be used to perfo...

Apply or Pass-through functions in Microstrategy

Ap ply (Pass-Through) functions MSTR Apply functions provide access to functions or syntactic constructs that are not standard in MicroStrategy but are provided by various RDBMS systems.. Syntax common to Apply functions Apply Function Name ("expression with placeholders", Arg1, Arg2, Arg3, …ArgN) where: Apply Function Name – is a generic name used for the predefined pass-through functions described above expression with placeholders – is the string describing the actual expression or syntax that the engine uses while generating the SQL and which is sent to the RDBMS. The placeholders are represented by #0, #1, and so on. "#" is a reserved character for MicroStrategy. Arg – is an argument that replaces the parameter markers in the pattern. Arg1 replaces #0, Arg2 replaces #1, and so on. There are five pre-defined Apply functions to replace regular, predefined functions of the same type. For more details, cli...

Microstrategy Dossiers explained

Microstrategy Dossiers With the release of MicroStrategy 10.9, we’ve taken a leap forward in our dashboarding capabilities by simplifying the user experience, adding storytelling, and collaboration.MSTR has evolved dashboards to the point that they are more than dashboards - they are interactive, collaborative analytic stories . Ultimately, it was time to go beyond dashboards, both in concept and in name, and so the've renamed VI dashboards to ‘ dossiers ’. Dossiers can be created by using the new Desktop product or Workstation or simply from the Web interface which replaces Visual Insights. All the existing visual Insights dashboards will be converted to Dossiers With MicroStrategy 10.9, there was an active focus on making it easier to build dashboards for the widest audience of end users. To achieve this, some key new capabilities were added that make it easier to author, read, interact and collaborate on dashboards ...

Create an alert-based subscription in MicroStrategy Distribution Services

Create an alert-based subscription in MicroStrategy Distribution Services on Web Subscription to a report or Report Services document which will be executed when a certain conditional threshold is met based on another executing report. For example, a scheduled report executes which shows the Revenue by day for the past week. If the Revenue on any one day falls below a certain value, a subscription to another report or Report Services document can be triggered and delivered to a recipient. An alert based subscription can only be created directly on a report; however, another report or Report Services document can be delivered when the alert based subscription is triggered. Note: you need a grid report to create an alert and you cannot create if you want to create on a document with text boxes. The following example will walk through the basic steps on how to setup a subscription based on an alert like this: Follow the brief steps bel...

Microstrategy Document Autotext macros:

Autotext code/macros in Microstrategy Document/dashboard This is a list of the available auto text macros that the Report Services Document engine recognizes. The following auto text codes allow you to add document variable information to your document. These auto text codes are automatically replaced by information about the document. Auto text codes for MSTR document/dashboard: AUTOTEXT DESCRIPTION {&PAGE} Display the current page. {&NPAGES} Display the total number of pages. {&DATETIME} Display the current date and time. {&USER} Display the user name that is executing the Report Services Document. {&DOCUMENT} Display the document name. {&DOCUMENTID} Display the document ID. {&DESCRIPTION} Display the document description. {&PROJECT} Display the project name. {&EXECUTIONTIME} Dis...

Settings for Outer Join between metrics in MicroStrategy

Settings for Outer Join between metrics in MicroStrategy MicroStrategy adopts multi-pass logic to determine the execution plan for a report. This means that every metric is evaluated in separate SQL passes. Outer Joins come into play when MicroStrategy Engine merges the results from all SQL passes into one report. For a multi-pass report, different Outer Join behaviors can give the user completely different results. In addition, report metrics can be of different types which can, in some cases, influence the result of the outer join. In MicroStrategy, there are two settings that users can access to control Outer Join behavior : Formula Join Type and Metric Join Type . Metric Join Type: VLDB Setting at Database Instance Level Report and Template Levels Report Editor > Data > Report Data Options Metric Level Metric editor > Tools > Metric Join Type Control Join between Metrics Formula Join Type: Only at Compound/Split...

Create a Visualization Filter in a Dossier

Create a Visualization Filter You can select attribute values to filter data that appears in a visualization by creating an attribute filter. Create a visualization filter by dragging attributes and metrics from the Datasets panel. To create an attribute filter Open the dossier you want to modify. Select the chapter that contains the visualization to filter. Click Filter . An empty filter appears in the dossier. Drag the attributes and metrics for filtering from the Datasets panel to the empty filter. Use the Ctrl key to select multiple attributes and metrics. In the filter, click Select Target . Click on target visualizations or visualization filters to select them. You can select targets across chapters and pages, as long as they are all on the same page. Click Apply . Click More in the filter and select options for modifying the filter. The available options are based on the objects you select.

Personalizing file locations, email and file subscriptions using macros in Microstrategy

Personalizing file locations MSTr allows to dynamically specify the File Location and Backup File Location in a file device using macros. For example, if you specify the File Location as C:\Reports\{&RecipientName}\ , all subscriptions using that file device are delivered to subfolders of C:\Reports\ . Subscribed reports or documents for each recipient are delivered to a subfolder with that recipient’s name, such as C:\Reports\Jane Smith\ or C:\Reports\Hiro Protagonist\ . The table below lists the macros that can be used in the File Location and Backup File Location fields in a file device: Description Macro Date on which the subscription is sent {&Date} Time at which the subscription is sent {&Time} Name of the recipient {&RecipientName} User ID (32-character GUID) of the recipient {&RecipientID} Distribution Services add...

Types of prompts in Microstrategy

Types of prompts in Microstrategy The different types of prompts allow you to create a prompt for nearly every part of a report. Prompts can be used in many objects including reports, filters, metrics, and custom groups, but all prompts require user interaction when the report is executed. The correct prompt type to create depends on what report objects you want users to be able to base a filter on to filter data, as described in the list below. Filter definition prompts allow users to determine how the report's data is filtered, based on one of the following objects: Attributes in a hierarchy : Users can select prompt answers from one or more attribute elements from one or more attributes. The attribute elements that they select are used to filter data displayed on the report. This prompt lets you give users the largest number of attribute elements to choose from when they answer the prompt to define their filtering criteria. For example, on a repor...

Microstrategy Analytics

Search This Blog