BQL Basics

The Logical Query Language (BQL) includes the following functionality:

For more advanced BQL syntax see Advanced BQL and Advanced BQL Functions.

Basic Elements and Query Syntax

There are two basic elements in Birst reports and dashboards: attributes and measures. They correspond to dimension attributes and fact columns in the underlying data model. BQL uses attributes and measures to generate query results.

Tip: The terms "attribute" and "measure" are from the realm of data warehousing. If you are familiar with spreadsheets or with relational databases, you can think of attributes and measures as two different kinds of columns. Attributes contain descriptions or the names of things. Measures are columns that contain counts or amounts. This is not a perfect analogy but the concept can help you understand what is going on in the underlying system. As you write BQL queries and expressions and see the results these terms will become very natural to you.

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”, meaning the entire set. Most BQL statements use [ALL]. For ease-of-use or security reasons you may create a custom subject area that has a subset of the entire list. See Creating a Custom Subject Area.

Note: Birst for CloudSuites licensed products cannot create Custom Subject Areas. (CSAs).

Here 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 the following syntax:

/* my comment */

Syntax and Conventions for Examples

For the purposes of this documentation, the examples follow these conventions:

  • 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.
  • Optional elements are in curly braces { }.
  • Most examples do not specify header strings.
  • Most examples use [ALL] as the subject area.

The data set is a variation of Retail Analytics, which is similar to Northwind.

The query results are from the Admin - Define Sources - Query Admin - Queries tab, unless otherwise stated.

Tips:

  • You do not have 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 Admin - Define Sources - Query Admin - Queries tab to see how the query engine is executing the logical query against the physical tables. See Reading Query Navigation Results.

Attributes (Dimensional Attributes)

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

SELECT [dimension.attribute] FROM [subject_area]

For example, to select all categories of products:

SELECT [Products.Categories] FROM [ALL]

The result is:

Categories
Desktops
MP3 Players
Mobile Phones
Notebooks
Tablets

Measures (Fact Columns)

The syntax for including measures in a query is similar to that of attributes. Because measures are for counting things, they have additional functionality including aggregates and date types. Aggregates are required; date types are optional though strongly encouraged.

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]

Results in:

Order_Date: Sum: Quantity
2,833,776

Two special aggregates use 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]

Results in:

# Distinct Quantity
502

Date Types for Measures

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

To specify a different "by Date Type", the measure must have one or more related date type columns in its data source. If the related date type columns are marked "Analyze by Date" in Admin - Define Sources - Manage Sources - Columns, then Birst creates additional columns in the model that support querying by the date type. For example, the Quantity measure has related Order_Date and Ship_Date columns that are enabled for analyzing Quantity by date. See Defining Column Properties.

Note: Modeler Pro provides data aggregation based on the data you import. You will not have the option to set the analyze by date feature as described

The default date type is Load Date, which is the last time the data source was loaded. You can see those dates in Admin - Process Data - Process New Data - Recent Processing History.

Important: Be sure to use the date type that makes sense for the particular measure and the calculation you want to create.

Use the date type syntax prior to the aggregate syntax:

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

For example, by Order Date:

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

Order_Date: Sum: Quantity
944,592

Very few measures make sense in a report without including at least one attribute in the query. For example, to analyze quantity by category:

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

Produces the following results:

Categories Quantity
Desktops 74,485
MP3 Players 308,100
Mobile Phones 135,397
Notebooks 117,216
Tablets 309,394

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]

Produces the following results:

Category Total
Desktops 74,485
MP3 Players 308,100
Mobile Phones 135,397
Notebooks 117,216
Tablets 309,394

Column Headers in Designer and Visualizer

When Designer automatically creates queries for you based on what you drag-and-drop, it uses internal syntax ('F1', 'F2', etc.) to map the header placeholder to the header from the data store, and displays the header. In Designer you do not use BQL to change the headers, you click on the column header and select Label Properties to modify the column label.

Visualizer does the same, though it uses 'COL0', 'COL1', etc. as the placeholder. You can see this for a report's query at Visualizer - Advanced Tools - Queries. To change headers in Visualizer, double-click on the column label in a table or on the axis label text on a chart.

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 wanted to know sales for 2015:

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

Sum of Unit Sale Price
in 2014
14,594,869.03

The filter column does not have to be one of the SELECT columns. A WHERE clause can include any attribute filtered for any specific value. Filtering operators include:

  • = (equals)
  • > (greater than)
  • <  (less than)
  • >= (greater than or equals)
  • <= (less than or equals)
  • <> (not equals)

For example:

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

Sum of Unit Sale Price Before 2014
94,639,114.88

Tiip: See also Result Sets and Display Operations - DISPLAY WHERE.

Filtering Results on Measure Values

You can restrict results based on measures. For example, to see a list of products that sold at least 50000 units in 2014:

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

Quantity Products
103,445 mangoPad 3 32 GB
94,946 mangoPad 3 64 GB
105,703 mangoPhone 4G 32GB
65,229 mangoPhone 4G 64GB

The query returns only the records where the measure column matches the filter when grouped by the attributes in the query.

Tip: See also Result Sets and Display Operations - DISPLAY BY.

Using Multiple Filters: AND, OR

More complicated queries can combine several individual filters using AND and OR syntax. For example, to see a list of products that sold at least 50000 units in 2014:

SELECT [Order_Date: Sum: Quantity] 'Quantity', [Products.Products] FROM [ALL] WHERE [Order_Date: Sum: Quantity]>=50000 AND [Time.Year] = 2014

Quantity Products
50,117 mangoPhone 4G 32GB

You can add an OR clause and use parenthesis to indicate which AND or OR takes precedence:

SELECT [Order_Date: Sum: Quantity] 'Quantity', [Products.Products] FROM [ALL] WHERE [Order_Date: Sum: Quantity]>=50000 AND ([Time.Year] = 2014 OR [Time.Year] = 2013)

Quantity Products
103,445 mangoPad 3 32 GB
105,703 mangoPhone 4G 32GB
65,229 mangoPhone 4G 64GB
94,946 mangoPad 3 64 GB

Filtering a Single Measure

In addition to filtering an entire result set, it is often useful to be able to filter specific measures by an attribute. This is also called a "measure filter". For example, to show Quantity for all sales and only for sales in France. To calculate this, you add a WHERE clause to the measure section, instead of after the FROM clause:

SELECT [Order_Date: Sum: Quantity] 'Quantity All', [Order_Date: Sum: Quantity] WHERE [Retail_Stores.Retailer Country]='France' 'France Only' FROM [ALL]

Quantity All France Only
944,592 3,352

Only filters on attributes are allowed in a measure filter. This is because a measure is undefined without a level of aggregation. If you want to filter based on a measure at a given level, you can use bucketed measures that turn measures into attributes with values bucketed based on the measure. This will make certain what level of aggregation is applied to a measure before it is applied in a measure filter. See Creating Bucketed Measures.

Filtering on a Measure-Filtered Column

Occasionally, you would like to filter a result set based on a filtered measure column. The syntax to do this is a slight alteration of both the normal filter syntax and the measure filter syntax. The filtered measure is surrounded by parenthesis to indicate that this is a measure filter.

SELECT [Time.Year/Quarter], [Products.Categories], [Order_Date: Sum: Quantity] 'Quantity', FROM [ALL] WHERE ([Order_Date: Sum: Quantity] WHERE [Products.Categories]='Tablets')>=25000

Year/Quarter Categories Quantity
2013/Q1 Tablets 35,284
2013/Q2 Tablets 45,466
2013/Q3 Tablets 33,849
2013/Q4 Tablets 25,436
2014/Q1 Tablets 45,848
2014/Q2 Tablets 41,534

Set-Based Filtering: IN, NOT IN

You can create a subquery that defines a set of values used to filter the parent query. This allows for more complex filtering conditions. For example:

SELECT [Products.Products],[Order_Date: Sum: Quantity] FROM [ALL] WHERE [Products.Categories] IN (SELECT [Products.Categories] FROM [ALL] WHERE [Order_Date: Sum: Quantity]<=100000)

Products Order_Date: Sum: Quantity
iMango Desktop 22 15,439
iMango Desktop 27 12,823

Mango Mini 3.0 GHz

 3,348
Mango Mini 3.5 GHz 11,810
Mango Server 5,211
Mango Workstation 12 Core 2,355
Mango Workstation 8 Core 10,003
Mango Workstation Quad Core 13,496

In this example the filtering is done on Products.Categories, which is not displayed, and all the Products in the category that meets the criteria are displayed.

Important: BQL expressions that perform operations, including query functions (RANK, CONCAT, etc.) and positional calculations, in subqueries are not supported. The "IN" or "NOT IN" subquery can use the basic operators such as equal to (=), less than (<), etc.

Important: Designer supports set-based filters. Visualizer does not.

Date Syntax in Filters

You may need to refer to a specific date in a BQL query WHERE filter. For slash-style dates, add the hash sign around the string. For example:

...WHERE [Month.Start Date]=#11/1/2015#

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:

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

Order_Date: Sum: Quantity Categories
309,394 Tablets
308,100 Mobile Phones
135,397 MP3 Players
117,216 Notebooks
74,485 Desktops

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

Order_Date: Sum: Quantity Products
1,218 Mango Air 11 128GB
1,623 MangoBook Pro 15 3.5 GHz
2,355 Mango Workstation 12 Core
3,348 Mango Mini 3.0 GHz
4,525 MangoBook Pro 17 3.5 GHz

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

Order_Date: Sum: Quantity Products
105,703 mangoPhone 4G 32GB
103,445 mangoPad 3 32 GB
94,946 mangoPad 3 64 GB
65,229 mangoPhone 4G 64GB
48,641 mangoPhone 3G 32GB

Tips:

  • The TOP operator is helpful when you have a large result set and you do not need to see all the rows, or you want to improve performance.
  • By default, Visualizer uses the TOP operator to limit the return set to the top 100 rows. You can change the TOP setting by clicking the Top N Results button.

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 or not some measures have values. This is called an “outer join” and you use the USING OUTER JOIN syntax. For example:

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

Tips:

  • By default, Designer and Visualizer generate queries having the USING OUTER JOIN syntax.
  • In formal data warehousing based on star schema, this is not a significant issue because all elements have a value for all periods (at the intersection of all dimensions, all cells in the cube are full) even if it is set to zero. However, in practical warehousing where the underlying tables may be in a more normalized format, there may be no values for various dimensional attributes and outer joins are required.

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:

Test_Constant Quantity
Test_Constant 150,028
Test_Constant 215,021
Test_Constant 330,081
Test_Constant 249,462

For more BQL syntax see Advanced BQL.

See Also
BQL Quick Reference
Expressions Reference
Report-Level Scripting
Pre-Aggregation vs. Post-Aggregation Calculations