Unlike TrackTik's API which can follow all the most up to date data sources and relationships because the objects in responses can be expanded in many 1:1, 1:many, many:many ways, the Data Warehouse is in a table/fields/rows format that will not support 1:many nor many:many relationships. It’s up to the Business Intelligence professional to create their own relationships, and the many tables in the Data Warehouse are not updated without synchronization triggers that the API doesn’t have to contend with, but the Data Warehouse does.
This leads to some limits in what can be populated in tables, and at what frequencies (currently with few capabilities to cascade/domino across complex relationships.)
The limits are discussed below.
Summary of what API endpoints and/or fields cannot be replicated:
- Endpoints that are Database Views, and not discrete tables
- Occurrence endpoints
- Computed fields
- Extensions
- Polymorphic relation fields
- Relation lists
The following offers a deeper dive into a few of those scenarios:
API Entities that are Views cannot be replicated in DWH
All of TrackTik’s DWH tables are directly connected to an underlying table via API so that create/update/actions events can be directly signaled to the DWH service for data refreshing.
But there are some API endpoints that are views and therefore can’t be included in a DWH solution as a table. There’s currently no means of detecting changes of a view.
E.g. Site Task Occurrences
This data entity available in the API refers to all the events scheduled into the future based on the start/stop dates of a Site Task Schedule, and which days of the week are defined.
When a Site Task Schedule is changed, its DWH table will be updated, but the View called Site Task Schedule Occurrences is not a data source that experiences state changes such as created/updated, so it can’t be included. It would never get updated.
There are several data entities like this which can’t be included based on the fact that they’re a view, like:
- Contract Occurrences
- Holiday Dates
- Leave Balances
- Leave Management Adjustments
- Mobile Runsheet Occurrences
- Mobile Schedule Occurrences
- Payroll Schedule Occurrences
- Site Task Occurrences
- Checkpoint Tour Schedule Occurrences
- Zone Clients
The API documentation available at https://<portal domain>/rest/v1 has indicators to help remind you which data entities are not suitable and cannot be provided as tables in the Data Replication solution.
E.g.
Some exclusions can occur on select fields of otherwise valid API/data entities, like with the break-rules (see conditions and positions, which are arrays -- data replication cannot represent an array as a single column in a table):
Absence of events for computed properties
For TrackTik’s goals of being a real-time system, there can be data values that are calculated related to the current moment instead of being fetched from a database. This allows business operations to operate faster and in the moment past the usual timings of data persistence.
Any real-time values that aren’t database persisted cannot be represented in the Data Warehouse.
One notable example of this is Shift Attendance Status.
The timer for defining this status operates in fixed values depending on how the TrackTik portal is configured. If the definition of “late” is “15 minutes past due”, there can still be a business need to see the attendance of a shift as late at 10 minutes, or 5 minutes. Sure, you can reduce the setting to 5 minutes, but then the situation still applies for 2 mins late, or 3 minutes. Calculated fields are therefore used for the overall business-value of the definition of late, and is therefore not data directly available from a database.
Shifts and their statuses are persisted to the database and the Shift data is available in Data Warehouse, but the refresh rates won’t be as real-time as calculated fields, so you’ll be able to retrieve Shifts and stable persisted statuses only.
Computed properties that depend on configurations and made references to other tables
Within the TrackTik ecosystem, there can be values that are hierarchically selected through logic at the time that they are retrieved based on a date-time boundary context, or a user operation having occurred. Some of these pivots between one value and another can involve a per-emptive cascading of multiple persistence operations. The Data Warehouse’s triggers for data refreshes are directly tied in a 1:1 method per table to the underlying persisted data, and cascades between multiple data tables are not tracked and sequenced in a queue.
There can therefore be situations where a value changes, based on changes from other sources, not all of these data points will be updated together at the end of a multi-step process and therefore aren’t viable for our Data Warehouse solution. The DWH user would never be able to tell if a value is current, delayed, or won’t update at all.
One such example is the stand-alone (outside the context of an invoice, payrun etc.) value of an employee’s pay rate. Pay rates can be direct from data or inferred across multiple additional data contexts usually related to the configuration of the TrackTik web application, like region rate vs employee rate or other employee classes like part time. When a rate is in flux or determined hierarchically based on various business rules in real-time, then it cannot be picked up by the Data Warehouse.
Therefore employee pay rates are only visible to a DWH solution when they are at rest, in data entities like employment_profiles, billable_items, invoices, payroll_payrun items and so on, which are either base configurations or snapshots generated programmatically for the purpose of creating an amalgam of various stable and computed values. This can only be done through logic, and the tables and rows of the Data Warehouse are not separate software per data entity.