Adding Query Based Objects from a Database

Note: For query based objects for Live Access or Extraction, Birst does not support calling stored procedures.

In addition to importing tables from databases, you have the option to add a "query based object" from your database. This option lets you employ simple SQL expressions such as joining tables or creating simplified views of existing tables.

You must provide a unique Query Object name and valid SQL syntax that is compliant with the connected database. Click Save to create the query object.

Note: If you change a column alias for a given query object after importing data, subsequent imports of date create the new alias resulting in both the old and the new columns being available for data prep.

In the following example, two views were created using the add query based object feature. In one case, a query based object was created from a subset or slimmed down version of an existing database object and the other query based object was created by joining two related objects by their foreign key relationship.

Here is the valid SQL syntax used to create the simple join with the Products and Categories table in MSSQL for the above example.

Select Products.CategoryID, Products.ProductName, Categories.CategoryName from Products, Categories

Where: Products.CategoryID = Categories.CategoryID

Similar to the database connections, you can edit the tables and filter rows for desired content.

After editing the table, click Import MSSQL2 Data to import the data base connected sources.

Live Access and Query Objects:

You can also create query objects from a Live Access database source. Birst Connect 2.0 supports data prep preview and reporting for Query Objects that contain Birst Variables.