All Entities (that is, Tables) that are available to be extracted and loaded to SQL Database exist in this area. Entities are prefixed with a module code, for example, APVendor, JCJob.
Entity Maintenance
It is possible to Generate extracts, build SQL tables and run extract data from the Entity Maintenance screen. Extracts can also be CDC enabled and / or CDC disabled in this page by running one of the functions available under “Generate Extract” which is pictured at the bottom right-hand corner of the screen. Once an entity is generated, its corresponding SQL table is built, or CDC is enabled, a tick appears in its column.
Entity Maintenance – columns with ticks
Be aware of any Entities that contain an * in front of the Label. These Entities may not be complete. The * is triggered by either not having a diagram created or a blank description in the Entity (these Entities were automatically generated from the previous ETL solution and have not been reviewed).
Entity Maintenance – incomplete entities
There are two entities, “PTPeople” and “PTPeople-C” that both must be generated since they are UNIONed and the SQL table “PTPeople” is generated based on the result of this UNION. If you don not generate both entities the resulting table in SQL will not be correct and the Reconciliation report will not provide the correct count results.
Columns
For each Entity, columns are automatically added for “last extract”, “last update” (in ERP+) and “when created” (in ERP+). Columns are also automatically created for “join ID fields”. Column names generally follow COINS column names and are unique unless they are the same across multiple tables. Fields prefixed with x are considered custom, all others are standard. It is possible to suppress creation of a field or fields in the SQL table by ticking the Suppress column.
Any change in an entity set up will not replicate in its SQL table unless the entity is re-generated, its SQL table is re-built, and the data should be re-extracted.
Entity Maintenance – columns
Relationships
Relationships get created automatically using the Primary and Foreign keys in each Entity. Custom relationships can be created to join tables to other tables, this can be done based on suggested relationships. To create Custom relationships, the relationship ID must begin with an X.
Entity Summary – Relationships tab
Diagrams
The Diagram tab lists all the relationship diagrams for a given Entity.
Triggers
Triggers for each Entity are defined to link the CDC trigger (Change Data Capture) with the Entity. The source table is essential for this link which is done automatically on creation.
Other Triggers should be defined and linked where other tables or records will affect the field value on this Entity. The Triggers should then be linked to the fields that they affect (this may be required in the future to provide field level data refresh).
The fields required from CDC trigger should match the key field selection criteria for the Entity. Typically, this will be the primary key fields.
Entity Summary – Triggers tab
The following steps must be taken if a new trigger should be added to the existing triggers of a custom entity:
Add new trigger / triggers to the “Triggers” tab of the entity.
Entity Summary – Triggers tab
On the “Detail” tab select the new trigger or triggers by ticking the box and then saving the entity.
Detail tab
Re-generate the entity to link its corresponding extract to all triggers defined for this entity.
Views
Specific views for an Entity can be defined and created. Views can be a subset of rows and/or a subset of columns with a Condition that is a partial SQL query. Column labels can be overridden by specifying values in the Label Overrides fields.
When creating your own bespoke entities do not duplicate Label names. Label duplication will cause an error in Generating the Extract. All Labels must be discrete in an Entity.
Entity Summary – Views tab
Indexes
Indexes should be defined to support views or other desirable access to records in the table.
Indexes are automatically created to support the joins defined in relationships.
When creating an index for the Entity the format required will be:
Name – A name of the Index, for example: Index1.
Unique – is the Index unique? If yes then tick the box for Unique, otherwise leave blank.
Columns – enter the columns (fields) that are to make up the Index and enter 0 (Ascending) or 1 (descending), for example: kco,0,job_num,0,vwb_code,0.
Entity Summary – Indexes tab
Export / Import Entities
COINS recommend any development and testing of custom entities happen in a non-live analytics environment first, once fully tested, these entities should be deployed to LIVE environment and become LIVE.
Once custom entities are ready to go live, they can be exported from an environment and then imported to the other environment. It is possible to select more than one entity, export them and then import them all to another environment.
Select the Entity or Entities
In the action menu, choose Export Entities and click Apply Action
Entity Maintenance – Export Entities
Copy the entity data from the Export Entities page.
Export Entities
Navigate to Entity Maintenance in the other environment into which these entities should be imported.
Click on the Options menu in the top right-hand corner of the Entity Maintenance screen.
Select Import Entities.
Entity Maintenance – Import Entities
Paste the entity data copied from the source environment and save it.
Import Entities
Entities will appear in Entity Maintenance.