MicroStrategy Architecture

09 October, 2020

Related Blogs

Microstrategy Intelligence Server

MicroStrategy Architecture

A MicroStrategy system is built around a three-tier or four-tier structure. The diagram below illustrates a four-tier system.

MicroStrategy metadata

MicroStrategy users need connectivity to the metadata so that they can access projects, create objects, and execute reports. MicroStrategy Intelligence Server connects to the metadata by reading the server definition registry when it starts.

MicroStrategy Metadata

What happens when the Intelligence Server starts?

When the Intelligence Server starts, it does the following:

  • Initializes internal processing units
  • Reads from the machine registry which server definition it is supposed to use and connects to the specified metadata database
  • Loads configuration and schema information for each loaded project
  • Loads existing report cache files from automatic backup files into memory for each loaded project (up to the specified maximum RAM setting)
  • Loads schedules
  • Loads MDX cube schemas

MicroStrategy Service Manager

Inclined to build a profession as MicroStrategy Developer? Then here is the blog post on, explore MicroStrategy Training

Intelligence Server job processing

The following is a high-level overview of the processing that takes place:

  • A user requests a client application such as MicroStrategy Web, which sends the request to Intelligence Server.
  • The intelligence server determines what type of request it is and performs a variety of functions to prepare for processing.
  • Depending on the request type, a task list is composed that determines what tasks must be accomplished to complete the job, that is, what components the job has to use within the server that handle things like asking the user to respond to a prompt, retrieving information from the metadata repository, executing SQL against a database, and so on. Each type of request has a different set of tasks in the task list.
  • The components within the Intelligence Server perform different tasks in the task list, such as querying the data warehouse, until a final result is achieved.
  • Those components are the stops the job makes in what is called a “pipeline,” a path that the job takes as Intelligence Server works on it.
  • The result is sent back to the client application, which presents the result to the user.

Processing report execution

MicroStrategy - Report Execution

Processing object browsing

MicroStrategy - Processing Object Browsing

MicroStrategy User Model

MicroStrategy users

  • Like most security architectures, the MicroStrategy security model is built around the concept of a user. To do anything useful with MicroStrategy, a user must log in to the system using a login ID and password. The user can then perform tasks such as creating objects or executing reports and documents and can generally take advantage of all the other features of the MicroStrategy system.
  • Users are defined in the MicroStrategy metadata and exist across projects. You do not have to define users for every project you create in a single metadata repository.
  • Each user has a unique profile folder in each project. This profile folder appears to the user as the “My Personal Objects” folder. By default, other users’ profile folders are hidden. They can be viewed by, in the Desktop Preferences dialog box, in the Desktop: Browsing category, selecting the Display Hidden Objects checkbox.
  • The administrator is a built-in default user created with a new MicroStrategy metadata repository. The Administrator user has all privileges and permissions for all projects and all objects.

MicroStrategy user groups

A user group (or “group” for short) is a collection of users. Groups provide a convenient way to manage a large number of users.

Instead of assigning privileges, such as the ability to create reports, to hundreds of users individually, you may assign privileges to a group. Groups may also be assigned permissions to objects, such as the ability to add reports to a particular folder.

Controlling access to objects: Permissions

Permissions define the degree of control users have over individual objects in the system. For example, in the case of a report, a user may have permission to view the report definition and execute the report, but not to modify the report definition or delete the report.

While privileges are assigned to users (either individually, through groups, or with security roles), permissions are assigned to objects.

Controlling access to functionality: Privileges

Privileges give users access to specific MicroStrategy functionality. For example, the Create Metric privilege allows the user to use the Metric Editor to create a new metric, and the Monitor Caches privilege allows the user to view cache information in the Cache Monitor.

Defining sets of privileges: Security roles

A security role is a collection of project-level privileges that are assigned to users and groups. For example, you might have two types of users with different functionality needs: the Executive Users who need to run, sort, and print reports, and the Business Analysts who need additional capabilities to drill and change subtotal definitions. In this case, you can create two security roles to suit these two different types of users.

Modes of Authentication

The available authentication modes are:

  • Standard: Intelligence Server is the authentication authority. This is the default authentication mode.
  • Anonymous: Users log in as “Guest” and do not need to provide a password. This authentication mode may be required to enable other authentication modes, such as database warehouses or LDAP.
  • Database warehouse: The data warehouse database is the authentication authority.
  • LDAP (lightweight directory access protocol): An LDAP server is the authentication authority Single sign-on: Single sign-on encompasses several different third-party authentication methods, including:
  • Windows authentication: Windows is the authentication authority o Integrated authentication: A domain controller using Kerberos authentication is the authentication authority
  • Tivoli or SiteMinder: A third-party single sign-on tool, such as Tivoli or SiteMinder, is the authentication authority.

Managing and verifying your licenses

MicroStrategy uses two main categories of licenses:

  • Named User licenses in which the number of users with access to specific functionality is restricted.

In a Named User licensing scheme, the privileges given to users and groups determine what licenses are assigned to users and groups. Intelligence Server monitors the number of users in your MicroStrategy system with each privilege and compares that to the number of available licenses.

  • CPU licenses, in which the number and speed of the CPUs used by MicroStrategy server products are restricted.

When you purchase licenses in the CPU format, the system monitors the number of CPUs being used by the Intelligence Server in your implementation and compares it to the number of licenses that you have. You cannot assign privileges related to certain licenses if the system detects that more CPUs are being used than are licensed. For example, this could happen if you have MicroStrategy Web installed on two dual-processor machines (four CPUs) and you have a license for only two CPUs.


A cache is a result set that is stored on a system to improve response time in future requests. With caching, users can retrieve results from Intelligence Server rather than re-executing queries against a database.

Intelligence Server supports the following types of caches:

  • Result caches: Report and document results that have already been calculated and processed, that are stored on the Intelligence Server machine so they can be retrieved more quickly than re-executing the request against the data warehouse.
  • Report caches can only be created or used for a project if the Enable report server caching checkbox is selected in the Project Configuration Editor under the Caching: Result Caches: Creation category.
  • The History List is a way of saving report results on a per-user basis. The History List is a folder where the Intelligence Server places report and document results for future reference. Each user has a unique History List.

With the History List, users can:

  • Keep shortcuts to previously run reports, like the Favorites list when browsing the Internet.
  • Perform asynchronous report execution. For example, multiple reports can be run at the same time within one browser, or pending reports can remain displayed even after logging out of a project.
  • Element caches: Most-recently used lookup table elements that are stored in memory on the Intelligence Server or MicroStrategy Desktop machines so they can be retrieved more quickly.
  • When a user runs a prompted report containing an attribute element prompt or a hierarchy prompt, an element request is created.
  • Object caches: Most-recently used metadata objects that are stored in memory on the Intelligence Server and MicroStrategy Desktop machines so they can be retrieved more quickly.

When you or any users browse an object definition (attribute, metric, and so on), you create what is called an object cache. An object cache is a recently used object definition stored in memory on MicroStrategy Desktop and MicroStrategy Intelligence Server.


Scheduling is a feature of MicroStrategy Intelligence Server that you can use to automate various tasks. Time-sensitive, time-consuming, repetitive, and bulk tasks are ideal candidates for scheduling. Running a report or document is the most commonly scheduled task since scheduling reports, in conjunction with other features such as caching and clustering, can improve the overall performance of the system.

Time-Triggered and Event-Triggered

With a time-triggered schedule, you define a specific date and time at which the scheduled task is to be run. For example, you can execute a particular task every Sunday night at midnight. Time-triggered schedules are useful to allow large, resource-intensive tasks to run at off-peak times, such as overnight or over a weekend.

An event-triggered schedule causes tasks to occur when a specific event occurs. For example, an event may trigger when the database is loaded, or when the books are closed at the end of a cycle.


A clustered set of machines provides a related set of functionality or services to a common set of users. MicroStrategy recommends clustering Intelligence Servers in environments where access to the data warehouse is mission-critical and system performance is of utmost importance.

A cluster is a group of two or more servers connected in such a way that they behave like a single server. Each machine in the cluster is called a node. Because each machine in the cluster runs the same services as other machines in the cluster, any machine can stand in for any other machine in the cluster.

  • Failover support
  • Load balancing
  • Project distribution and project failover

MicroStrategy - Clustering


How Microstrategy Desktop Works?


The MicroStrategy Desktop interface has three panes:

Folder List: Where all the project folders that hold your reports and report-related objects are accessible. The Folder List displays all the project sources, projects, application and schema object folders, and the administrative functions for your business intelligence system. When all panes are displayed, the Folder List is the center pane of the Desktop interface.

If the Folder List does not automatically appear when you log in to MicroStrategy Desktop, from the View menu select Folder List.

Object Viewer: Where the contents of each folder, such as reports or report objects, are displayed as you browse through folders in the Folder List. The right pane of the MicroStrategy Desktop interface is the Object Viewer.

Shortcut Bar: This pane contains icons that allow you instant access to your favorite or most frequently used folders. Simply click on a shortcut icon to jump immediately to the folder to which it is linked. You can create a shortcut to any folder that appears in your Folder List. You can add or remove shortcuts at any time.

Navigating through Desktop

Use the following menus and tools in MicroStrategy Desktop to access the different reporting features of MicroStrategy.

From the Desktop menus, you can do the following.

Microstrategy desktop

From the Desktop toolbar, you can do the following:

MicroStrategy Desktop Toolbar

Report Editor Interface

Microstrategy-Report Editor

Report Objects pane: (top left) This pane appears only if you have the MicroStrategy OLAP Services product. Where you can see a summary of all the objects you have included in your report.

There may be more objects in this pane than are displayed on the executed report because OLAP Services lets analysts quickly remove or add objects from this pane directly to the report template. When the report is executed, the MicroStrategy Engine generates SQL that includes all the objects in this Report Objects pane, not just the objects that are displayed in the report after it is executed.

Object Browser pane: (center left) Where you navigate through the project to locate objects to include on the report.

My Shortcuts pane: (bottom left) enables you to access any folder in the Object Browser quickly. Creating shortcuts can save you time if you repeatedly browse to the same folders.

View Filter pane: (top right) Where you apply a special kind of filter to any object that is in the Report Objects pane. View filters do not modify the SQL for the report like normal report filters do. Instead, view filters are applied to the overall result set after the SQL is executed and results are returned from the data source. This can help improve report execution performance.

Report Filter pane: (center right) Where you add filtering conditions to a report. Filtering conditions can be made up of attributes, metrics, advanced filter qualifications, and shortcuts to an existing report filter. The Report Filter pane allows you to create a filter without having to open a separate object editor (the Filter Editor). Simple filters can be conveniently created by dragging and dropping objects from the Object Browser into this pane to create a filter.

Report View pane: (bottom right) Where you define your report layouts by dragging and dropping objects from the Object Browser onto this report view pane. You can create a report to serve as a template for other reports;

Page-by pane: (top of Report View pane) Where you place subsets of your report results to be displayed as separate pages of the executed report.

Project objects

Microstrategy-project objects


Attributes are the business concepts reflected in your stored business data in your data source. Attributes provide a context in which to report on and analyze business facts or calculations. Attributes are created by the project designer when an organization’s project is first created.


Metrics are MicroStrategy objects that represent business measures and key performance indicators. From a practical perspective, metrics are the calculations performed on data stored in your database, the results of which are displayed on a report.

Specifically, metrics define the analytical calculations to be performed against data that is stored in the data source. A metric is made up of data source facts and the mathematical operations to be performed on those facts so that meaningful business analysis can be performed on the results.

Metric creation is usually the responsibility of advanced analysts.

Metric Editor:

Microstrategy-Metric editor

MicroStrategy Advanced Interview Questions

You use the Metric Editor to create and save metrics, and to edit existing metrics. The Metric Editor is accessible from MicroStrategy Desktop and is shown in the image above.

Metrics Terminology:

Metric formula: A metric’s formula is made up of a mathematical function and the business facts stored in your data source. A metric’s formula can also consist of other metrics.

Metric expression: A metric’s expression is made up of all the metric components displayed when a metric is opened in the Metric Editor: formula (described above), level, condition (if any), and transformation (if any).

Metric definition: A metric’s definition includes the metric’s expression, plus any software settings applied to the metric.


A metric definition must contain a formula, which determines

the data be used from your data source and the calculations to be performed on that data. An example of the formula of a metric is


where Cost is a fact stored in the data source. The metric formula above calculates the sum of all costs recorded in the data source, to determine a measure of a company’s expenditures.

The following examples of metrics show these different formula options:

Metric made up of facts: (Sum(Profit) + Sum(Cost))

o  Metric made up of attributes: Count(Employee)

  • Metric made up of other metrics: [This month’s profit] - [Last month’s profit] / [Last month’s profit]

Most metrics must also have a mathematical function as part of the metric formula. The function is part of the formula.

The default function for every formula made up of facts is Sum. The default function for every formula made up of attributes is Count.


A metric’s level (sometimes called dimensionality) determines the level at which the metric is calculated. Therefore, every metric must have a level as part of its definition. By default, a metric is calculated at the report level, that is, at the level of the attribute on the report in which the metric is placed.

When more than one attribute is on a report, as is generally the case, a metric is calculated by default at the level of the lowest-level attribute that is on the report.

In MicroStrategy, the level at which a metric is calculated is displayed in the Metric Editor in two places:

  • In the lower Definition pane at the end of the metric’s formula, the level appears within curly braces. The default metric level appears as {~}. If the default level is changed, the name of the attribute that represents the new level appears between the curly braces.

In the upper pane of the Metric Editor, as shown in the image below, the level appears as Level (Dimensionality) = Report Level.

MicroStrategy-Metric Editor

The {~+} means that the metric is calculated at the level of the lowest attribute on the report, which is the default calculation level for a metric.

The level will be disabled for Compound Metrics.

Level Metrics:

The level within the metric, allowing you to specify the attribute(s) to use in the metric calculation, regardless of what is contained on any report the metric is placed upon. A metric level is sometimes also referred to as dimensionality.

Elements of a metric level:

Target: The target is the attribute level at which the metric calculation groups.

Grouping: Grouping determines how the metric aggregates. The result of this setting is reflected in the GROUP BY clause of the SQL command. The grouping options for levels include:

  • Standard groups by the attribute level of the target. That is, the metric calculates at the level of the target, if possible.

None excludes the attribute in the target from the GROUP BY clause. It also excludes any of the targets

Filtering: Filtering governs how the report filter interacts with the metric calculation.

MicroStrategy-Metric Revenue

A target, grouping, and filtering combination composes one level unit.

Nested Metrics:

Nested metrics, or nested aggregation metrics, are a type of simple metric, where one aggregation function is enclosed inside another. For example:


The inner function Sum calculates the total for the Revenue fact, and the outer function Avg calculates the average of that result.


A filter is a condition placed on data from your data source. A filter is placed on a report as a whole screen overall report data. You can also apply a filter directly to an individual metric on a report. A filter applied this way screens only the data related to that individual metric. The filter becomes part of that metric’s definition.

In this context, the filter is called a conditional, and the metric to which the filter is applied is called a conditional metric. A condition is not required in a metric’s definition; this is an optional metric component. Applying conditionality to a metric force the calculation of a metric to be qualified by the metric’s filter irrespective of what is specified in the report filter, if one exists.

To apply conditionality to a metric, you create a filter and add the filter to the metric’s definition so that only data that meet the filter conditions are included in that metric’s calculation.

The metric condition can be either a filter or a prompt that returns a list of filters. Only one filter or prompt can be associated with each metric, but that filter can contain multiple qualifications.

MicroStrategy-Metric Order

The condition will be disabled for Compound Metrics.


A transformation applies offset values, such as “four months ago,” to a metric on a report. A transformation is not required in a metric’s definition; this is an optional metric component.

Transformations are generally added to metrics that are designed to do time-series analysis, for example, to compare values at different times, such as this year versus last year, or month-to-date. Transformations are useful for discovering and analyzing time-based trends in your data.

When a transformation is created and applied to a metric, you have created a transformation metric. A transformation metric is a metric that assumes the properties of the transformation applied to it.

Note: Transformations are created as a separate object and then added to a metric. Transformations are schema objects, so you must have the appropriate privileges to create or modify them.

MicroStrategy-Metric Sales

Transformations will be disabled for Compound Metrics.

Compound Metric:

A metric that contains other metrics is called a compound metric. You can easily identify metrics within a metric, as opposed to facts or attributes within a metric because metrics within a metric are displayed in bold font in the Metric Editor. The image below shows the metrics Sales Rep Quantity and Sales Rep Quantity for Last Year, which make up the definition of the new metric is created.

Compound Metric

A metric is a compound metric if it uses a non-group function, which includes OLAP functions and scalar functions.

A metric is a compound metric if it consists of two or more existing metrics joined by an arithmetic operator (+, -, *, and /).

One advantage compound metrics have over simple metrics, it that compound metrics can use smart totals. Smart totals define the evaluation order for the final calculation.

Useful Metric Functions:

Rank - In rank functions, you specify the metric to be ranked and whether to rank in ascending or descending order. You can also specify whether to break by an attribute.

Running or Moving Sums and averages - The running sum function uses a metric as input and calculates a cumulative total of values based on a sort order specified in the metric definition.

N-tile - The N-tile function, which is also referred to as segmentation, sets up numbers of groups, or tiles, for a metric.

An example of an N-tile function in use is displaying what items are in the top 25% of sales, the next 25%, and so on. Use the N-tile function with the Revenue metric. Because the results are in quartiles (four groups of 25 each), the number of tiles is four.

First & Last - The First and Last functions provide the ability to use sort-by inside aggregation functions, that is, functions where the value depends on the sort order. First returns the First value in a sorted set of values, while Last returns the last value.

Count - The count function is usually used with an attribute, although a fact can also be counted. Metrics that use the Count function in their definition are commonly referred to as count metrics.

Joins for Rank Metrics

If you create a rank metric, which is a metric that ranks attribute elements by numbering them, for example, 1 through 10, you must set the metric’s join type to the outer. If the default inner join is used on a rank metric, some of the ranks (and therefore, the ranked attribute elements) may not appear on the report because an inner join does not include elements with null values in the result set. But an element with a null value may have a rank. With an outer join, all rows are displayed on the report even if there is no result displayed for some of the elements for some of the metrics on the report.

Insert Function Wizard

An easy way to define a function for a metric’s formula is to use the Insert Function Wizard within the Metric Editor. With the wizard, you can use any of the functions available in MicroStrategy to include in the formula for a metric. The wizard helps you locate a function and define its arguments, BreakBy parameter, SortBy parameter, and any other required parameter. These components of a function are described in the procedure below.

You open the Insert Function Wizard using the f(x) button in the Metric Editor, as shown below:


Applying Totals to a Metric

In Desktop, double-click the metric for which you want to enable a total or subtotals. The metric opens for editing in the Metric Editor.

Click the Subtotals / Aggregation tab.

To enable a total, from the Total subtotal function drop-down list, select the function to use when the metric displays a total on a report.

To enable one or more subtotals, from the Available project subtotals list, select the functions to be available for the subtotal calculations. Click > to move your selections to the Available subtotals for the metric list.

Click Save and Close to save the metric with its newly enabled total and/or subtotals.

Smart Totals: Smart totals are also referred to as smart metrics. Smart totals are used on compound metrics. Smart totals allow you to change the default evaluation order of a compound metric.Smart totals calculate subtotals on individual elements of the compound metric. For example, a smart metric uses the formula Sum (Metric1) / Sum (Metric2) rather than Sum (Metric1/Metric2).The smart metric property is available for compound metrics and for some simple metrics which combine two or more calculation formulas with arithmetic operators. Toenable or disable smart metrics, use the Allow Smart Metric check box at the bottom of the Subtotals/Aggregation tab in the Metric Editor.


A report filter is the part of a MicroStrategy report that screens data in your data source to determine whether the data should be included in or excluded from the calculations of the report results.

Filters ensure that the report results contain only the data that answers the specific business query the report was designed to answer. It is important to design the correct filter to retrieve the desired data.


Filter Types

When you open the Filter Editor and double-click in the Filter definition pane at the top right, a list of filter types appears in the Filtering Options pane. You select a filter type from this list, and then specify the information that is required for the type of filter you are creating.

Stand Alone Filters:

A stand-alone filter is a filter created as an independent MicroStrategy object. The stand-alone filter can then be used on many different reports, as well as on metrics and other objects.

Report Filter:

Filters can also be created as part of a given report, at the same time the report itself is being created. These kinds of filters are generically called report filters. Report filters are saved with the report’s definition. Therefore, a report filter cannot be used on any other report.

Joining filter qualifications with operators

When a filter has multiple qualifications, they are always joined by operators. When qualifications are joined, operators govern the interaction between different filtering conditions. Whenever you have more than one qualification in a report filter, you can define the operator as any of the following:





The AND operator is the operator assigned by default when more than one qualification is added to a filter. You can change the default AND operator by simply right-clicking on the word AND in the filter’s definition pane, pointing to Toggle Operator, and selecting a different operator.

Microstrategy-Filter Editor

Additional Filtering Functionality

Dynamic Dates: Dynamic Filters can be applied on Date attribute only. When you qualify on a date attribute form with the date data type, you can select dynamic dates, which are fixed offsets of the current date. They can be either a fixed set of dates or different date ranges that change through time.Date and Time Editor

Break By Property:

Applicable to Rank functions in the metric type of filters


Prompted Filters:

You can create filters that prompt you for answers when you run reports. These prompted filters allow a report to have a dynamic report definition, which can change with each query when the information in the prompt dialog box is altered.

Prompted Filters

Shortcut Vs Embedded Filters:

You can add a filter to a report in either of the following ways:

Apply a previously created, stand-alone filter to a report during report creation.

When you add a stand-alone filter to a report, you can choose to create a shortcut to the filter or to embed the filter in the report. The differences are described below:

  • When you create a shortcut to a stand-alone filter, changes that you make to the filter are propagated to this report and to all other reports that use the filter as a shortcut. Changes that you make to this filter within this report are propagated to the filter, and to all other reports that use the filter as a shortcut. This is sometimes referred to as a linked filter.

Use this option to share report caches.

  • When you embed a copy of the filter in the report, changes that you make to the stand-alone filter are not propagated to this report. Changes that you make to the embedded filter within this report are not propagated to the filter. This is also called a local filter.

Create a filter at the same time that you create a new report. This filter is embedded within the report and cannot be used in another report.

Within a report in the design view, right-click on any filter to the below options.

Microstrategy-Design View

Replace Report Filter with a shortcut to this filter to connect the stand-alone filter to this report. Changes to the stand-alone filter are propagated to the report, and vice versa.

Replace Report Filter with a copy of this filter to embed the filter in the report. Changes to the stand-alone filter are NOT propagated to the report, and vice versa.


A prompt is a question the system presents to a user during report execution. How the user answers the question determines what data is displayed on the report when it is returned from your data source.

Prompts allow each user who executes the report to request individualized sets of data from your data source when he answers the prompts and runs the report. Effectively, each user creates his own filter for the report.

Prompts can allow the report designer to create a smaller number of reports overall, using more inclusive objects, rather than having to create numerous, more specific reports that are individualized to each analyst.

Prompts allow the report designer to ensure that the objects on a report are the latest available objects in the project. This is possible using a search object in a prompt. When a user launches a prompt by running a report, the search object goes through the project and retrieves the latest objects that fit the search criteria the report designer defined. Thus, no matter when the prompt was created, each time a user executes the report, the user chooses prompt answers from a list of the most up-to-date objects available in the project, including objects that may not have existed when the prompt was created.

Prompts allow users to keep the objects on their saved reports up-to-date because users can save a prompt report so that the objects within the prompt remain connected to the original objects within the project that they were originally based on when the prompt was created. If objects are modified or deleted in the project, the report can reflect those changes the next time the prompted report is run.

With prompts, you can let the user decide how to restrict the data to be returned from the data source.

A prompt is similar to a filter (see Filtering data on a report: Filters) because a prompt determines the specific data to be displayed on a report. The difference, from a report designer’s perspective, is that you create a filter for a report to provide a single, specific definition for the report. A filtered report then displays the same set of data to every user who executes that report. In contrast, a prompt dynamically modifies the contents of a report

Prompt Generation Wizard

Prompts in Scheduled Reports

A scheduled report can contain prompts; how and if the report is executed depends on the prompt definition. The following table explains the various scenarios. Notice that to ensure that prompts are used on a scheduled report, the prompt must be required and the default prompt answers set.

Microstrategy-Scheduled Reports

 Prompts in MSTR Web

You can determine how a prompt appears when it is executed in MicroStrategy Web, by selecting a display style, and then updating the style properties associated with it.

Prompt Generation Wizard

Prompt in MSTR

Prompt in MSTR

Prompt in MSTR

 Prompts and Security Filters

Security filters in MicroStrategy restrict a user’s ability to access or view certain objects within a given MicroStrategy project. As a result, a security filter can limit the data a user sees on a report, even if the report is designed to display a broader set of data. For example, a report shows revenue data for all geographical regions of the U.S., but a set of users may have a security filter assigned to their MicroStrategy user accounts that limits the display of data to only the Northeast region.

For prompts, this means that users who have a security filter assigned to them may only see certain prompt answers to choose from when they execute a prompted report, even when the prompt creator has explicitly defined a broader set of prompt answers to be available to users of that report.


A template is a structure that underlies any report. A template specifies the set of information that the report should retrieve from your data source, and it also determines the structure in which the information is displayed in the report’s results. A template’s structure is the location of objects on the template, such as showing that metrics have been placed in the report’s columns, and attributes have been placed in the rows.

Custom Groups & Consolidations

A custom group is a set of special filters that can be placed on a template. It is made up of an ordered collection of elements called custom group elements.

Consolidations are used to specify the data you want to view in your report. They allow you to group attribute elements in new ways without changing the metadata and warehouse definitions.

Both of these features allow you to qualify a report on a row-by-row basis.

 Custom Groups

A custom group is an object that can be placed on a template and is made up of a collection of elements called custom group elements. Each element contains its own set of filtering or banding qualifications. Custom groups provide a method to group attribute elements from the same or different attributes to meet your reporting requirements.

Each custom group element can be labeled with a meaningful header and can include a logical expression containing any of the following:

Attribute qualification

Set qualification

Report object

Filter object

Custom group banding qualifications

The benefit of a custom group is its ability to group attribute elements in a way that is not defined in the data warehouse.


Banding qualifiers enable you to create banding custom group elements. Banding is a method of slicing a list, defined by the output level, of attribute elements using the values of metric elements. Banding is a method of slicing a list, defined by the output level, of attribute elements using the values of a metric.

You can apply different types of banding:

Band size: to slice the range of metric values defined by “start at” and “stop at” values into several bands, each defined by the parameter “step size.”

For example, in the following diagram the “start at” value is 10, “stop at” is 50, and “step size” is 10. These settings slice the group into four bands.

Microstrategy-Band count

Band count: to define the number of equal bands into which the range of metric values is sliced. The range is defined by “start at” and “stop at” values, as band size is. However, band size defines the size of each band, while the band count defines the number of bands.

For example, to use band count to achieve the same results as the preceding diagram set the band to count to four, “start at” to 10, and “stop at” to 50. If you set the band count to five instead, each of the five bands has a size of eight.

Banding points: to specify the value where a band is placed. This enables you to produce bands of different sizes.

For example, you want to create a report with two bands, one band showing the top 10 stores and the second band showing stores 11-100. For this, you must use three points—1, 10, and 100—as shown in the following figure.

Microstrategy-Banding points

To show a band of the bottom 10 stores and then the remaining, use the same points in reverse order, that is, 100, 10, and 1.

Band for each distinct metric value: to create a separate band for each value calculated by the metric. This type of banding qualification directly uses the results of a metric as bands. It is very useful with metrics that already contain the logic needed to calculate sequential band numbers. Such metrics use mathematical formulas, NTile functions, Band functions, or Case functions.

For example, a metric uses the NTile function to group revenue values into three groups. The custom group element is therefore sliced into three bands as well.

Microstrategy-distinct metric value

Custom group effects on SQL generation:

You can also regard custom groups as many different reports just “stacked up” together. The SQL for a report with a custom group is likely to be very complex. Each of the individual “mini-reports” that make up the entire custom group report will have at least one, perhaps more SQL passes of its own. The Analytical Engine stacks up all these “mini-reports” to create the final results. In addition, numerous temporary tables may be created and dropped to hold intermediate data.

Therefore, running a report with a custom group is equivalent to running many different reports and putting them together. As a result, custom groups are SQL-intensive in the sense that they are likely to generate many passes of SQL to the database.

Prompted Custom Groups

A prompted qualification in a custom group allows you to dynamically modify the contents of a report at run time.

By applying certain conditions on one or more of the attributes, attribute elements, metrics, or other objects that a report can include, it is possible to:

Apply conditions or modify components in a report at run time, thus eliminating the need for preliminary definitions

Execute a report multiple times, selecting a different answer to a given prompt each time, and have, as a result, a set of immediate answers to compare


Consolidations enable you to group and to pick specific attribute elements. Further, consolidations allow you to place this grouping of attribute elements on a template just like an attribute. The elements of the consolidation appear as rows on your report, and they can have arithmetic calculations.

For example, suppose you want to see each season of the year as a separate row on a report, but Season does not exist as an attribute in your project. Consolidation allows you to group the elements of the Month of Year attribute into various seasons and place them on the template. This consolidation will contain four consolidation elements, one for each season.

In general, consolidations provide two powerful functions that enhance your reporting needs. These functions are:

Create a “virtual” attribute

Perform row-level math


The calculations associated with a consolidation are done by the Analytical Engine component of the Intelligence Server. The SQL Engine writes the SQL query that gets the required data from the warehouse and then passes it to the Analytical Engine to do any mathematical operation that is needed to create the report.

Differences between Custom Groups and Consolidations:

custom groups and consolidation

Drill Maps

Drill maps allow you to create fully customized drill paths that are available to your users while drilling on a report. By default, the paths available are based on the system hierarchy of the project. You can create custom drill maps that can override these defaults.


After executing a report in a MicroStrategy reporting environment, you may need to execute another report based on the original report to get more detailed or supplemental information. For example, after looking at annual sales of a certain city, you may want to look at the monthly sales for the same city. Alternatively, after noticing that a certain item had a very high-profit margin, you may want to see if that is also true for the entire category of that item. Such actions where you create a related report based on an existing report are referred to as drilling.

Drill Maps & Drill Paths:

Drill maps determine the options available to an end-user when drilling on a report. By right-clicking on a report and choosing the drill option, you are using drill maps.

When the drill hierarchies are created, a default drill map is created. If no drill hierarchies are created, the system hierarchy is used to create the default drill map. The drill map determines what options are available to you when you drill on a report object. These different options are referred to as drill paths, which include the destination of the drill. The destination can be an attribute, a consolidation, a hierarchy, or a template.

Drill map editor

Using the above drill map in a report with grid view.

Drill map-Report

Filters & Drilling:

How a report’s filter is changed while drilling depends on what part of the original report is selected when the drill is performed. By default, if an attribute element on the original report is selected while drilling, then that attribute element is added to the new filter created for the drill. The filter from the original report on which you drill is carried over as well. For example, a report lists revenue by state and contains a filter for the Electronics category. You select Virginia when you drill to Store. The resulting report contains Electronics revenue for Virginia stores only. You can change this default behavior for a drill path in the Drill Map Editor and for a report in Report Data Options.

Note: The right-click menu provides two ways to drill. If you right-click a header, a filter is not added to the drill. If you right-click an attribute element, the filter is used.

Drill path types

A drill path can be one of the following types:

Up—The destination can be any attribute or consolidation and does not have to be related to the original object. The destination is shown as part of the Drill Up menu when you right-click and select Drill in the report.

Down—This is similar to Up, except that the destination is shown as part of the Drill Down menu when you right-click and select Drill.

Across—This is also similar to Up, except that:

  • The destination is shown as part of the Other Directions menu when you right-click and select Drill.

o  A hierarchy can be used as a drill path.

Template—This allows you to replace the template of the original report template with a completely different destination template. Select the template to use as the destination template.

Drill Map—Use this as a shortcut to the drill paths of another drill map.

  • The destinations of those drill paths are displayed along with the destinations you have created. For example, you select a drill map that drills up to Brand. You already have a drill path up to Subcategory. When you select Drill and Up, both Brand and Subcategory are displayed.

Select an existing drill map to use as the destination.


Creating a new report

Sample Report from Tutorial Project:

Revenue by region

In MicroStrategy Desktop, in the Folder List, login to a project source that contains the MicroStrategy Tutorial project.

Expand the MicroStrategy Tutorial project, expand the My Personal Objects folder, and select My Reports.

From the File menu, point to New, and choose Report.

If the New Grid dialog box opens, select Empty Report and click OK. The Report Editor opens.

In the Object Browser on the left, browse to the Schema Objects folder.

If you cannot see the Object Browser, from the View menu select Object Browser.

Double-click the Attributes folder and then the Geography folder to display the attributes within that folder.

Double-click the Region attribute to add it to the Template pane.

In the Object Browser, browse to the Public Objects folder and double-click the Metrics folder, then double-click the Sales Metrics

Double-click the Revenue metric to add it to the Template pane.

From the File menu, select Save As. The Save Report As dialog box opens.

Navigate to the location in which you want to save the report, usually in the My Reports folder. Name the report My Revenue by Region and click Save.

From the View menu, choose Grid View. This runs the report and displays the report results showing sample data retrieved from the sample data warehouse. When a report like this is built using your organization’s objects, this view provides actual revenue numbers an analyst can use for detailed reporting purposes.

From the View menu, choose Graph View to see the report as it is displayed in the matching sample report in the Tutorial project. This view provides an opportunity to perform a high-level assessment of how various regions are doing in terms of revenue.

Basic Report

Switch to Design View. The following screenshot displays the Basic Report in the Design View of Desktop. All of the panes mentioned in the next paragraphs have been opened and annotated for your use.

Microstrategy-Basic Report

Report as a Filter:

A report as a filter allows you to create a report and use it as a filter to generate another report. It is a different way to achieve the same results as a metric qualification, but it is easier to understand and create. Because the logic used to generate the final report is clearer, MicroStrategy recommends using it rather than the metric qualification.

Note: In Desktop, you select Add a Shortcut to a Report to access the report as filter functionality.

Data Vs Display:

Data vs Display

Sorting Data in Report:

If a report is not sorted, the result set is displayed in the default order. If only one attribute is included in the report, the report is sorted by the attribute IDs, in ascending order. If the report includes multiple attributes on the rows, the report sorts by the attribute farthest to the left. If the attributes are on the columns, the report sorts by the top attribute. If the report displays multiple attribute forms for a single attribute, the report still uses this default order.

Advanced sorting allows you to create your own, more complex sorts for rows, columns, and pages. You can sort by both columns and rows at the same time. You can select the object to sort by, the sorting order (ascending or descending), the sorting criteria, and the position of the totals. The options for the sorting criteria depend on the sort object.

Select Data -> Advanced Sorting from Report Editor Menu.

Sorting Data Report


Bulk Export:

The bulk export feature allows you to select a report and export it to a delimited text file. Using this feature, you can retrieve result sets from large datasets without having to load the datasets in memory. As the result sets are brought back incrementally, no limit is set on the size of the dataset to be retrieved.

While you can schedule a bulk export report for execution with Distribution Services, you cannot directly execute a bulk export report in Desktop. When you right-click a bulk export report, the Run Report option is not available. You can, however, view the report in Design View and SQL View on Desktop. (All the other views are disabled.)

Configure a report for Bulk Export:

In Desktop, open a report in the Report Editor.

From the Data menu, select Configure Bulk Export. The Bulk Export Setup dialog box is displayed.

Bulk export setup

Specify the options in the three tabs of the dialog box. For a detailed explanation of each option, see the online help.

Click OK. The Bulk Export Setup dialog box closes and the report is now specified as a bulk export report.

Save the report. After you save a report as a bulk export report, its icon on Desktop changes as shown in the following figure.

Desktop icon


Best Practices

You can select Grid Graph View to see the report grid and corresponding graph side-by-side. This allows you to add and replace objects on the grid and immediately see how its corresponding graph changes.

Your report grid must contain at least one attribute and one metric to be displayed as a graph

As you design your graph, try to position objects such as attributes and metrics on the report grid (or the categories and series) in a variety of ways until the graph presents the data in the desired manner.

Each graph style requires a minimum or a specific number of attributes and metrics on the report grid. For example, a pie graph requires only one attribute and one metric on the report grid, although more attributes and metrics can be included.

Consider the order of the attributes, metrics, and other objects on your report grid.

Dual-axis graphs can improve the display of graph reports that include metrics that return different types of data.

If you are creating a graph report to view in MicroStrategy Web, it is recommended that you choose the JPEG or PNG image format before you save the graph report.

If the graph is included in a Report Services document, you can provide some interactive functionality to the graph by doing the following. In the document, add a Slider selector under the graph. Then, configure the slider to control the graph. This allows you and other users to navigate to different sections of the graph’s results.

Apply a glossy, rounded effect to your graph’s series. The effect is displayed in Graph view and Grid Graph View in Desktop and Editable, Interactive, and View Mode in MicroStrategy Web. To do so, from the Graph menu, select Preferences. Expand Options, and select the General category. Select the

Apply rounded effects to all series checkbox.

Use font anti-aliasing to display more clearly and distinctly the text that appears on or near your graph report. To do so, from the Graph menu, select Preferences. Expand Options, and select the General category. Select the Use font anti-aliasing checkbox. This formatting effect is especially noticeable when the graph report is viewed in Flash Mode in MicroStrategy Web.

 Minimum object requirements for each graph style

Graph style

Graph style 1

 Applying Graph Style

  1. View the report in the Graph view.
  2. From the Graph menu, select Graph Type. The Graph Type dialog box opens.
  3. From the left side of the Graph Type dialog box, select a graph type. The right side of the dialog box shows you the different graph styles you can create within each graph type. For example, if you select the Pie graph type, you can choose from several different Pie graph styles, including Pie: Ring or Pie: Multiple Proportional Ring.
  4. Select a graph style from the right side of the Graph Type dialog box.
  5. Click OK to apply the graph style to your graph:

If your report contains sufficient data to be displayed in the selected graph style, your graph is generated in the new style.

If your report does not contain sufficient data to be displayed in the selected graph style, a dialog box notifies you of this and your graph is not updated.

  1.  Dynamic Titles in Graphs

    The following table describes the variables you can use to insert dynamic text into your graph’s titles and labels.

Dynamic Tiles

Free Form SQL and Query Builder

Freeform SQL and Query Builder provide two alternative methods to access your data and begin the analysis with MicroStrategy. Freeform SQL allows you to write your own SQL statements to run directly against a data warehouse or operational data store, giving you full control over accessing your data. Query Builder provides you with a graphical user interface that helps guide you on building SQL queries that can adapt to different data models.

These two features allow you to run queries against ODBC data sources that are not easily modeled to an attribute and fact schema. This includes databases that are a collection of flat tables rather than being defined into fact and lookup tables. You can also save a great amount of time setting up a project to use with Freeform SQL or Query Builder because you are not required to create an attribute and fact schema.

 Free Form SQL

The following image is an illustration of the Freeform SQL Editor, where you define the SQL statement for the report. Notice the different panes for different purposes.

Free from SQL

Creating a Free Form SQL Report:

1. In Desktop, from the File menu, select New, and then choose Report. The New Grid dialog box opens.

2. On the ODBC Sources tab, in the Source area, select a database instance for the data source to access using Freeform SQL.

3. Select Use Freeform SQL Editor and click OK. The Freeform SQL Editor opens.

4. In the SQL Statement pane (the top pane on the right), type in your SQL query. Be aware of the following when creating a SQL statement:

a. Column names in the SQL statement have to match the column names in the database, column headers in the Excel file, or the field names in the text file.

b. The column names you use in your SQL query are NOT case sensitive.

c. When connecting to Excel and text files, you do not use the file name as the table name for the “From” clause. You must use the table names you defined when creating and connecting to the files. Remember that the Excel file or text file is the data source that contains the tables, which are named individually.

  1. In the Mapping pane (the bottom pane on the right), map the columns in your SQL statement to attributes and metrics to be used in the report. Be aware of the following when mapping columns in your SQL statement to attributes and metrics:

When mapping the columns, you must follow the same sequence of the columns as they appear in the SQL statement. Doing otherwise causes the report to fail.

Make sure that the number of mappings is the same as the number of columns in the SQL statement. For example, if your SQL statement lists 10 columns from which to retrieve data, you should map these columns to exactly 10 attributes or metrics.

You must map a column to the ID form for each attribute you use in your Freeform SQL report.

  1. Insert prompts into the SQL statement if needed.
  1. Insert security filter qualifications, if needed.
  1. Click OK to close the Freeform SQL Editor. The Report Editor opens.
  1. Format and define the Freeform SQL report in the same way as you would a standard report, using features such as sorting, view filters, thresholds, exporting, and so on.
  1. From the File menu, click Save As. The Save Report As dialog box opens.

You must save the report before you can run it, otherwise, a message is displayed.

Enter a name for the report and click Save.

Query Builder

Query Builder provides an easy way to quickly access your ODBC data sources without having to write any SQL. You can create queries to be run against imported database tables, which allows you to begin reporting and analyzing with MicroStrategy without performing the project creation step of modeling attribute and fact schemas. This step is necessary for the ROLAP Engine to define attribute and fact schemas. You can also import tables into a project’s Warehouse Catalog using the Query Builder feature. The following image shows the Query Builder Editor, where you choose the tables, columns, and joins to define the database queries for a report.

         Query Builder

Query Builder allows you more control over the SQL generated against your database systems, without the need for extensive knowledge on how to create SQL statements. Basic knowledge of how SQL statements use tables, columns, and joins to build queries is essential. You should also be familiar with the names of the tables and columns in your data warehouse or ODBC data sources, as well as what information they store.

VLDB Properties

VLDB properties allow you to customize the SQL that MicroStrategy generates. These settings affect how MicroStrategy Intelligence Server manages joins, metric calculations, and query optimizations, among other options.

Levels of VLDB Properties:

VLDB properties are available at multiple levels so that SQL generated for one report can be manipulated separately from the SQL generated for another, similar report. This flexibility is an important benefit of VLDB properties.

The hierarchy or order of precedence, for VLDB properties, is outlined in the following figure.

VLDB Proerty

The arrows depict the overriding authority of the levels, with the report level having the greatest authority. For example, if a VLDB property is set one way for a report, and the same property is set differently for a metric included on the report, the report property takes precedence.

Report Service Documents

A MicroStrategy Report Services document contains objects representing data coming from one or more MicroStrategy reports, as well as images and shapes. Documents can appear in almost as many ways as you can imagine and are generally formatted to suit your business needs, in a single display of presentation quality.

Designing and creating documents:

First, you open a blank document and select a report to use as the document’s dataset. The dataset report of a document contains the MicroStrategy objects that can be displayed in the document. These objects include attributes, custom groups, consolidations, and metrics. You can select more than one dataset report to include in the document. You must define one dataset as the grouping and sorting dataset; you can group and sort only by the objects on this dataset.

After you create a document, you add controls to the document. Controls are the objects that display the data, images, and shapes in a document; they are the objects shown in the document’s Layout area as you design the document.

A Control can be a link, HTML container, Line or Rectangle, Image, Grid/Graph,

Creating a Document:

You can create a document in one of several ways, depending on your needs:

On your own from start to finish using the Document Editor, which allows you to select the information to be included and the formatting of the document. You can add one or more dataset reports to the document.

With the help of the Document Wizard, which provides steps to create the document.

Using another document as a template, which allows you to pattern the new document after an existing one. The same dataset, controls, formatting, and layout as the template are used in the new document. However, you can add to or modify the new document after it is created.

From a report. The report is added as a Grid/Graph (an object that acts as a standard MicroStrategy report) in the Detail Header of the new document.

Using Document Editor:

-From the File menu, select New, then Document. The New Document dialog box opens.

-Select the Blank Document icon and click OK. Select a report dialog box opens.

-Navigate through the report folders and select the MicroStrategy report or reports to use as datasets. To select multiple reports, hold the SHIFT or CTRL key while selecting the reports. If you select multiple reports, all the selected reports are added to the document. The first dataset in alphabetical order is defined as the grouping and sorting dataset. You can sort and group the document using fields from the grouping and sorting dataset only.

-Click OK. The Document Editor opens.

-Select the data for the document. Add data fields, auto text codes, text labels, images, and other controls.

-Arrange the controls as you like.

-Format the various controls and sections, as well as the -document as a whole.

-Group and sort the data.

-Add totals, if desired.

-Save the document by selecting Save from the File

-You can now execute and view the document. To do this, from the View menu, select PDF.

Displaying data in the document

Most of the information on a document is from an underlying dataset. A dataset is a MicroStrategy report that defines the data that the Intelligence Server should retrieve from your data warehouse or from a cache that is available to the document.

Other information that does not originate from the dataset is stored in the document’s definition.

How metrics are calculated in a document

A metric is calculated differently depending on its location in the document. For example, the Revenue metric next to each employee is calculated at the level of the employee. The same Revenue metric next to the text “Total Regional Revenue” calculates all the revenue for the specific region.

Calculation of Metrics


A hyperlink connects text or an image in a document to a web page (the target of the hyperlink). When the document is open in PDF View, as shown below, the cursor changes to a hand when you hover the cursor over text that contains a hyperlink.


Click the hyperlinked text to open the target (in this example, the website in another window. Hyperlinks are functional in PDF View in Desktop, as well as in Express Mode, Interactive Mode, and Editable Mode in MicroStrategy Web.

Grouping documents to create pages of data

If a document is organized so that the data is grouped by page, drop-down lists are displayed at the top of the screen, as shown in the portion of the sample document displayed below. You can select which elements, or subsets of data, to display. Each subset of data is called a page.

Microstrategy-Grouping documents


A multi-layout document contains multiple documents, each in its own layout, creating a “book” of documents. Each layout functions as a separate document, with its own grouping, page setup, and so on, but the layouts are generated into a single PDF document. If a document contains multiple layouts, tabs are displayed at the top of the screen. Click a tab to select which layout to display.


Displaying reports on documents: Grid/Graphs

A Grid/Graph is the object used to display MicroStrategy reports on documents. Like a report, a Grid/Graph can be displayed as a grid, a graph, or both, as shown in the document sample. The data on the Grid/Graph comes from a dataset report, just as the attribute or metric data comes from a dataset report.

If the data in the dataset report changes (for example, new employees are added to the data warehouse), the data in the Grid/Graph on the document changes. However, changes, such as formatting the employee names, made to the MicroStrategy report are not propagated to the Grid/Graph in the document. If an object, such as the Employee attribute is removed from the MicroStrategy report, it cannot be displayed in the Grid/Graph in the document.

A special type of Grid/Graph, a shortcut, is linked to the MicroStrategy report. Grid/Graph shortcuts do reflect changes made to the MicroStrategy report.


A dashboard is a special type of document, not a part of a document.

A dashboard is commonly only one page long, is intended to be viewed online, and usually provides interactive features that let analysts change how they view the dashboard’s data. By being only one page long, a dashboard makes it easy to view the whole document at one time and see all the information. A dashboard allows interactivity from users, so each user can change how they see the data, within the limits of what the dashboard allows them. You must view a dashboard in Flash View in MicroStrategy Web to be able to interact with its widgets, selectors, and panel stacks.


For Indepth understanding of MicroStrategy click on


About Author


Author Bio

TekSlate is the best online training provider in delivering world-class IT skills to individuals and corporates from all parts of the globe. We are proven experts in accumulating every need of an IT skills upgrade aspirant and have delivered excellent services. We aim to bring you all the essentials to learn and master new technologies in the market with our articles, blogs, and videos. Build your career success with us, enhancing most in-demand skills .