DataBase: xMart

Tables

Currently, the tables we are request in Xmart from frontend app are:

• TRANSLATIONS: used for many of the translations and all stories
• FACT_ENDEMICITY (formerly called FACT_ENDEMICITY_REGIONS): data source for getting country subregion and country endemicity.
• REF_GEO_REGION - data source for defining the names of the WHO regions
• REF_COUNTRY - data source for countries information
• FACT_COUNTRY_CONTEXT - data source for country context on dashboards
• FACT_UPDATE - List of updated date for theme and some map types

There are other REF tables that we don't request directly but via custom views.

The other data is currently read from ARCGIS.

Custom views

Custom views to retrieve information about the new maps:

• FACT_PREVENTION_VIEW: Vector Insecticide Resistance (VIR) studies. For Insecticide resistance status, Insecticide resistance intensity, Resistance mechanisms detection and Synergist effect in susceptibility maps.
• FACT_DIAGNOSIS_VIEW: Parasite PFHRP2/3 gene deletions (HRPO or HRP) studies. For PFHRP2/3 gene deletions and PFHRP2/3 gene deletions studies maps.
• FACT_TREATMENT_VIEW: Antimalarial drug efficacy and resistance (AMDER or AMDERO) studies. For Treatment failure, Delayed parasite clearance (P.f), Molecular markers of drug resistance, Therapeutic efficacy studies and Molecular marker studies maps.
• FACT_INVASIVE_VIEW: Invasive vector species (INV) studies. For Vector occurrence maps.

Custom view to retrieve information about the total studies in each theme:

• FACT_THEME_TOTALS: contains total number of studies by theme.

Creating custom views

To create a custom view, is necessary a connection to the database:

1. Connect to the VPN. For example, using AnyConnect:

• Server:secure.who.int
• User: your user
• password: The password is your password and the code (token) retrieved by authenticator service (google authenticator mobile app, Chrome extension authenticator, etc.). Example: MyPasswordMyToken

2. Connection to the database is:

• Server: SSBD103.WHO.INT
• user: WIMS\your user
• password: Your password

The database is XMART.

The tables are in the view section and the prefix (schema) indicate the code of the mart. MAL_THREATS

To create custom views, it's necessary to create it in MAL_THREATS_c, for example:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [MAL_THREATS_c].[FACT_HRPO_VIEW] AS
SELECT
  1000 + FACT_HRPO.Code as OBJECTID,
  FACT_HRPO.Code,
  FACT_HRPO.DIM_ID__DIM_ID as DimensionID,
  FACT_HRPO.SURV_ID,
  FACT_HRPO.STUDY_SEQ,
  FACT_HRPO.SITE_ID,
  REF_SITES.SITE_NAME,
  REF_SITES.PROV as PROVINCE,
  REF_SITES.LAT as Latitude,
  REF_SITES.LON as Longitude,
  REPLACE(UPPER(REF_GEO_REGION.GEO_REGION_NAME_SHORT),' ','_') as REGION_FULL,
  FACT_HRPO.CODE_ISO_2 AS ISO2,
  FACT_HRPO.YEAR_START,
  FACT_HRPO.YEAR_END,
  FACT_HRPO.TOTAL_STUDIES,
  FACT_HRPO.SURV_STAT_ID__SURV_STAT_ID as SURV_STATUS,
  REF_INST.INST_NAME as INSTITUTION,
  REF_FUND.FUND_NAME as FUNDING_SOURCE,
  REF_PROMPT.PROMPT_NAME,
  REF_HRP_GENO.HRP_GENO_NAME,
  REF_GEOGR_SCOPE.GEOGR_SCOPE_NAME,
  REF_PROT_TYPE.PROT_TYPE_NAME,
  REF_SYMP_STAT.SYMP_STAT_NAME
FROM VECTORS_IR.FACT_HRPO FACT_HRPO
LEFT JOIN VECTORS_IR.REF_SITES REF_SITES ON FACT_HRPO.SITE_ID = REF_SITES.SITE_ID
LEFT JOIN VECTORS_IR.REF_COUNTRY REF_COUNTRY ON FACT_HRPO.CODE_ISO_3 = REF_COUNTRY.CODE_ISO_3
LEFT JOIN VECTORS_IR.REF_GEO_REGION REF_GEO_REGION ON REF_COUNTRY.GEO_REGION_CODE = REF_GEO_REGION.GEO_REGION_CODE
LEFT JOIN VECTORS_IR.REF_INST REF_INST ON FACT_HRPO.INST_ID = REF_INST.INST_ID
LEFT JOIN VECTORS_IR.REF_FUND REF_FUND ON FACT_HRPO.FUND_ID = REF_FUND.FUND_ID
LEFT JOIN VECTORS_IR.REF_PROMPT REF_PROMPT ON FACT_HRPO.PROMPT_ID = REF_PROMPT.PROMPT_ID
LEFT JOIN VECTORS_IR.REF_GEOGR_SCOPE REF_GEOGR_SCOPE ON FACT_HRPO.GEOGR_SCOPE_ID = REF_GEOGR_SCOPE.GEOGR_SCOPE_ID
LEFT JOIN VECTORS_IR.REF_PROT_TYPE REF_PROT_TYPE ON FACT_HRPO.PROT_TYPE_ID = REF_PROT_TYPE.PROT_TYPE_ID
LEFT JOIN VECTORS_IR.REF_SYMP_STAT REF_SYMP_STAT ON FACT_HRPO.SYMP_STAT_ID = REF_SYMP_STAT.SYMP_STAT_ID
LEFT JOIN VECTORS_IR.REF_HRP_GENO REF_HRP_GENO ON FACT_HRPO.HRP_GENO_ID = REF_HRP_GENO.HRP_GENO_ID