Core
SoFlow is a method of logging pipeline runs and linking rows in the data tables to a specific run.
When SoFlow is called, RunId is created, which provides a unique identifier for the entire run. For each process listed under SoFlow, an AssetId is created. AssetId is inserted into Core, which enables troubleshooting in the event that an error occurs during a pipeline run. An entry into SoFlow.Log is created for the entire run, and entries are created for each individual run.
Example:
SoFlow.Log
| Id | RunId | AssetId | Name | Version | Args | StartDateTime | EndDateTime | RunTime | Status |
|---|---|---|---|---|---|---|---|---|---|
| 43 | f7beab86-a11f-47eb-9645-6fc0d2b0b4fa | f7beab86-a11f-47eb-9645-6fc0d2b0b4fa | SoFlow | 0.0.1 | {"SolerProgramPipeline": {"spawn_programresolved": true, "load_to_core": true, "load_soi_programs": true}, "SolerPersonPipeline": {"spawn_personclean": true, "spawn_personresolved": true, "load_to_core": true, "load_personclean": true, "update_personresolved": false, "bulk_personresolved": true}} | 2024-02-18 12:31:10.923 | 2024-02-18 14:56:15.230 | 02:25:04 | Completed |
| 44 | f7beab86-a11f-47eb-9645-6fc0d2b0b4fa | a87e6046-56e6-4c50-84ff-963e9650e558 | SolerProgramPipeline | 0.0.1 | {"spawn_programresolved": true, "load_to_core": true, "load_soi_programs": true} | 2024-02-18 12:31:10.937 | 2024-02-18 12:31:41.200 | 00:00:30 | Completed |
| 45 | f7beab86-a11f-47eb-9645-6fc0d2b0b4fa | 796d5aa5-09c1-4ea2-9bea-c078ff05864c | SolerPersonPipeline | 0.0.1 | {"spawn_personclean": true, "spawn_personresolved": true, "load_to_core": true, "load_personclean": true, "update_personresolved": false, "bulk_personresolved": true} | 2024-02-18 12:31:46.203 | 2024-02-18 14:56:10.210 | 02:24:24 | Completed |
Table SoFlow.Log as Log {
RunId varchar
AssetId varchar
Name varchar
Version varchar
Args varchar
StartDateTime datetime
EndDateTime datetime
RunTime varchar
Status varchar
}
Core
Common Columns for Core
| colname | colvalues | coltype | detail |
|---|---|---|---|
CreatedDateTime |
|||
AssetId |
|||
SourceId |
Source
A source of a row of data, e.g. a legacy HAS file, a GMS database, an excel file.
Table Core.Source as S {
SourceId int [pk, increment]
SourceName varchar
AssetId varchar [ref: > Log.AssetId]
CreatedDateTime datetime
}
Location
A location is the identification of a geographic area, ranging from low granularity (country) to high granularity(specific address).
Table Core.Location as Loc {
LocationId int [pk, increment]
AddressPrimary varchar
AddressSecondary varchar
City varchar
State varchar
PostalCode varchar
Country varchar
CountryCode varchar
AssetId varchar [ref: > Log.AssetId]
SourceId int [ref: > S.SourceId]
CreatedDateTime datetime
}
Person
A person is an individual that may take on a number of roles at specific times and contexts.
Table Core.Person as P {
PersonId int [pk, increment]
FirstName varchar
MiddleName varchar
LastName varchar
BirthDate date
Gender varchar
LocationId int [ref: > Loc.LocationId]
AssetId varchar [ref: > Log.AssetId]
SourceId int [ref: > S.SourceId]
CreatedDateTime datetime
}
Organization
An organization is any governed body which can include companies, programs, delegations, etc.
Table Core.Organization as Org {
OrganizationId int [pk, increment]
OrganizationName varchar
OrganizationType varchar
Region varchar
LocationId int [ref: > Loc.LocationId]
AssetId varchar [ref: > Log.AssetId]
SourceId int [ref: > S.SourceId]
CreatedDateTime datetime
}
Event
An event is a time-bound instance of observable interaction of a Person in a collective setting, usually a competition. Other kinds of events could be fitness challenges, fundraisers, etc. Typical types of events are local, state, regional, national, and world games.
Table Core.Event as E {
EventId int [pk, increment]
EventName varchar
EventType varchar
OrganizationId int [ref: > Org.OrganizationId]
LocationId int [ref: > Loc.LocationId]
StartDate date
AssetId varchar [ref: > Log.AssetId]
SourceId int [ref: > S.SourceId]
CreatedDateTime datetime
}
Role
A role is specific to an individual's participation in an event. An individual can have more than one type of role for an event, with each role tied to a participation. An example of a role can be athlete, volunteer, coach, etc.
Table Core.Role as R {
RoleId int [pk, increment]
RoleName varchar
RoleType varchar
AssetId varchar [ref: > Log.AssetId]
SourceId int [ref: > S.SourceId]
CreatedDateTime datetime
}
Discipline
A discipline is a context in which an activity is engaged. For example, Sport and Health are both top-level disciplines. Basketball is also a discipline with type Sport. All HAS disciplines fit into this concept.
Table Core.Discipline as Disc {
DisciplineId int [pk, increment]
DisciplineName varchar
DisciplineType varchar // "sport" or "health"
DisciplineSubType varchar
DisciplineSubSubType varchar
AssetId varchar [ref: > Log.AssetId]
SourceId int [ref: > S.SourceId]
CreatedDateTime datetime
}
Metric
A metric is a measurement tied to an instance of participation. Example: For sport, a metric is 'participated'. For health, a metric is 'BMI'.
Table Core.Metric as Met {
MetricId int [pk, increment]
MetricName varchar
MetricType varchar
AssetId varchar [ref: > Log.AssetId]
SourceId int [ref: > S.SourceId]
CreatedDateTime datetime
}
Observation
An observation is a generic observation of a Person. It can include an observed engagement in a health screening or engagement in a sport. Use this table to join to other schemas like HAS and GMS.
Table Core.Observation as Obs {
ObservationId int [pk, increment]
ObservationSourceId int[ref: > GMS.Entries.Id,
ref: > HAS.{}View.Id]
PersonId int [ref: > P.PersonId]
PersonOrganizationId int [ref: > Org.OrganizationId]
SourceSchema varchar
SourceTable varchar
IsScratched bit
AssetId varchar [ref: > Log.AssetId]
SourceId int [ref: > S.SourceId]
CreatedDateTime datetime
}
Measure
A measure is a measure tied to an observation. Forexample, an athlete is observed to have participated in a FitFeet health screening, which would be documented in core.Observation. The specific results of that screening will be documented in Core.Measure.
Table Core.Measure as M {
MeasureId int [pk, increment]
ObservationId int [ref: > Obs.ObservationId]
DisciplineId int [ref: > Disc.DisciplineId]
RoleId int [ref: > R.RoleId]
MetricId varchar [ref: > Met.MetricId]
MetricVarChar varchar
MetricFloat float
MetricBit bit
MeasuredDateTime datetime
AssetId varchar [ref: > Log.AssetId]
SourceId int [ref: > S.SourceId]
CreatedDateTime datetime
}
PersonResolved
This table provides the resolved PersonUuid that can be joined to PersonId. Person profiles are resolved on FirstName, LastName, BirthDate, Gender, and Program.
Table Core.PersonResolved as PR {
Id int [pk, increment]
PersonId int [ref: > P.PersonId]
PersonUuid varchar
Score real
AssetId varchar [ref: > Log.AssetId]
CreatedDateTime datetime
}
ProgramResolved
This table provides the resolved OrganizationUuid that can be joined to OrgnanizationId. Organization profiles are joined on Organization Name and Region. Only Organizations with OrganizationType='Program' are resolved.