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.
- About BQL
- Queries and Where Calculations Occur
- Using BQL in Birst Spaces
- Using BQL in Dashboards and Reports
- Using BQL in ETL Scripts for Transforming Data
- Syntax References
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".
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.
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.
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.
You can use BQL expressions
for sophisticated 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.
For Visualizer tables you can use BQL expressions to specify conditional formatting.
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.
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.
BQL lets you pass filter (prompt) values to expressions.
Designer Expression Builder lets you write scripts that support advanced data manipulation at the report level.
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.
Syntax References
To learn about the details of BQL queries, expressions, and ETL scripts, see:
- BQL Basics
- Advanced BQL
- Advanced BQL Functions
- BQL Quick Reference
- Formula Reference for Custom Attributes and Measures
- ETL Reference