Adding a Live Access Query-based Source

You have the option to add a "query-based object" as the source for a Live Access table definition. 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.

As an example, two views are created using the add query based object feature. In one case, a query based object is created from a subset or slimmed-down version of an existing database object, and the other query based object is 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.

  1. Select Modeler > Connect . Create a connection. Click the ellipsis (…) button and select Add query-based object.
  2. Specify this information:
    Object Name
    Specify the name of the query-based source.
    Query
    Specify a query.
    After the Live Access query data source has been configured, it can be used in Designer and Visualizer reports.