MicroStrategy ONE

Customize Your SQL Queries: 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.

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. A 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.

In addition to simplifying the process of data access, Query Builder provides the majority of MicroStrategy's wealth of reporting and analysis capabilities. For more information on the MicroStrategy features that apply to reports created with Query Builder, see Usage Scenarios.

Reports that are built using the Query Builder feature are referred to as Query Builder reports in this section.

The following image shows the Query Builder Editor, where you choose the tables, columns, and joins to define the database queries for a report.

The individual panes of the Query Builder Editor shown above provide the following features:

  • Object Browser pane: This pane is located on the left side of the Query Builder Editor. Here you can browse the Warehouse Catalog or your data warehouse directly for tables to include in your queries. You can also browse the project folders for objects such as attributes that can also be used to build your queries.
  • Tables and Joins pane: This is the top pane on the right side of the Query Builder Editor. Tables and joins can be added to the definition of your queries here, which hold the columns that provide the data from which your report results are drawn. For more information on how Query Builder uses tables and joins to build queries, see Access and Analyze Multiple Data Sources with Query Builder.
  • Conditions pane: This is the middle pane on the right side of the Query Builder Editor. Here you can define qualifications and insert security filters that will be included in the WHERE or HAVING clause of your SQL queries, which filter the data that appears on the resulting report. For more information on defining qualifications for Query Builder reports, see Reporting Analysis Features.
  • Selections pane: This is the bottom pane on the right side of the Query Builder Editor. Columns added to this pane are included in the SELECT clause of your SQL queries and become the attributes and metrics on the resulting report. For more information on selecting columns in Query Builder, see Access and Analyze Multiple Data Sources with Query Builder.