MicroStrategy Tips and Techniques

By Kyle Robinson,2014-12-27 22:44
13 views 0
MicroStrategy Tips and Techniques

    MicroStrategy Tips

    MicroStrategy Tips and Techniques

    Reporting Essentials

    Five Styles of Business Intelligence:

    Enterprise Reporting, Cube Analysis, Ad-hoc query analysis, Statistical Analysis and Data Mining, Report Delivery and Alerting

    Basic Components:

    Project Sources : Database location for multiple projects (metadata)

    Server Project Sources: Same as above, but in 3-tier mode using Intelligence Server

The Administration node shows Intelligence Server tools only if you connect to a server project source. The Administration

    node contains the Job Monitor, Project Monitor, User Connection Monitor, Database Connection Monitor, Schedule Monitor, Cache Monitor, Cluster Monitor, Database Instance Manager, Schedule Manager and User Manager

    The Desktop Interface contains the Folder List, Object Viewer, Shortcut Bar, Menu Bar and Toolbar

There are three categories of objects in MicroStrategy:

     Schema Objects: Attributes, Facts, Hierarchies, Transformations, Partitions

     User Objects: Templates, Filters, Metrics, Custom Groups, Consolidations, Prompts, Searches

     Reports and Documents

The Project Preferences page (My Preferences) contains the following tabs: (Tools Menu)

     Home Page: set html home page, background color, image etc.

     Reports: Grid (autostyle, column width, outline mode wrapping, drilling …)

    Graph (default graph type, num rows/cols, size …)

    Grid Graph (position and sizing of a grid display 7.5 only)

    Export (auto export to Word, Excel, Access, Text or HTML)

     Editors: Document (measure units, alignment, grid density, selection behaviour…)

     HTML Document (select layout, update messages, default XSL …)

     Prompts: Add long value to list of prompts, Use Server Cache, Client Object Cache …

The Desktop Preferences page contains the following tabs: (Tools Menu)

     Home Page: Enabling home page for projects (the actual html file is set in My Preferences)

     Browsing options to specify type of headers to display in Object Viewer, show Hidden Objects

     Choose to Show the Data Explorer and System Hierarchy

     History Options (cancel running reports, delete read messages, show only unread, limit warnings

     General: Auto login to last used project, load dependent editor dialogs, default object templates

     With report templates, one can customize headers and footer globally

     Reports: Execution (resize based on rows, cols or use standard size)

     Details (show filters, metric details …)

     Close (prompt for save on closing)

     International: set default language and character set

     Warnings: warn on object delete, when server version is newer than client, when metadata requires update Report

     A report contains attributes, metrics and filters it always has a template and filter

     Report editor has Object Browser, Shortcuts, Objects, Filter, View Filter, Report View

     Template and Filter can be saved separately after report is designed


     Double click an element, right-click on one or more elements, data menu(drill), Toolbar (drill)

     The Keep Parent option lets you see the object from which you drilled

    Page By

     Sections the report. Page By is done using View Page By option. Page By can use:

     Attributes, Metrics, Hierarchies, Consolidations, Custom Groups, Object Prompts (of these)

    Venky Kandaswamy 706882710.doc Last printed 10/1/2013 4:19 PM 1 of 20

    MicroStrategy Tips

    Data Pivot

     Drag and drop rows to cols and vice versa. Also available from Move option


     Only one row or column is used in basic sort.

     Advanced Sort option allows setting up more complex sorts (from Data menu)

     Can choose criteria, Total Position (top, bottom, mixed [as dictated by level], inherit [from report])

    Parent Position (only for custom groups choose where header label appears)

     Hierarchical Sort allows sorting of subtotals with the groups (only when report uses subtotals) Subtotals

     Subtotals can be turned on for specified levels only

     Totals can be specified by

    position (grand totals only, subtotals or none)

    across levels

    group by (takes one level and groups across all levels)

    Outline Mode

     Used for collapsing and expanding sections. Use subtotals to see data for collapsed attributes.

     Accessed from Grid menu or Grid-Options-General tab


     Accessed by right-click menu (for the object) or Toolbar

     Can format Headers, Values, Subtotal Headers, Subtotal Values for each object AutoStyles

     Applies a style to the report. Available from Grid menu. Custom formats can be saved as an AutoStyle. Thresholds

    Thresholds (or stoplights) are conditional formats on metrics. Can use attributes or metrics to define

    thresholds on metrics. Unlimited number of conditions with simple or complex expressions.

    Accessed from Grid menu or Toolbar.


     Banding on reports can be done by row headers or number of rows. Accessed from Grid menu.

    Resizing and Locking Columns and Rows

     Auto column widths, word wrap, fixed widths etc. Turn on View Column handles from Grid menu to do this.

    Graph Properties

     Accessed from Gallery menu.

    Export and Email

     Reports can be exported to Excel, Word, Access, Text, HTML or PDF

    Choose to show application after export

    Basic Filters

Filters can be report filters or View filters. Report filters affect SQL and can use any object. View filters do not change the

    SQL and can only use objects in the Report Objects window. View filters operate on report results at the lowest attribute.

Filter Types

     Attribute Qualification

     Element List Qualification: qualify on a list of element values

     Form Qualification: qualify on an attribute form

     Date Qualification: qualify on date or date range

     Set Qualification (also called Metric Qualification filters) (metric need not be in report)

     Metric Value: qualifies on a metric value

     Rank: qualify on numerical rank (Bottom 20)

     Percent: qualifies on percentage of rank (top 10%)

     Report As Filter: use result set of existing report (should not contain custom groups or consolidations)

     Filter Qualification: use an existing filter to define a new filter

     Set Operators:


    Venky Kandaswamy 706882710.doc Last printed 10/1/2013 4:19 PM 2 of 20

    MicroStrategy Tips


Metrics can be of various types:

     Simple: single function applied to a fact Sum(Cost){Report Level}

     Nested: Uses multiple aggregate operators Min(Sum(Cost)){Report Level}

     Compound: Combination of simple, nested and compound Avg(Sum(Profit){Region, Report Level}){Report Level}

     Derived: Report specific metrics with existing Report Objects (Sum(Cost){Report Level}+Sum(Profit){Report Level})

Metric Properties:

     Level: The level at which the metric is computed. Default is {Report Level}.

     Level is defined for filter and grouping separately (see later)

     Condition: Specify a filter used directly in the metric definition

     Transformation: Specify time-series or other transformation

    {~+} in the metric means it is calculated at the lowest report attribute level Nested metrics use temporary tables for intermediate result evaluation

    Levels, Conditions and Transformations are not allowed in Compound metrics

    Derived metrics can use only Report Objects and are local to that report

Metric Editor:

     Contains Object Browser, Shortcuts, Definition area

     Can specify function to be used in Total subtotal, dynamic aggregation

    Can also specify different total functions available for this metric

    Subtotals can be suppressed from this editor if it does not make sense to total the metric Smart Totals:

     Smart totals can be used with compound metrics using the Allow Smart Metric checkbox

     This computes total on final results

    Shortcut Metrics:

     MicroStrategy provides commonly used derived metrics such as percent-to-total and rank


Prompts can be of various types:

     Filter Definition: allows selection of filtering criteria from hierarchies, attributes (forms and lists) and metrics

     Object: allows selection of which objects to use on templates

     Value: allows creation of single value prompts (date, number or string). Max and Min limits can be specified.

     Level: allows specification of the level of the metric

    All prompts can have default answers specified and can be optional. That is, an answer is not required. Prompted Report can be saved as „static‟ or to be re-prompted. A „static‟ report uses the prompt values and will never re-

    prompt the user.

Searches can also be created as objects. They can search by name and location as well as contained and contained by.

    Search objects can be included in Prompts.

    Prompt Qualification:

    A prompt can use a static list, search object, filter or a relationship filter to qualify the list of elements to display as choices

    for the user. The prompt answer can be used to build Reports, Templates, Filters, Custom Groups or Metrics. A Level prompt is defined and dragged into the level qualifier of a level metric A report as filter can be used as prompt by creating the prompt with this filter and using it in a report Prompt with an imported list can be created using a prompt on attribute qualification and selecting the „Modify‟ and „Text

    File and Shopping Cart‟ style. This will cause a prompt to ask for a text file when the report is run.

    What-if analysis can be performed using value prompts on a metric

    Venky Kandaswamy 706882710.doc Last printed 10/1/2013 4:19 PM 3 of 20

    MicroStrategy Tips

    Advanced Reporting

Report Query Flow: