Creating a Live Access Opaque View (Query) Source

This topic pertains to relational databases.

In some cases you may want to use a physical query as the source for a Live Access table definition. This is also called an "opaque view", because Birst does not know anything about the underlying components (tables, columns, etc.) of the view. Instead of importing one or more physical tables into a table definition, you add an opaque view source, then manually enter the SQL query that will define the opaque view.

See About Live Access Query Sources (Opaque Views).

Prior to creating a Live Access query source

Create a Live Access real-time connection in Birst Connect.

Add a Live Access source on the Data Flow page as described in Modeling a Live Access Relational Database Source.

To create a Live Access query source

In the Import Table/Cube Definition dialog box, select the connection to use then select Add an Opaque View Source.

Type a name for the source then click Go.

The new source will be appear on the Data Flow page as in the following example:

Right-click the source and select Manage Sources.

Click the Properties tab.

Enter the query to use for this source in the Query text box. The query must be a physical SQL query, although a logical (BQL) query can be embedded in the physical query. The example below uses a physical SQL statement to join to another table. See About Live Access Query Sources (Opaque Views) for additional examples.

Important: The query should not contain any tabs or new lines.

After entering the query, add the columns to that data source. Click the Columns tab then click Add Column. Give each column a name and map it to a column from the query by putting the query column alias into the Formula field.

When you are finished, click Save then click Close.

Set the primary key for each source by right-clicking the source and selecting Set Primary Key.

10.

Create joins between Live Access sources as necessary. To create a join between tables that have the same column but use different column names, you can do a complex join to a related source.

Tips:

You can create joins only between Live Access sources. Joins cannot be created between warehouse dimensions or measures and Live Access sources.

Joins cannot be created between Live Access sources and raw data sources.

Column names in PostgreSQL databases are case-sensitive. You need to use quoted column names when defining joins. For example: "Categories-New Level"."CategoryID"="Products-New Level"."CategoryID"

After creating a Live Access query source

After the Live Access query data source has been configured it can be used in Designer or Visualizer reports.

You can add custom columns and expressions to your Live Access sources. See Adding Custom Columns and Custom Expressions to a Live Access Source.