Connecting to Live Access Databases Using Birst Connect

Similar to connecting to a database, you can select the Live Access option to create a logical mapping of data from a database rather than importing the data into the Birst Cloud.

Note: You can map the tables using the Live Access connection, but you cannot prepare the data since it is not in the Birst Cloud.

Caution:

Before Birst can connect to a database, you must download the Birst Cloud Agent to the machine that will access the database. The agent enables Birst to access the database behind its firewall.

The Modeler Relate feature creates joins between mapped sources.

For Birst Enterprise customers, you can use Birst Connect (HTML Admin) to create Live Access connections and model your existing ADR sources in the HTML interface along with joining both LA and ARD sources for an end to end experience.

For instructions on creating a new Live Access connection, see Connecting to Database Data Sources.

Note: Infor Data Lake connections are not supported by Live Access.

After completing your data source connections, you can create the logical data map by clicking the Map Data button. The name of this button includes your connection name. For example, Map MSSQL LA Data.

Live Access objects connect like database objects. After establishing a Live Access connection, you can edit data source connections like database objects.

Live Access Query-based Sources (Opaque Views)

For Live Access RDBMS connections, the source for a table definition is not necessarily defined using a join relationship among existing tables. Alternatively, you may use a physical query as the source for a table definition. This option is available for Live Access sources; it is referred to as a Query source (or previously an Opaque view). It is considered opaque because Birst does not know the underlying constituents of the view (its tables, columns, etc.). Instead of importing physical table(s) into a table definition, you can add a query-based object, then manually enter the SQL query that will define the view.

In addition, after a Live Access source is imported as a catalog object, you can convert it into a Live Access query-based object. Navigate to Modeler > Relate. Select it and open the source properties. On the Source Properties pane, select the Advanced toggle and then the Query tab to specify a query which converts it into a query object.

These are examples of where the query-based sources are useful:

These views can obscure complexities that exist in the model, such as the need to create a source by joining multiple tables or including complex calculations.
These views can simplify snowflake data models 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.
These views allow you to make changes in the data model without breaking existing reports and dashboards. This is pertinent where downstream dependencies such as reports and dashboards rely on the logical naming of columns from the view and not the physical columns found in the data model.

Navigate to Modeler > Connect and select the connection. Click the ellipsis (…) button and select Add query-based object.

Example 1

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

These 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 a query-based source 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 must be sent to the underlying database to process the request. These elements will be ignored.
Example 3

To join to another table in the query-based object, use a full physical SQL statement that includes inner and outer joins, such as:

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 a query-based 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}