Using L7 Intelligence (IMB)
Summary
Use the L7|INTELLIGENCE Manager to browse, download and schedule exports for all of your ESP data. You can also create and update the data views required for this.
Available Collections
When the bundle is first installed, it automatically creates the Export Tables (ITDs) by default, so they are ready to be used on the system right away, without further user action. The existing Collections you can use are:
Export Tables: also known as "ITDs" (Intelligence Table Definitions), they collectively compose a normalized and simplified model of ESP's database schema. You can use the ITDs to export all of ESP's data, and they reflect commonly used artifacts in ESP's UI.
Entity Type Pivots: these pivot views allow you to see entities data for all entities within each Entity Type as tables. For large datasets, there's the option of materializing these views, which is a database provided option for "snapshotting" the data for quicker retrieval when querying.
Protocol Pivots: these pivot views allow you to see all protocol data within Protocol as tables. For large datasets, there's the option of materializing these views, which is a database provided option for "snapshotting" the data for quicker retrieval when querying.
Master Matrices: this pivot view allows you to see all of protocols data for all of the entities in your system. Depending on your system and configuration, this may represent a largely sparse array of values. This matrix can be useful for Data Analysis and Machine Learning, but should be used with care as they may be costly to generate.
“Snapshots” option
For Entity Type Pivots, Protocol Pivots and Master Matrices, there's the option of materializing the views, which "snapshots" the data for faster retrieval when querying. This can be useful for large datasets to speed up data retrieval in exchange for data storage, as the materialization requires data to be stored in the database.
User Guide
Create
View Collections can be created on this tab.
Export Tables (ITDs): these views are automatically created when the bundle is installed, but can be re-created here. This can be useful if the views were modified after bundle install.
Entity Types, Protocol Pivots and Master Matrices: creating these pivot views will update them to the latest definition of the Entity Types or Protocols in L7|MASTER.
Optionally, Entity Type and Protocol Pivots can be materialized. Note that when materialized, the views will not contain the latest data in L7|MASTER, but a snapshot of the data when the views were last created.
Browse
Data for any views can be browsed on this tab.
Export Tables (ITDs): the list of ITDs will be fixed, and are documented later on this guide.
Entity Types, Protocol Pivots: the number of views displayed here depends on the number of Entity Types and Protocols defined in L7|MASTER. Entity Type Pivots have a
vcte_prefix. Protocol Pivots have avctp_prefix.Note: for non-materialized views, the app will check for the latest definition in L7|MASTER and will automatically update the view if the definition was updated since the view was created, and issue a notification accordingly.
Master Matrices: the number of views here is fixed.
You can export any of the opened views in .csv format at any time.
Schedule
Views creation and data extraction can be configured on this tab.
You can select the frequency of creation and extraction, and select which collections to update.
The Auto Export CSV option will export all the data for the selected collection into .csv files, in the specified folder (Output Path).
The Materialize View option will create a materialized view snapshot in the database for the selected collection.
Note: you need to make sure the server volume that hosts the Output Path folder has enough storage space to hold the data for the exported collections.
Ideally, this should be a mounted volume, so that the main server volume storage is not compromised by these data extracts.
Configuration
Bundle Version Checker
Parameter | Type | Options | Comments |
|---|---|---|---|
L7|HUB Location ID | int | 1 | L7|HUB Location ID for checking for new versions of the bundle. This refers to your L7|HUB location id (see |
Consuming Data
There are a couple of ways to consume the data that L7|INTELLIGENCE offers, besides Browse and Schedule:
API: for each ITD, there's a corresponding Custom Query, that allows downloading the data through an API call:
/api/v2/queries/[ITD name]?limit=[max rows]&offset=[starting row]Where limit and offset can be used to paginate the result sets for large datasets. For instance:
/api/v2/v_exp_entities?limit=10&offset=20will return entity instances from row 20 to 30.
Direct database queries: given all the ITDs and pivots are created as views in the Postgres database, they can be queried from a database client, for instance
select * from entities;will return all entity instances in the database.
Technical Considerations
Prerequisites
ESP Version 3.3
ESP Analysis Pipelines: tasks must be configured to execute, as the creation of views and data extraction depend on them.
Installation
To install the bundle, follow L7|HUB instructions for bundle installation. Bundles can be installed from configured remote locations or directly from uploaded local files. The L7|INTELLIGENGE bundle will automatically create the Export Tables (ITDs) views in the database during installation. So they will be available and ready to be used right after bundle installation with no further user action required.
Creation of Views
When creating views, Postgres view objects are created in the database.
Export Tables (ITDs): these views are created during bundle install, so the creation is not necessary to have these views in the database. Creating these views can be useful if the views were modified by another process or user and need to be set back to their initial bundle state.
Pivots: for both Entity and Protocol Pivots, creating the views will create pivot query views in the database, based on their latest L7|MASTER definitions. You can re-create the views to get them updated to their latest definition at any time. Note that when browsing the data of pivots the system will detect if the views are not up-to-date with their latest definitions and if so will re-create them automatically, with a notification of this update.
Data Security
Given Export Tables (ITDs) were designed for exporting all of ESP's data, using them out-of-the-box for in-ESP Operational Reports is not recommended, given these views do not consider the security context of the user, that is, the IAM User, Role(s) and Workgroup(s) of the ESP logged-in user executing them.
The views can be used as the basis for Operational Report queries, as long as the user security context is added to the SQL of the query.
ITD Reference
This is the list of ITDs, grouped by functional areas.
L7|Master | |
Entity Classes (v_exp_entity_classes) | Entity Classes as defined in L7|MASTER. |
Entity Types (v_exp_entity_types) | Entity Types as defined in L7|MASTER. |
Entity Type Vars (v_exp_entity_type_vars) | Entity Type variables, "Custom Fields" as defined in L7|MASTER. |
Workflowchains (v_exp_workflowchains) | Workflow Chains as defined in L7|MASTER. |
Workflowchain Workflows (v_exp_workflowchain_workflows) | Association of Workflow Chains and Workflows- reflects what Workflows -> Used In tab shows |
Workflows (v_exp_workflows) | Workflows as defined in L7|MASTER. |
Workflow Protocols (v_exp_workflow_protocols) | Association of Workflows and Protocols - reflects what Protocols -> Used In tab shows |
Protocols (v_exp_protocols) | Protocols as defined in L7|MASTER. |
Protocol Vars (v_exp_protocol_vars) | Protocol variables, "Custom Fields" as defined in L7|MASTER. |
Dynamic Execution Plans (v_exp_dynamic_execution_plans) | Dynamic Execution Plans as defined in L7|MASTER. |
Dynamic Execution Plan Values (v_exp_dynamic_execution_plan_values) | Dynamic Execution Plan value Overrides as defined in L7|MASTER. |
Entities (v_exp_entities) | Entities as viewed in the Entities app. |
Entity Vals (v_exp_entity_vals) | Entity instance values as viewed when opening an entity in the Entities app. |
Containers (v_exp_containers) | Containers and their contents as viewed when listing them on the Location app. |
Container Contents (v_exp_container_contents) | Containers and their contents as viewed when listing them on the Location app. Will only show containers that are not empty, i.e. that contain at least one item. |
Container Joins (v_exp_container_tree) | Containers and their contentsas viewed when listing them on the Location app (deprecated, use Container Contents instead) |
Dependencies (v_exp_dependencies) | Entity dependencies as viewed in the Parent and Children tab when opening an entity. |
Projects (v_exp_projects) | Projects as viewed in the Project app. |
Experiments (v_exp_experiments) | Experiments as viewed in the Project app. |
Protocol Instance (v_exp_protocol_instance) | Protocol instance data as viewed in the LIMS app landing page. |
Worksheets (v_exp_worksheets) | Worksheets data as viewed in the LIMS app landing page. |
Protocol Vals (v_exp_protocol_vals) | Protocol values data as viewed in the LIMS app worksheets. |
Protocol Instance Sample Map (v_exp_protocol_instance_sample_map) | Protocol isntance to sample relationships as viewed in the LIMS app worksheets. |
Protocol Vars Audit Trail (v_exp_var_audit_trail_by_protocol_var) | Protocol vals audit trail as viewed in the History tab of any sample in the Samples app. |
Worksheet values (v_exp_worksheet_values) | Worksheet values as viewed in the LIMS app worksheets. |
Ingest Definitions and Status (v_exp_ingest_def_and_status) | Ingest definitions and their status as viewed in the Ingest app. |
Ingest Template Definitions (v_exp_ingest_definition) | Ingest template definitions as viewed when opening them in the Ingest app. |
Ingest to Samples (v_exp_ingest_entities_info) | List of entities created from ingests, as viewed when opening a completed ingest in the Ingest app in the Created Entities tab, along with their Entity Types. |
Ingest Instances (v_exp_ingest_instance) | List of ingest instances as viewed when opening the Ingest app. |
Ingest Instance Entity (v_exp_ingest_instance_entity) | List of ingest instances and their created entities as viewed when opening a completed ingest in the Ingest app in the Created Entities tab. |
Ingest Joins to Sample (v_exp_ingest_to_sample) | List of ingest instances and their created entities as viewed when opening a completed ingest in the Ingest app in the Created Entities tab (deprecated, replaced by Ingest Instance Entity) |
Ingest Strategies (v_exp_ingest_strategy) | List of ingests and their field mappings as viewed when opening a Not started ingest in the Ingest app. |
Ingest Joins to Sample Type Definitions (v_exp_ingest_to_sample_type_def) | List of Entity Types and their Entity classes for samples created by ingests in the Ingest app. |
Availability Schedules (v_exp_availability_schedules) | List of Availability Schedule as viewed in the Scheduling app. |
Schedule Breaks (v_exp_schedule_breaks) | List of Schedule Breaks as defined for each Schedule as viewed in the Scheduling app. |
Schedule Shifts (v_exp_schedule_shifts) | List of Schedule Shifts as defined for each Schedule as viewed in the Scheduling app. |
Availability Blocks (v_exp_availability_blocks) | List of Availability Blocks for all resources as defined by the Availability Schedules as viewed in the Scheduling app. |
Resource Assignments (v_exp_resource_assignments) | List of Resource Assignments for Availability Blocks as viewed in the Scheduling app. |
Planned Actions (v_exp_planned_actions) | List of Planned Actions for all resources as defined by Tasks created in the Calendar in the Scheduling app. |
Planned Action Chains (v_exp_planned_action_chains) | List of Planned Actions for Workflow Chains as defined by Tasks for Workflow Chains created in the Calendar in the Scheduling app. |
Planned Action Types (v_exp_planned_action_types) | List of Planned Actions Types as defined by Tasks created in the Calendar in the Scheduling app. |
Notebooks (v_exp_notebook) | List of all Notebooks, Notebook Entries and contained Widgets as viewed in the Notebooks app. |
Users (v_exp_users) | List of Users as defined in the IAM app. |
Roles (v_exp_roles) | List of Roles as defined in the IAM app. |
Workgroups (v_exp_workgroups) | List of Workgroups as defined in the IAM app. |
As Built Details (v_exp_as_built) | List of all Entity Types and their Entity Types Vars, i.e. "Custom Fields", in a format suitable for LucidChart PostgreSQL ERD importing |
LucidChart Import (v_exp_entity_type_vars_lucid) | List of all Entity Types and their Entity Types Vars, i.e. "Custom Fields", in a format suitable for LucidChart SQL Server ERD importing (deprecated, replaced by "As Built Details") |
Joined Workflows (v_exp_joined_wfs) | List of associated Workflow Chains and Workflows as defined in L7|MASTER |
Additional Reference
The following resources are available for download from within the L7 INTELLIGENCE bundle User Guide.