Introduction to BQL

The Birst Logical Query Language (BQL) is a fundamental feature of Birst. By understanding and using this language, advanced data modelers and report authors can get the most out of Birst.

Prior to Learning BQL

Before you dive into BQL, it helps to have a basic understanding of databases, data warehouses, and business analysis and reporting. If you are new to these technologies or want a refresher, watch the Introduction to Birst videos available at the Education site.

This topic discusses BQL functionality and query architecture, and shows where in the product you can use BQL to create powerful reports. Subsequent topics go into more detail about the basic syntax, and dive deeper into the most powerful aspects of the language.

About BQL

BQL is a powerful logical query language that supports important capabilities in Birst, including:

  • Dashboards 2.0 filters and KPIs
  • Queries, conditional formatting, and custom calculations for measures in Visualizer reports
  • Queries, scripts, and conditional formatting in Designer reports
  • Shared ("saved") expressions for Dashboards 2.0, Visualizer, and Designer
  • Custom variables for a space
  • Custom attributes and custom measures that enhance the data model in a space
  • ETL scripts

BQL vs. SQL Syntax

BQL is a logical query language that supports the logical data model that is based on a star schema. SQL supports a relational schema. BQL syntax is very similar to SQL, so it may appear familiar to you, however it has important differences. For example, consider this basic BQL statement and its results:

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

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

The BQL SELECT statement returns the quantity ordered for each product for quantities 50000 or over, sorted by quantity. [ALL] means to query from the full set of attributes (dimensions) and measures (facts) in the default Subject Area of the space. BQL uses [ALL] instead of a table name because the SELECT statement already specifies the dimension (Products) and the attribute (CategoryName). This is quite different than how SQL syntax would work in an RDBMS.

See BQL Basics for a more detailed overview of basic syntax.

Queries and Where Calculations Occur

At a high level, the following diagram shows that the Birst user interface sends BQL to the Birst middle tier from the logical layer. The middle tier executes the query logic. For some important scenarios, the middle tier passes the queries onto the data tier and processes those results. Then the middle tier returns the results to the user interface, to be rendered in reports and dashboards.

Because there are two levels where calculations occur in Birst, it helps to understand at a high level what happens where. Queries for different purposes are executed at different tiers.

  • Queries executed in the middle tier for report expressions, variables, and ETL scripts
    Report expressions and ETL scripts execute in the middle tier, in the Birst ROLAP engine. These are called "logical queries" or "post-aggregation queries". Post-aggregation means that calculations occur after the results are returned from the database in the logical layer (middle tier).
  • Queries executed in the data tier for custom measures and custom attributes
    Custom measures and custom attributes are additions to the data model which are based on formulas that are calculated in the data tier. These are called "physical queries or "pre-aggregation queries". Pre-aggregate queries used for custom measures and custom attributes have a slightly different BQL syntax than logical queries. See Formula Reference for Custom Attributes and Measures.

In some scenarios it is helpful to calculate report expressions in the data tier. This is also called "push-down expressions". See Pre-Aggregate Report Expressions.

Using BQL to Customize Spaces

Birst spaces are containers for the data model, its reports, and dashboards. Custom objects defined for a data model are reusable across reports and dashboards. Space Administrators can create custom attributes, measures, and variables.

Custom Attributes and Measures for the Data Model

Space Administrators use BQL to create custom attributes and custom measures for the data model. These can be shared with any reports in the space. Custom attributes or measures created at Admin - Customize Space are available in the Subject Area of Designer and Visualizer. See Creating Custom Attributes and Creating Custom Measures.

As previously mentioned, custom attributes and measures use a slightly different syntax than BQL used in report expressions or in ETL scripts. See Formula Reference for Custom Attributes and Measures.

Custom Variables

Space Administrators can use BQL to create custom variables at the space level for use in any reports in the space. The BQL syntax for variables is the same for report expressions.

See Using Variables and Creating Variables.

Using BQL in Dashboards and Reports

Visualizer, Designer, and Dashboards 2.0 automate most of the BQL for you. Birst gives you more control over reports and dashboards by letting you write BQL for report expressions. Expressions can be used for key performance indicators (KPIs), filters, report-level attributes and measures, and for conditional formatting.

Dashboards

Dashboards 2.0 supports expressions as key performance indicators (KPIs) and as an indicator expression for KPI indicator shape and color. See Adding KPIs to Dashboards.

You can use BQL expressions for sophisticated dashboard filters. See Creating Dashboard Filters.

Visualizer Reports

The Visualizer Expression Builder helps you add report-level attributes, measures, or filters based on BQL expressions. You can make these available to other reports in the same space using the Make Global option.

Note: Visualizer is not available to "Birst for CloudSuites" licensed customers.

See Adding an Expression in Visualizer Expression Builder.

For Visualizer tables you can use BQL expressions to specify conditional formatting. See Conditional Formatting for Tables and Crosstabs.

Tip: To see the BQL generated by Visualizer when you drag-drop attributes and measures onto the work area, click Advanced Tools - Queries. You can try out queries and then copy/paste the relevant text into your expressions.

Designer Reports

Designer has an Expression Builder for adding report expressions. You can save an expression, called a "saved expression", for re-use in other reports in the same space. Use report expressions to define report-level attributes or measures and custom calculations based on one or more base measures, and add them to the Subject Area. These are different than space-level custom attributes or measures. See Defining Expressions in Designer and Using the Expression Builder.

Note: Designer Reports is not available to Birst Professional licensed customers. It is available as an add on if you upgrade to Birst Professional Pro licensed product.

Use BQL also for Designer report-level conditional formatting based on expressions. See Creating a Conditional Display Expression.

BQL lets you pass filter (prompt) values to expressions. See GETPROMPTFILTER and GETPROMPTVALUE. .

Designer Expression Builder lets you write scripts that support advanced data manipulation at the report level. See Report-Level Data Manipulation Using Scripts.

Tip: To see the BQL generated by Designer when you drag-drop attributes and measures onto the report layout, click Options - Report Query. You can try out queries and then copy/paste the relevant text into your expressions.

Using BQL in ETL Scripts for Transforming Data

When source data is imported to Birst, it may need modifications so that it can better map to the data model. Birst provides scripting functions to facilitate this process. ETL Services scripts transform and manipulate source data prior to being loaded into Birst. This is called a "scripted source". ETL scripts use a subset of BQL that is evaluated at the middle tier. To create scripted sources see Creating a Scripted (ETL) Data Source.

Syntax References

To learn about the details of BQL queries, expressions, and ETL scripts, see: