Live Access Query Sources (Opaque Views)

This topic pertains to Live Access RDBMS connections.

Sometimes the source for a table definition may not be defined using a join relationship among existing tables. You may want to use a physical query as the source for a table definition instead. This option is available for Live Access sources; it is referred to as an Opaque view or a Query source. It is opaque because Birst does not know anything about the underlying constituents of the view (its tables, columns, etc.). Instead of importing one or more physical tables into a table definition, you can add an Opaque view source, then manually enter the SQL query that will define the opaque view. After a Live Access source has been created, you can also select the Query radio button in the Properties tab of the Live Access source (as shown below) to mark the source as a Query (opaque) source and provide the SQL query that will define the opaque view.

See Creating a Live Access Query (Opaque View) Source for step-by-step instructions on creating an opaque Live Access source.

Examples where opaque views are useful include:

Opaque views can help hide complexities that exist in the model. The need to create a source by joining multiple tables or including complex calculations can be obscured behind an opaque view.
Snowflake data models can be simplified with opaque views by combining multiple tables into one data source. This is useful in cases where creating a data source for each physical table would result in too many levels of snowflaking.
Opaque views allow you to make changes in the data model without breaking existing reports and dashboards. This is because downstream dependencies such as reports and dashboards rely on the logical naming of columns from the opaque view and not the physical columns found in the data model.

You can access the Modify Data Source dialog box by right-clicking the Live Access source on the Data Flow page and selecting Manage Sources.

Example 1

For example, an alternative to defining separate data sources for the Orders and Order Details tables is to define a single source as a Query table source (Opaque view):

select * from orders, orderdetails where orders.OrderID=orderdetails.OrderID

Example 2

Opaque views can be particularly powerful when they include embedded logical query (BQL) syntax. Complex, multi-pass calculations can be done in this case. For example, if the measure $ Average Unit price were defined elsewhere, you could use an Opaque view that integrates existing product category information with the average price for a product in the category:

SELECT * from categories A, Q{Select [Product.Category Name] ‘CNAME’, [$ Average Unit Price] FROM [All]} B WHERE A.CategoryName=B.CNAME

The logical query is indicated by the use of the query operator Q{}.

Display calculations like expressions and positional calculations are not available in embedded logical queries as the entire table source needs to be sent to the underlying database which must process the request. These elements will be ignored.

Example 3

In order to join to another table in the opaque view query, use a full physical SQL statement that includes inner and outer joins, for example:

SELECT O.OrderID OrderID,S.CompanyName CompanyName,S.Phone Phone FROM Orders O LEFT OUTER JOIN Shippers S ON O.ShipVia = S.ShipperID

Example 4

It is also possible to reference Birst variables in the query of an opaque view (Query) source, using the V{VariableName} syntax. For example:

SELECT O.OrderID OrderID,S.CompanyName CompanyName,S.Phone Phone FROM Orders O LEFT OUTER JOIN Shippers S ON O.ShipVia = S.ShipperID WHERE O.ShipCountry = V{DefaultCountry}