Create Registered Table objects

Background and Strategy

In Ursa Studio, most object types need to be run (i.e., executed as part of an ELT) to take effect; running the object creates or updates a database table or view.

Registered Tables are an exception. Because the underlying table already exists, there is no need to run anything; these objects become usable – that is, users can view their contents in Case Review, link to them in a downstream object or measure, etc. – as soon as they are saved.

Relatedly, there isn’t much to configure when creating a Registered Table object. Beyond identifying the object name, only the schema and name of the table must be provided. (Note that, like all other object in Ursa Studio, Registered Tables cannot be associated with a table or view that is already in use by another object.)

When the schema and table name are entered in the Registered Table screen, Ursa Studio attempts to find the table and retrieve its metadata. If the table exists (and the Ursa Studio database account has permission to access it) a checkmark will appear next to the Table Name control, and the columns, with data type, will be displayed in the Object Metadata panel.

Key Diagnostics / Heuristics

1. Are any elements of source data available as physical tables or views in the client database? If so, use a Registered Table to access their contents in Ursa Studio. (In contrast, source data in flat files must use Import objects.)

Detailed Implementation Guidance

1. You can create a new Registered Table object by navigating to the Object Workshop zone, clicking the Create new data object icon in the upper right, and selecting Registered Table.

2. Tables in any schema in the database can be registered as long as the Ursa Studio service account has been granted the appropriate permissions. (Note that Ursa Studio has no way to distinguish between a table that does not exist and a table it does not have permission to read from, and so there is no differentiated error message that will identify the absence of appropriate permissions as the issue.)

3. There is currently no way in Ursa Studio to list the names of all the tables in the database available to be registered, so you must know the schema and name of the table you want to register.

4. To ensure uniqueness of object names across the Ursa Studio instance, it is a best practice to include the Source ID of the data source in the object name of each Registered Table.

5. To view more than the first 250 records of a Registered Table object in Case Review, a primary key field – or any field with a unique value for each record – must be designated in the Object Metadata panel of the object. (Designating this field will not actually add a primary key to the table; it will simply facilitate the pagination logic in Case Review.)

Examples

Example 1: Registering a table in a different schema

HealthCo has made an extract of production data from its consumer-facing "PatientLink" available as a set of four tables in the pl schema of the same database used by Ursa Studio. (Tables and views created by Ursa Studio always use the ursa schema.) The names of the four extracted PatientLink tables are:

pl.user
pl.contact
pl.appointment
pl.staff

To start to integrate the PatientLink data, five Registered Table objects are created, all in the HC-PL namespace, and named the following

Source PL User
Source PL Contact
Source PL Appointment
Source PL Staff

The (bare-bones) Source PL User object configuration is as follows:

Object Namespace: HC-PL
Object Name: Source PL User
Object Description: One record per user in the PatientLink web portal database.
Table name: pl.user
Case ID: userid