Ontology - Federated Data
Intended audience: END-USERS ANALYSTS DEVELOPERS ADMINISTRATORS
AO Platform: 4.3
Overview
This section contains the details for configuring Federated access to Data Sources for the Ontology.
Federated Data Access allows a single data source connection to a federation environment to query one or more underlying data sources and perform join operations between them. This is particularly powerful when the underlying data sources are in different data source types. The AO Platform supports Federated Data access by integrating with Trino. Trino will connect to other data sources and perform the required data federation before supplying the data to the user interface components as needed. All data sources connected to Trino will appear in Trino as Catalogs.
The following diagrams show how a federated connection can retrieve data from multiple MSOs in an Ontology with connection to different data source types.
A simple illustration of how questions in Easy Answers get orchestrated and processed by the Federated Data Server, Trino, in a Federated Data configuration.
An example set of MSOs in an AO Platform ontology where each MSO is connected to different data sources, and some MSOs even have properties configured with different data sources.
The Federated Data page in the Ontology Composer provides all configuration options for establishing a Federated Data setup for an Ontology. There are five key configuration steps:
General - enabling, configuration, and testing of the Federation Server.
Cached MSOs - configuration of any high-latency, large data volume MSOs as Cached MSOs.
Materialized Views - configuration of advanced federated data queries as persisted Materialized Views.
Runtime Views - automatic storing of Runtime Views from federated data queries for faster retrieval during subsequent requests.
Data Load Jobs - configuration of scheduled data load/refresh jobs.
General
This page includes selecting the Federated Server environment and the Federated Server’s connections to the data sources (Catalogs) to be federated. It also has some additional cost-optimization “guardrails” in place to help the user understand how the server will be handling requests for questions in Easy Answers that may take a long time to execute.
Properties
Label | UI Widget | Default | Description |
|---|---|---|---|
Enable Federation | ON/OFF Toggle | OFF | If enabled, it will allow configuration of the Federation Server and its connections (Catalogs) to connected database data sources. |
Federation Server | Dropdown | Select from available Federation server connections (configured in the Admin solution). | |
Test Connection | Button | Click to test the selected Federation Server connection. | |
GUARDRAILS | Properties defining Cost Optimizer Scores | ||
| Number Field (read-only) | 0.35 | This parameter controls how Easy Answers questions are transformed and executed against a Federation Server, depending on an estimation of the duration for the results to be generated. For estimation scores below the Min value configured, questions will be executed as normal. Questions with an estimation score above the Min value provided but below the Max score will show a message to the user about the query taking a long time to execute. The query will run in the background, and the user will receive a Notification when the Results are ready. |
| Number Field (read-only) | 0.67 | For estimation scores above the Max value configured, questions will not be executed, and a message will be shown. The user should add one or more Filters to the Question to reduce the data needed to respond to the Question. |
CATALOGS | Repeater section | ||
| Text Field (read-only) | OFF | List of active database data sources on the Ontology > Sourcing page. |
| Text Field | Enter a meaningful name for the database data source used for the Catalog Name. | |
| Radio-button | Not Selected | Select the radio button for the Primary Catalog. |
| Text Field (read-only) | Not Registered | A Status informing the user whether the Catalog has been registered in the Federation Server or not. |
| Button | Register | Buttons allow the user to manually register (or unregister if no longer required) a Catalog in the Federation Server. Note: Any Catalogs configured, but not yet registered, will be registered on Save of the Ontology. |
Message to Easy Answers Users if the Guardrails score for a Federated Data Question is between Min and Max | Message to Easy Answers Users if the Guardrails score for a Federated Data Question is above Max |
|---|---|
  |  |
Cached MSOs
This page includes the configuration for one or more Cached MSOs that can be created to persist data retrieved from the federated environment using either Build the Query or Advanced mode. In other words, it’s a way to eliminate long-running processes retrieving data in a federated environment in a live production environment, but instead loading data from one or more MSOs into Cached MSOs that can then be accessed “locally” to the AO Platform. Such Cached MSOs can be refreshed both manually and on schedule.
Properties
Label | UI Widget | Default | Description |
|---|---|---|---|
Cached MSOs | Repeater section | ||
| Icon | Click to create a new Cached MSO. | |
| Read-Only Text Field | Displays the name for the configuration of a Cached MSO. | |
| Icon | Click to delete a configured Cached MSO. | |
Cached MSO Details | |||
| Button | Click to display the data from the Cached MSO. | |
| Button | Click to run the process that loads data from the original MSO into the Cached MSO. | |
| Text Field w/Search | Enter the Name of the Cached MSO that will be created. | |
AUDITING | |||
| Read-Only Text Field | <empty> | Displays the user name of the user who created the Cached MSO. |
| Read-Only Timestamp Field | <empty> | Displays the timestamp for when the Cached MSO was created. |
| Read-Only Text Field | <empty> | Displays the user name of the user who modified the Cached MSO. |
| Read-Only Timestamp Field | <empty> | Displays the timestamp for when the Cached MSO was modified. |
STATUS | |||
| Read-Only Text Field | <empty> | Displays the most recent Action performed. Options include: Cached MSO Refreshed, Cached MSO Dropped. The Field will be empty if the Last Action Status is Not Started. |
| Read-Only Text Field | Not Started | Displays the status from the Cached MSO process. Options include: Not Started (black), In Progress, Completed, Failed. |
| Read-Only Timestamp Field | <empty> | Displays the timestamp for when the Cached MSO was most recently refreshed. The field will be empty if the Last Action Status is Not Started. |
| Read-Only Text Field | <empty> | Displays the user name of the user who started the most recent process. The field will be empty if the Last Action Status is Not Started. |
| Read-Only Number Field | <empty> | Displays the number of records loaded into the Cached MSO. If Last Action Status resulted in Failed or Not Started, the field will be empty. |
Materialized Views
This page includes the configuration for one or more Materialized Views that can be created to persist data retrieved from the federated environment using either Build the Query or Advanced mode. In other words, it’s a way to eliminate long-running processes retrieving data in a federated environment in a live production environment, but instead loading data from a federated data query into a persisted view of the data that can then be accessed “locally” to the AO Platform. Such Materialized Views can be refreshed both manually and on schedule.
Data - using SQL | Data - using Build the Query |
|---|---|
 |  |
Properties
Label | UI Widget | Default | Description |
|---|---|---|---|
Materialized Views | Repeater section | ||
| Icon | Click to create a new Materialized View | |
| Read-Only Text Field | Displays the name for the configuration of a Materialized View. | |
| Icon | Click to delete a configured Materialized View. | |
View Details | |||
| Button | Click to run the process that creates the Materialized View and populates the view with the data extracted from the Federation Server. | |
| Button | Click to run the process that drops the Materialized View, including data if populated, from the data source. | |
| Text Field | Enter the Name of the Materialized View that will be created. | |
| Multi-Line Text Field | Enter a short Description of the Materialized View. | |
AUDIT | |||
| Read-Only Text Field | <empty> | Displayss the user name of the user who created the Materialized View. |
| Read-Only Timestamp Field | <empty> | Displays the timestamp for when the Materialized View was created. |
| Read-Only Text Field | <empty> | Displays the user name of the user who modified the Materialized View. |
| Read-Only Timestamp Field | <empty> | Displays the timestamp for when the Materialized View was modified. |
STATUS | |||
| Read-Only Text Field | <empty> | Displays the most recent Action performed. Options include: View Refreshed, View Dropped. The Field will be empty if the Last Action Status is Not Started. |
| Read-Only Text Field | Not Started | Displays the status from the Materialized View process. Options include: Not Started (black), In Progress, Completed, Failed. |
| Read-Only Timestamp Field | <empty> | Displays the timestamp for when the Materialized View was most recently refreshed. The field will be empty if the Last Action Status is Not Started. |
| Read-Only Text Field | <empty> | Displays the user name of the user who started the most recent process. The field will be empty if the Last Action Status is Not Started. |
| Read-Only Number Field | <empty> | Displays the number of records loaded into the Materialized View. If Last Action Status resulted in Failed or Not Started, the field will be empty. |
DATA | This section is used to create the Federated Data Query to retrieve data from the Federation Server, and to populate the Materialized View. Use either Build a Query or the Advanced mode to create the query. | ||
| Radio-button | Default | This option is the interactive way of building a query by selecting an MSO, Properties, and configuring Criteria (optional). |
…MSO | Text Field w/Search | Enter the primary MSO for which the Federated Data Query shall be based. | |
…Property | Dropdown w/Expression and Additional Properties | A Repeater section for entering one or more MSO Properties for which the Federated Data Query shall be based, - including Properties for Connected MSOs. | |
…Criteria | Text Field w/Query Builder | Opens a Query Builder dialog to allow entering one or more filters to be used as a data filter for the Federated Data Query. | |
| Radio-button | This is an Advanced option for power users entering a query in the SQL language. | |
…MSO Query | Multi-Line Text Field | Enter or paste an entire MSO Query SQL into a multi-line text box. | |
| Button | Opens a dialog showing the data retrieved from the Federated Data Query. | |
| Button | Opens a dialog showing the Trino-specific SQL used to retrieve data based on the Federated Data Query. | |
| Button | Opens a dialog showing the MSO Query SQL used to retrieve data from the Federated Server. |
Runtime Views
Runtime Views are automatically created for federated data queries that are considered “long-running” queries, ie, those queries that have a computed “guardrail score” between Min and Max (on the General page). The limit of Runtime Views created will be determined by the property at the top of the Runtime Views page (default: 100). Once a Runtime View has been auto-created, subsequent questions in Easy Answers that makes use of the same MSOs from which the Runtime View was created, will automatically retrieve data from the Runtime View instead, and thereby execute with improved performance.
Properties
Label | UI Widget | Default | Description |
|---|---|---|---|
| Number Field | 100 | Enter the number for the maximum number of Runtime Views to be stored. |
| Button | Click to delete selected Runtime Views. | |
View Details | |||
| Checkbox | Unchecked | Check the checkbox to select a Runtime View. Used if multiple Runtime Views are to be deleted. |
| Read-Only Text Field | Displays the Name of the Runtime View. | |
| Read-Only Text Field | Displays the MSO Query for the Runtime View. | |
| Read-Only Text Field | Display the Status of the Runtime View. | |
| Read-Only Number Field | Displays the number of records stored in the Runtime View. | |
| Read-Only Text Field | Displays the user name of the user who created the Runtime View. | |
| Read-Only Timestamp | Displays the timestamp for when the Runtime View was created. |
Data Load Jobs
This page provides status monitoring of any scheduled, longer-running tasks relating to the federated data and materialized view configurations, and it allows scheduled tasks to be created.
Status tab | Scheduled Tasks tab |
|---|---|
 |  |
History dialog | New Scheduled Task dialog |
 |  |
Properties
Label | UI Widget | Default | Description |
|---|---|---|---|
Tab: Status | |||
| ON/OFF toggle | ON | If enabled, the entries on the Status tab will automatically refresh every 10 seconds. |
| Enter a search term to find specific View Names. | ||
| Click to manually refresh all entries on the page to get the latest status. | ||
Table with Status | All read-only | ||
| Displays the most recent status of an executed task. There are three types of Status buttons that can appear in this column: COMPLETED RUNNING and FAILED | ||
| Displays the name of the materialized view that the task relates to. | ||
| Displays the short description for the scheduled task. | ||
| Displays the timestamp for when the task will be executed next. | ||
| Displays the duration for the most recent execution of the task. | ||
| Displays the user name of the person who initiated the most recent execution of the task. | ||
| Displays the amount of records that were loaded into the materialized view from a federated data query. | ||
| Displays the timestamp for the most recent execution of the task. | ||
Options menu | Available for each entry in the Status table. | ||
| Select to refresh the specific entry for which the Options menu is selected. | ||
| Select to view the log file content in a separate dialog for the specific entry for which the Options menu is selected. | ||
| Select to view the history in a separate dialog for the specific entry for which the Options menu is selected. | ||
Tab: Scheduled Tasks | |||
| Click to open the New Scheduled Task dialog allowing the user to configure a scheduled task for the loading of data into a materialized view. | ||
Table of Scheduled Tasks | All read-only | ||
| Displays the name of the materialized view that the task relates to. | ||
| Displays the short description for the scheduled task. | ||
| Displays the timestamp for the most recent execution of the task. | ||
| Displays the timestamp for when the task will be executed next. | ||
| Displays a short description of the recurrence schedule. | ||
| Displays the user name of the person who created the scheduled task. | ||
| Displays the timestamp for when the scheduled task was created. | ||
Options menu | |||
| Select to run the task manually, outside the schedule. | ||
| Select to view the history in a separate dialog for the specific entry for which the Options menu is selected. | ||
| Select to enable/disable a scheduled task. | ||
| Select to open the Scheduled Task dialog to edit existing scheduled properties. | ||
| Select to delete the scheduled task. |
Notifications
Notifications are used to alert users about the progress in creating, loading, and dropping materialized views relative to the Federated Data configurations. They appear as both desktop notifications as well as from the Bell icon in the header of Ontology Composer.
Using Ontology Discovery on a Materialized View
Once one or more materialized views have been created from data retrieved from a federated environment, they need to be added to the Ontology in the same way as other data sources are added, ie, using the Discover Ontology wizard. See Discovering an Ontology.
Select the Trino Data Source Type on the Data Source Type page in the wizard, and follow the steps to create MSOs from Materialized Views for Federated Data. The Materialized Views will be in the schema: aodatalake.
Limitations and Other Considerations When Using Federated Data Configurations
GEOM data type
MSOs representing data source tables that contain spatial data in the GEOM format cannot be cached (as a Cached MSO) or included in Materialized Views. The GEOM data type is not currently supported by the technology that is used by Trino to persist Cached MSOs and Materialized Views. Therefore, such MSOs with GEOM data types should retrieve data directly from their original data source via Trino.
AI Response Table
Queries being executed against Federated Data Sources directly will generate output as AI Response Tables in Easy Answers solutions. This is caused by the fact that statistics have not been generated on the data being retrieved from multiple data sources, and therefore, curations, such as charts, maps, and other visualizations, cannot be auto-generated. See AI Response - Table. To avoid the AI Response Tables in response to Federated Data questions, consider using Cached MSOs and Materialized Views.
Supported data sources that can be used with Federated Data configurations
Although the Federated Data server technology, Trino, supports a great number of data sources with its plugin connectors, the AO Platform 4.3 release has been tested with the following:
Snowflake
Google BigQuery
SAP HANA
PostgreSQL
Which Trino version is supported
4.5.1
Slides…
