Introduction
The entities stream offers detailed content relating to IFI standardized names and corporate hierarchies (ultimate owners and subsidiaries). This stream allows users to quickly build full corporate portfolios, retrieve all the subsidiaries for a specific company, and gain quick statistical insights into a portfolio more easily. The optional statistical analysis tables give insights for key metrics such as number of active patent families, number of granted patents, and more. Before beginning, it is important to review the design of the schema, tables and columns as well as the built-in functions.
Prerequisites
Before executing any of the functions described below, it's important to stop all stream updates:
systemctl stop apgupd.service
Linking Entities to Publications
Although the entities stream is self-contained (has no direct dependencies on other stream content), the optional table linking to the xml schema via entities.t_publication_ifi_numbers can only be exposed by building these using the function entities.f_update_xml_info_bf().
To populate the optional content:
select entities.f_update_xml_info_bf();
This function builds the links between the xml.t_ifi_integrated_content data and the entities stream which includes publications, publication status, family and family status information. These links will be maintained by apgupd every entities stream update.
Generating and Maintaining Statistics
Linking entities to publications is an in-line process and is triggered by loads into the XML schema. The optional statistics in entities.t_entity_hierarchy_counts, on the other hand, are generated by an offline process that can be scheduled. As with populating entities.t_publication_ifi_numbers, an initial population of the table entities.t_entity_hierarchy_counts is done by executing a function:
select entities.f_recalculate_all_entity_hierarchy_counts(true)
With the true parameter, the table entities.t_entity_hierarchy_counts will be completely rebuilt, false and only load-ids that haven't been processed will be refreshed. IFI CLAIMS Primary scheduling uses cron as shown below:
# Incremental updates: 3:30 Mon-Sat 30 3 * * 1-6 psql -U alexandria -h localhost -p 5432 -d alexandria -qt -c "select entities.f_recalculate_all_entity_hierarchy_counts()" -o /dev/null # Full refresh: Sat 8:30 30 8 * * 6 psql -U alexandria -h localhost -p 5432 -d alexandria -qt -c 'select entities.f_recalculate_all_entity_hierarchy_counts(true)' -o /dev/null
Getting Started
To ease into the entities content, we'll go over some fundamental functionality:
- Retrieving an ultimate owner from an IFI standardized name
- Retrieving a list of subsidiaries from an IFI standardized name
- Retrieving ticker and exchange information for an IFI standardized name
- Expanding functionality
Retrieving an Ultimate Owner
In its simplest form, given any IFI standardized name that is a member of an entity hierarchy, its ultimate owner can be found by simply executing the following SQL query:
select * from entities.f_cdws_ultimate( 'Google LLC' ); -[ RECORD 1 ]-------+----------------------------------------------------------- entity_id | 3898 country | US name | Alphabet Inc name_type | N extract_status | V updated | 2020-11-16 parent_relationship | O address | 1600 Amphitheatre Parkway, Mountain View, CA, 94043, US attributes | <attributes />
Retrieving a List of Subsidiaries
select name, country from entities.f_cdws_subsidiaries( 'Alphabet Inc' ); name | country --------------------------------------------+--------- Admob Inc | US Adometry Inc | US Adscape Media Canada Inc | CA Adscape Media Inc | US Agnilux Inc | US Anvato Inc | US Apigee Corp | US Apigee Technologies India Pvt Ltd | IN -- List abbreviated
Retrieving Ticker and Exchange
select * from entities.f_attributes_ticker( 4366 ); exchange | symbol ----------+-------- XNYS | IBM
Expanding Functionality
Of course, the above functions are rudimentary. One can easily build out the functionality for application-specific use cases. As a simple example, the following query retrieves an ultimate owner from a publication ucid (or publication id).
Entity status
When executing statements against the table entities.t_entity_hierarchy_info it is important to filter out all but validated (V) and reviewed (R) entities using the status column.
with t as (
select a.entity_name
from entities.t_entity_hierarchy_info as a
inner join entities.t_publication_ifi_numbers as b
on ( a.ifi_number=b.ifi_number)
where a.status in ('V', 'R') and b.publication_id = xml.f_ucid2id( 'EP-0700000-B1' )
)
select * from entities.f_cdws_ultimate( ( select x.entity_name from t as x ) );
-[ RECORD 1 ]-------+-----------------------------------------------------------
entity_id | 4366
country | US
name | International Business Machines Corp
name_type | N
extract_status | V
updated | 2021-01-04
parent_relationship | O
address | Old Orchard Road, Armonk, NY, 10504, US
attributes | <attributes />
Understanding Entity Hierarchy Statistical Information
Along with entity information housed in entities.t_entity_hierarchy_info, is the companion hierarchy statistics table entities.t_entity_hierarchy_counts. This table contains summary statistics for entire hierarchies (ultimate owner and all subsidiaries) including hierarchy family statistics. The following table lists the available statistical categories:
| Category (col: count_category) | Description |
|---|---|
| CB | Total number of backward citations |
| CF | Total number of forward citations |
| FSA | Total families with Active status |
| FSL | Total families with Lapsed status |
| FSP | Total families with Pending status |
| FT | Total number of families |
| PLA | Total published applications |
| PLG | Total published grants |
| PLU | Total publications with unknown publication level |
| PSA | Total publications with Active status |
| PSL | Total publications with Lapsed status |
| PSP | Total publications with Pending status |
| PSU | Total publications with unknown status |
| PT | Total publications |
| ST | Total number of subsidiaries |
This information is wrapped into a view that expands the category with a category description:
select * from entities.v_hierarchy_family_statistics where entity_id = entities.f_name2id( 'Alphabet Inc' ) ;
entity_id | count_category | count_category_desc | count_value | modified_load_id
-----------+----------------+-----------------------------------+-------------+------------------
3898 | CB | total_backward_citations | 420962 | 679395
3898 | CF | total_forward_citations | 897547 | 679395
3898 | FSA | total_families_status_active | 22935 | 680151
3898 | FSL | total_families_status_lapsed | 11877 | 680151
3898 | FSP | total_families_status_pending | 2673 | 680151
3898 | FT | total_families | 37485 | 680151
3898 | PLA | total_publevel_application | 79625 | 680151
3898 | PLG | total_publevel_grant | 66325 | 680151
3898 | PLU | total_publevel_unknown | 38821 | 680151
3898 | PSA | total_publications_status_active | 94507 | 680151
3898 | PSL | total_publications_status_lapsed | 34353 | 680151
3898 | PSP | total_publications_status_pending | 17092 | 680151
3898 | PSU | total_publications_status_unknown | 38819 | 680151
3898 | PT | total_publications | 184771 | 680151
3898 | ST | total_subsidiaries | 186 | 680151