You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 3 Current »

Introduction

The entity 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.

Terminology

Note on terminology: In the following blog, the terms IFI standardized name and entity name are synonymous. Generally, an IFI standardized name is an entity if it belongs to an entity hierarchy.

Prerequisites

Although the entity stream is self-contained (has no direct dependencies on other stream content), the optional analysis tables entities.t_entity_hierarchy_counts as well as direct 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(). This function may run for 24 hours or more so it is important to stop all stream updates by pausing or stopping the apgupd service.

systemctl stop apgupd.service

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 entity stream which includes publications, publication status, family and family status information. These links will be maintained by apgupd every entity stream update.

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

Hierarchy Levels

Hierarchies can have multiple levels so it is possible that a subsidiary itself is an owner of other subsidiaries. To this end, if you are querying for an entire hierarchy, the input parameter should be an ultimate owner.

Extended vs Compact

The following SQL output from psql is displayed in both compact and extended format. Extended format is used when the number of columns would make page display unwieldy.

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

Returned Columns

To see the full list of columns returned, issue the \df psql command.

\df entities.f_cdws_subsidiaries

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
CBTotal number of backward citations
CFTotal number of forward citations
FSATotal families with Active status
FSLTotal families with Lapsed status
FSPTotal families with Pending status
FTTotal number of families
PLATotal published applications
PLGTotal published grants
PLUTotal publications with unknown publication level
PSATotal publications with Active status
PSLTotal publications with Lapsed status
PSPTotal publications with Pending status
PSUTotal publications with unknown status
PTTotal publications
STTotal 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

  • No labels