Design Indicators, Filters and prompts within Microstrategy 9.

Printer-friendly version

Before proceeding with the design of indicators, filters, prompts, and see how to develop templates for our reports, we will see a bit more in detail issues that have to do with the attributes of the dimensions (which we saw were set in the previous entry in the blog ), and that will be useful in developing the project.

Types of hierarchies.

Hierarchies allow us to browse and attributes. In Microstrategy, we have two types of hierarchies:

System hierarchy: The hierarchy of the system, including the defined relationships between the attributes of the project.This hierarchy is not necessary to define, but the tool automatically generates Desktop to create the project (which was built using the Architect).

This is the default hierarchy, and contains all the attributes of the project, part of the schema definition. After creating the project, is the only hierarchy that exists. The system hierarchy is not edited, but is updated each time you add or remove child or parent attributes using the attributes editor or architect, or add new attributes to the project.

Project System Hierarchy Enobia

The hierarchy of the system is useful for determining the relationships between all objects in the project (you can navigate through it to see the values ​​of attributes and how they relate to each other).The attributes defined in the hierarchy of the system does not need are defined in a user hierarchy. The attributes of the hierarchy of systems not using in user hierarchies are still available for use in reports, filters, or consolidations.

User hierarchy: user hierarchies are groups of attributes and relations between them, coupled in a manner that is meaningful in the context of analysis.Not necessarily have to follow the logical data model. Moreover, unlike the hierarchy of system allows us to define limitations to the number of displayed values, the values ​​set filters to view or define an entry point, which will be the attribute which will position us to use the hierarchy (for example, navigating or performing filtering.)

Hierarchy Editor

For example, we create a hierarchy that includes attributes Year, Quarter, Month and Day. When we surf the attributes with the Data Explorer, double-click on the year, we appear quarterly, then monthly and so on.

In our project we will create all necessary hierarchies to allow navigation through the different attributes of the dimensions.

User hierarchies are available when we will navigate the data in the reports and documents of Microstrategy, and we will provide the functionality Drill Up and Drill Down among the attributes that define within it.They are therefore an indispensable element in shaping the system, they are the starting point to take full advantage of dimensional navigation. For example, if in the above example we are viewing the attribute Quarter, we can sail up (drill up) by the hierarchy until, or browse down (drill down) to the month, or switch to another and change the navigation jeranquia .

They are the only kind of hierarchy can be defined by the user and may create all the hierarchies that may be required within a project.We will have to think of all the hierarchies required to cover all needs within the business model and outline the company's data warehouse.


Are schema objects that can be defined using attributes. Used to run time series analysis and period to date, current period against the previous period or periods changing analysis.The transformations we define the attributes (for example, in the image, we are using a date, which we subtract 14 days). This will set a variable value that depends on the value of the attribute to which it is related, and then use to create the indicators.

Transformations Editor

For example, we have a number called Net Income (estimated from net income of fact), we show current net sales in the period we are analyzing.If we create an indicator called Net Income for 14 days (and we associate the transformation defined in the image) when using the indicator in an earnings report will show 14 days earlier. Transformations are a very powerful and allow us to easily perform the analysis of time series.

All objects seen so far, attributes, events, user hierarchies and the changes are part of the schema objects, and are the starting point for the construction and development of our Business Intellingence system. Let's see what elements we can build with them:

Design of indicators, filters and selections.

Before proceeding, I recommend you view the video produced by speaking Microstrategy design of indicators, filters and selections.


Indicators are MicroStrategy objects representing business measures and key performance indicators.From a practical standpoint, the indicators are the calculations on data stored in the database and the results are shown in a report. They are similar to formulas in a spreadsheet program. It would not be an exaggeration to say that the core of virtually all reports are indicators.Most of the decisions made about the other objects to be included in a report depend on the indicators used in the report.

In particular, the indicators define the analytical calculations are performed with the data stored in the data source. An indicator is composed of facts and data source of mathematical operations to be performed on those facts to, thus, to carry out a meaningful analysis of business results.

Indicators Editor

In the indicators can be defined several things, but the key element is the formula that determines its value. A metric formula can consist of facts from the data source (as in the example above), business attributes of the source data or other indicators have already been created.The following examples of indicators show different features of these formulas:

  • Indicator consists of facts: (Sum (Revenue)-Sum (Cost)). The formula for this indicator sums all the benefits recorded in the data source, add up all the costs registered in the data source and then subtract the total cost of all benefits.
  • Indicator consists of attributes: Count (Customers).The formula gives an account of customers of the company registered in the data source.
  • Indicator consist of other indicators: [Benefits of this month] - [Benefits Month] / [Benefits of last month]. The formula for this indicator subtracts the benefits of last month (an indicator) of the benefits of this month (another indicator) and divide by the result of last month to determine the percentage difference in profits from last month.

The formulas can contain mathematical functions and has a wide range Microstrategy of them to allow for multiple types of calculations:

Funciones matematicas para utilizar en las fórmulas

Besides the formula, we can indicate on the properties of the indicator:

  1. Level of calculation of indicators: the level in the hierarchy of attributes in which we calculate the indicator.By default, an indicator is calculated in the report level, ie at the level of the attribute of the report, which is the indicator. But there may be occasions on which the calculation makes sense only at a certain level (not at all possible according to the hierarchical structure of attributes of the dimensions).With this attribute we condition that aspect.
  2. Condition: Condition that is included in the data from the data source and applies only to data related to that particular indicator. The filter becomes part of the definition of the indicator. The application of conditionality in an indicator makes the calculation of an indicator is described by the filter indicator, regardless of what is specified in the report filter, if any. Allows us to define indicators on their own terms regardless of the conditions attached to the report level.
  3. Transformation: The changes are typically added to the indicators designed for time series analysis, for example, to compare values ​​at different times, as compared to the previous year or month to date.The transformations are useful for discovering and analyzing time-based trends in datos.Las transformations are created as separate objects (as seen above) and added to an indicator.

In the Tutorial project that offers Microstrategy can see examples of how to define indicators for all possible types, from functions to manipulate dates, strings or numbers, statistical functions, financial, OLAP and data extraction functions for DataMining.


The filters allow us to restrict the values ​​to different elements of our system.For example, we set a filter on the attribute year and select a particular value (eg 2009), and to run a report with this filter, only recover the values ​​of that year. We could also have defined the filter dynamically, so that when you run the report, we aparecezca a list of years and choose the one that we will discuss (it will prompt filter).

Filter Editor

You can also define filters on the indicators, for example, to limit values ​​that we do not appear to be less than a value or the like.

Prompts (Prompts)

A prompt is a question that makes the user running a report.Any report may contain prompts. This information allows us to give dynamism to the reports and to run the same report with different performance criteria. Prompts can be mandatory (that is essential to indicate a value) or optional.

Prompts are used to select the filter conditions at the time of report execution.It can dynamically determine an attribute, an attribute element selection, metrics and other objects that may be included in a report. Prompts can be used along with other selections static filters, and can add a report as many as needed.

Creating Prompts

We have the following types of prompts:

  • Dynamic selection of a filter definition: allow users to set filter criteria in the implementation of reports, to be applied to the values ​​returned in the execution of these.We can set filters on the following items:
    • Hierarchies: You can dynamically choose the classification of a hierarchy or between any attribute of any hierarchy. We can choose between all the attributes that form a hierarchy and select the filter values ​​from the values ​​shown for each attribute.
    • Attributes: As with the hierarchies, it is possible to dynamically choose a particular attribute. Similar to the previous case, but only qualifies for a particular attribute (eg, year).
    • Item List an attribute.This option lets you choose from a preset list of attribute elements, or they may show all the elements. We can pre-select a list of possible values ​​of an attribute, and the previous list, select the desired values.
    • Rate on a measure. The classification of an indicator can also be done dynamically when running a report. We can set restrictions to the values ​​of the indicator.
  • Dynamic selection of an object: allows the user to select which objects (such as attributes, metrics, custom groups, etc) to include in the report. For example, we have a list of various indicators, and when you run the report, select only the indicators that interest us.
  • Dynamic selection of a value: Allows dynamic selection of a date, a numerical value or text.The return value can then be used to define filters on a metric or attribute values.
  • Level prompt: This option allows the user to specify the level of an indicator calculation.

Reporting Templates

Templates are objects that serve as a basis for building the reports. Define the layout that will have the report. The templates specify the jointdata to which the report must retrieve the data source and also determine the structure in which information is displayed in the report's findings.

When you create a template, place the different objects in Microstrategy, with which we can use the following attributes, metrics, filters and prompts (in addition to custom groups and bindings).

Basically, we have two types of layout:

  • Crosstab (cross-tab): useful for multidimensional analysis.
  • Tabular: useful for simple lists of information.

Template Editor

The templates will be the basis for building reports.


We passed over the main components that form the basis of Microstrategy BI system.Elements are much more complex than we have seen, with many options and features that will give us many possibilities in order to cover our requirements.

I recommend a close reading of the documentation provided by MicroStrategy to know each of the elements:

Microstrategy Documentation Structure

Seen everything we can highlight:

  • User hierarchies: we can customize the structure of the attributes defined in the model.Allows us to give our approach to the analysis, bypassing the general hierarchical model. Also, being able to define filters on them would allow us to create hierarchies tailored to the person who will analyze the information (eg, geographical area).
  • Indicators: go far beyond simple calculations using the values ​​we have generated in the fact table of our model. The use of functions that MicroStrategy has allowed us to do almost anything.Interestingly enough power available to calculate indicators related topics attributes (such as accountants).
  • Filters and prompts: give us momentum in the reports at all levels, not just the time to filter information to show discriminating information for the values ​​of attributes or indicators, but also to select which items we want to appear in the reports dynamically. The availability of choices for the values ​​of the attributes of a dimension, you can select between the different attributes of a hierarchy and hence, values, also gives us powerLooking filtering.

With that seen at least a few basics to start working with Microstrategy 9, define the basic elements and then use (such as user hierarchies, indicators, filters and prompts) and to further deepen the other components of the tool .


Great collection of tools that allow us to include motion graphics, maps, etc ...
Strategic analysis based on Business Intelligence tools are encroaching more and more deeply, enterprise environments. Lead to large multinational chains in decisions about their products helping, for example, to determine which customers should be directed to increase sales, generate new links or existing loyalty. The scope of business intelligence is undergoing a major change is an...
In a previous blog entry ( OpenSource Database. Why do we choose MySQL for our project? ), we talk about open source databases as interesting and reliable option for the development of Business Intelligence projects. We saw some different products and compare between them. But there are other alternatives (with limitations in most cases), allowing us to start working with "big" of a...