MicroStrategy ONE

Mapping column expressions to attributes and metrics

Use the Selections pane (the bottom pane on the right) in the Query Builder Editor to map the columns in your SQL query to attributes and metrics that appear in the Query Builder report.

When columns are added to the Selections pane, Query Builder automatically maps the column to an attribute or metric object, based on the column name. For example, if you map the column YEAR_ID, Query Builder names the object "Year". You can modify this automatic mapping by renaming the object or replacing the object in the Selections pane.

You can map columns to:

  • Managed object attributes and metrics: Managed objects are created on the fly and only exist in the context of Freeform SQL and Query Builder reports. These objects are stored in the Freeform Objects folder, and are known as managed objects. For details on how to do the mapping, please refer to the topics listed below. For more information on what a managed object is and how they are used in MicroStrategy, see the Advanced Reporting Help.

    Managed objects are also created when integrating MDX cube sources into MicroStrategy. MDX cube sources include SAP BW, Microsoft Analysis Services, Hyperion Essbase, and IBM Cognos TM1. For information on integrating MDX cube sources into MicroStrategy, see theMDX Cube Reporting Guide.

  • Project attributes: These attributes exist in the project and may be used to generate standard reports. Project attributes allow a designer to present a consistent perspective to users. Project attributes also facilitate joins with standard MicroStrategy reports in a Report Services document. This capability enables you to create many types of documents.

    • For example, you can create a Report Services document with two datasets that share a common project attribute. One dataset pulls historical data from a data warehouse while the other contains current data from an operational system accessed with Query Builder.

    • Another advantage of mapping data to project attribute is that you can then use and enforce security filters with Query Builder reports.

    • Unlike attributes, which can be mapped to project attributes, all metrics are created or mapped to managed object metrics. The only existing metrics you can use are managed object metrics that are defined for Freeform SQL and Query Builder reports in the current project.

You can perform the following mapping tasks: