BQL Basics

Basic Elements and Query Syntax

The basic form of a BQL query is:

SELECT [attribute],[measure] FROM [subject_area] WHERE condition ORDER BY [attribute|measure] ASCENDING|DESCENDING

You can select one or more attributes or measures from a given subject area, filter results, and sort them.

BQL syntax requires that attribute, measure, and subject area names are surrounded by square brackets. Strings are surrounded by single quotes.

A subject area is a grouping of attributes and measures in the space. The default subject area is “ALL”, which indicates the entire set. Most BQL statements use [ALL]. For ease of use or security reasons, you may create a custom subject area having a subset from the entire list. See Creating and managing a subject area in the Infor Birst Administrator User Guide.

This is an example of a basic BQL query:

SELECT [Order_Date: Sum: Quantity], [Products.Products] FROM [ALL] WHERE [Order_Date: Sum: Quantity]>=50000 ORDER BY [Order_Date: Sum: Quantity] DESCENDING

To put comments in BQL code, use this syntax:

/* my comment */

• Syntax and Conventions for Examples
  • These are the conventions followed in the examples:
    • Keywords are in ALL CAPS.
    • Pipes (|) separate multiple possible entries (attribute|measure means either an attribute name or a measure name).
    • Placeholders for element names are in italic font.
    • Optional elements are in curly brackets { }.
    • Most examples do not specify header strings.
    • Most examples use [ALL] as the subject area.
  • It is not required to write all queries by hand. You can use the Visualizer drag-drop interface to interactively build queries, then click Advanced Tools > Queries to see the BQL statement that was generated via drag and drop.
  • For advanced users with data warehousing experience, use Navigate Query in the Space Admin > Query Admin to see how the query engine executes the logical query against the physical tables. See Query Admin in the Infor Birst Administrator User Guide.

Hierarchy Attributes (Dimensional Attributes)

To create a query that includes a hierarchy column, use its fully qualified name, the column name preceded by the hierarchy name. For example, to select an attribute:

SELECT [hierarchy.attribute] FROM [subject_area]

For example, to select all categories of products:

SELECT [Products.Categories] FROM [ALL]

Measures (Fact Columns)

The syntax for including measures in a query is similar to attributes. Because measures are for counting, they have additional functionality including aggregates and date type (Analyze by Date enablement in Prepare/Model Properties). Aggregates are required; date types are optional though strongly encouraged on those date columns where it is important to slice data by time series (i.e., YTD).

SELECT [{date_type: }aggregate: measure] FROM [ALL]

• Aggregates for Measures
  • Aggregates are required in the SELECT statement and they are case-sensitive. Aggregates include:
    • Avg
    • Count
    • Count Distinct
    • Max
    • Min
    • Sum

  • For example:

    SELECT [Order_Date: Sum: Quantity] FROM [ALL]

    Table 2. Query Result

    Order_Date: Sum: Quantity
    2,833,776

  • Two special aggregates use a slightly different syntax. Count and Count Distinct use the hash sign (#) and do not use a colon:
    • # Distinct (count distinct)
    • # (count)

  • For example:

    SELECT [# Distinct Quantity] FROM [ALL]

    Table 3. Query Result

    # Distinct Quantity
    502

Data Types for Measures

For all measures there is a default. For many of the measures, there is an optional syntax for specifying a "by Date Type".

The default date type is Load Date, the last time the data source was loaded.

Use the date type syntax before the aggregate syntax:

SELECT [attribute],[{date_type: }aggregate: measure_name] FROM [ALL]

For example, by Order Date:

SELECT [Order_Date: Sum: Quantity] FROM [ALL]

To ensure measures make sense in a visualization, include at least one attribute in the query.

For example, to analyze quantity by category:

SELECT [Products.Categories],[Order_Date: Sum: Quantity] FROM [ALL]

Column Header Strings

The syntax for changing column headers is optional. By default Birst uses the column name from the data store as the header.

SELECT [attribute | measure {'header_string'}, attribute | measure {'header_string'} ...] FROM [subject_area]

For example, to change the column headers from Categories to Category and Quantity to Total:

SELECT [Products.Categories] 'Category', [Order_Date: Sum: Quantity] 'Total' FROM [ALL]

Filtering Results: WHERE

In addition to selecting attributes and measures, you can restrict the results to various subsets using a WHERE clause.

For example, if you want to know the sales for 2014:

SELECT [Order_Date: Sum: Unit_Sale_Price] 'Sum of Unit Sale Price in 2014' , FROM [ALL] WHERE [Time.Year]=2014

For example:

SELECT [Order_Date: Sum: Unit_Sale_Price] 'Sum of Sales Before 2014' , FROM [ALL] WHERE [Time.Year]<=2014

Controlling Sort Ordering: ORDER BY

You can specify how results should be sorted by attribute orderings or measure orderings. Sorts can be either ASCENDING or DESCENDING. The abbreviated syntax ASC and DESC are also supported.

For example, to show product categories in descending order of sales:

You can define multiple sorts. For example, to sort by store region, then by quarter for 2013:

SELECT [Order_Date: Sum: Quantity], [Retail_Stores.Retail Region], [Time.Quarter] FROM [ALL] WHERE [Time.Year]='2013' ORDER BY [Retail_Stores.Retail Region] ASC, [Time.Quarter] DESC

Returning a Limited Result Set: TOP

Use the TOP operator to return a limited number of rows in the result set from the database.

For example:

SELECT TOP 5 [Order_Date: Sum: Quantity], [Products.Products] FROM [ALL] ORDER BY [Order_Date: Sum: Quantity] ASCENDING

This example causes the query to return only the top 5 records, as determined by sort order.

Whatever sort ordering is applied, TOP limits the results to the first ones in the result set. For example, if you change the sort order to DESCENDING, the TOP would be very different.

SELECT TOP 5 [Order_Date: Sum: Quantity], [Products.Products] FROM [ALL] ORDER BY [Order_Date: Sum: Quantity] DESCENDING

INNER and OUTER Joins: USING OUTER Join

Depending on the underlying data, you combine measures in a result set with the USING OUTER JOIN syntax.

By default, the BQL query engine returns only results that have values for all measures that are specified. This behavior generates more efficient underlying queries and conforms to standardized data warehousing techniques.

However, sometimes you want to include all dimensional attributes that have any cells filled, regardless of whether some measures have values. This is called an outer join and you employ the USING OUTER JOIN syntax. For example:

SELECT USING OUTER JOIN [Products.Categories], [Order_Date: Sum: Quantity], [Order_Date: Sum: Revenue] FROM [ALL]

Constants

BQL supports string or numeric constants in queries. Surround strings in single quotes.

SELECT 'Test_Constant', [Order_Date: Sum: Quantity] FROM [ALL]

A string constant in the SELECT statement results in a column that includes the string in each row, including the header. For example:

For more BQL syntax, see Advanced BQL.