Modeling a Live Access Relational Database Source

Birst Connect Live Access connects to an existing on-premise relational database and runs queries on that data.

With the 5.27 release, Live Access supports Attribute only queries, Queries with attribute and measures, Nested queries, Queries using filters, sorting, Group By, and Opaque views while connecting to Hive and MongoDB via the Apache Drill driver.

You model Live Access data sources for a relational database on the Data Flow page, set the primary key for the sources, and create joins.

Important: If you use the Data Flow page to map Live Access sources you should not use the Manage Local Sources page to modify them. Conversely, if you have used the Manage Local Sources page to map your Live Access sources, you should continue to use the Manage Local Sources page and should not use the Data Flow page to modify them.

Important: When you create a new automatic mode space and establish a Live Access connection in Birst Connect, the Manage Local Sources - Add button for adding a Live Access source in Data Flow is disabled. Convert the space to Advanced mode to use Data Flow to add Live Access sources.

Prior to creating a Live Access data model for a relational database

Create a new Live Access real time connection in Birst Connect, then minimize Birst Connect.

Note: With the 5.29 release, you can now create Live Access connection using Birst Connect 2.0. After establishing your Live Access connection, you can model Live Access sources as described here.

Important: Infor Connect or the Birst Cloud Agent (if you used Birst Connect 2.0 to connect to your Live Access data sources) must be running.

To model a Live Access relational database source using the Data Flow page

In Birst, click Home to refresh the screen cache. Birst updates the interface to show the new connection.

Select the space in which the Live Access connection was created.

Go to Admin - Define Sources - Data Flow.

Important: When a real-time connection is created, a new link called Manage Local Sources appears under the Define Sources tab. However, you should use the Data Flow page to create and manage Live Access relational database sources.

On the Data Flow tab, click Add - Add New Data Source - Add Live Access Source.

In the Import Table/Cube Definition dialog box, select the connection to use and the type of table source.

For instructions to import a table, continue with this topic.

For instructions to import a compound table, see Creating a Compound Live Access Source.

For instructions to add an opaque view source (query source), see Creating a Live Access Query (Opaque View) Source.

Click Go.
When you select Import Tables, a list of the tables in the source opens.

Select the tables to import.
Tip: Some tables may have the same name in multiple schemas. Be sure to select the correct schema/table to import.

Click Import.
The new sources populates on the Data Flow page display as blue rectangles.
Tip: Select and drag to rearrange the sources on the page.

Verify or set the primary key for each source by right-clicking the source and selecting Set Primary Key.
Tip: Live Access automatically selects a primary key on import.

10.

Create joins between Live Access sources. 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".

11.

Identify Attributes and Measures

After creating a Live Access data model for a relational database

The columns from the Live Access sources you created are available for creating reports.

For information about adding custom columns and expressions to your Live Access sources, see Adding Custom Columns and Custom Expressions to a Live Access Source.

To continue on to create a compound source, see Creating a Compound Live Access Source.

Tip: Right-click a Live Access source and select Manage Sources to see the column properties and Live Access source properties. For example, you can change the table source (table, compound table, or query) in the Properties tab of the Modify Data Source dialog box.