MicroStrategy ONE

Usage Scenarios

Query Builder allows more control over the queries against your database tables than normal MicroStrategy reports allow, without having to write any SQL statements by hand with the Freeform SQL feature. It allows you to create queries by dragging database tables from the warehouse catalog or directly from the data warehouse into the Query Builder Editor, defining the joins between tables, and selecting the columns of data to be returned. These queries create reports that can be displayed in MicroStrategy Developer or Web. This functionality can be useful in the following scenarios.

When Should I Use the Query Builder Feature?

You can use Query Builder to run queries against a set of database tables 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, or other operational data stores. It can also save time when setting up your project since Query Builder only requires that database tables are imported to a project's Warehouse Catalog. Query Builder does not require you to create an attribute and fact schema for your project. You can even import tables into a project's Warehouse Catalog within the Query Builder Editor. For more information on importing tables with the Query Builder Editor, see Access and Analyze Multiple Data Sources with Query Builder.

Query Builder allows you to pick the tables, columns, and joins used in your queries. This can be helpful if you are more accustomed to creating queries at this level. Query Builder allows you more control over the SQL generated against your database without the need for extensive knowledge on how to create SQL statements.

Query Builder also provides a robust system when moving from one data warehouse to another. Unlike Freeform SQL, which uses static SQL statements that you provide, Query Builder creates the SQL statements based on tables and columns you select and map to MicroStrategy objects. Because of this functionality, Query Builder SQL statements can automatically change to reflect the syntax of a data warehouse.

For example, if you move from a development Oracle data warehouse to a production SQL Server data warehouse, the SQL statements for your Query Builder reports automatically change to reflect SQL Server syntax. For Query Builder reports, this seamless move from a development to a production data warehouse requires that both data warehouses have the same exact table structures, relationships, and column naming conventions.

Query Builder Reports vs. Standard Reports

A Query Builder report is simply a standard report, which has been built using the Query Builder feature. Due to some variations in how standard reports and Query Builder reports are built, there are also some varying functionalities between the two.

You can create Query Builder reports by building your own queries against imported database tables. Although Query Builder reports can only be created on MicroStrategy Developer, once created, they can be executed from both MicroStrategy Developer and Web like any other MicroStrategy standard reports. Functions that you can perform on MicroStrategy standard reports can also be performed on Query Builder reports, including the following:

  • Formatting
  • Exporting
  • Thresholds
  • Filtering
  • Value prompts
  • Graphing
  • Shortcut-to-a-report qualification. For more information on using a Query Builder report as a shortcut-to-a-report qualification, see Reporting Analysis Features.
  • Narrowcast Server subscriptions and report execution
  • Object security
  • OLAP services
  • Prioritization
  • Report Services documents
  • Drilling within a personal Intelligent Cube
  • Scheduling
  • Subtotals

The following features are available for use with Query Builder reports if an attribute and fact schema is used to model the data returned from your queries:

  • Element list prompts
  • Security filters

The following features are not available for use with Query Builder reports:

  • Custom groups
  • Consolidations
  • Transformations
  • Existing filters
  • Save as template/filter
  • Data marting

Query Builder Reports in Report Services Documents

Once created, Query Builder reports can be included in Report Services documents in the same way as standard reports. The same document can also contain reports from other data sources, such as MDX cube reports. For information regarding MDX cube reports, refer to the MDX Cube Reporting Help.

For data to be joined across different data sources in a document, a common attribute is needed across the datasets. In the following diagram, the common attribute which appears on each of the three reports is named A1.

You can establish a common attribute by mapping objects, such as attributes and prompts, retrieved from different data sources to existing objects in the MicroStrategy environment. For more information on mapping columns for Query Builder reports, see Access and Analyze Multiple Data Sources with Query Builder.

For example, in a Report Services document, you have three datasets from three reports: one standard MicroStrategy report, one Query Builder report, and one MDX cube report using SAP BI as the MDX cube source. All three reports use the same attribute, Product. This means that Product is used in the standard report as a project attribute, the Query Builder report has one object mapped to Product, and the MDX cube report uses Product to map one of its characteristics from the imported SAP BI query cube. Because data is joined by the common attribute Product, the document is generated successfully.

If each of the three reports originally has a prompt on Product, then the prompt will only be displayed one time when a user executes the document; this means a user only needs to answer the prompt one time, instead of three times.