Difference between revisions of "IMSMA Staging Area"

From IMSMA Wiki
Jump to: navigation, search
Line 25: Line 25:
 
| align="left" | '''Standard single selects'''  
 
| align="left" | '''Standard single selects'''  
 
| align="left" | Stored directly in the main object table (whereas in {{IMSMANG}} the main object table only contains a GUID reference to the IMSMAENUM table)
 
| align="left" | Stored directly in the main object table (whereas in {{IMSMANG}} the main object table only contains a GUID reference to the IMSMAENUM table)
−
| align="left" | The HAZARD (Land) attribute status is stored in a column called status_enum in the HAZARD table of the staging area. It contains the '''translated value''' of the status base value, e.g. ''Open'' or ''Closed'' in English or the equivalent values in another language that has been specified at staging area generation.
+
| align="left" | The Land attribute ''status'' is stored in a column called status_enum in the HAZARD table of the staging area. It contains the '''translated value''' of the status base value, e.g. ''Open'' or ''Closed'' in English or the equivalent values in another language that has been specified at staging area generation.
 
|-
 
|-
 
| align="left" | '''Standard multi selects'''  
 
| align="left" | '''Standard multi selects'''  
 
| align="left" | Stored directly in the main object table as '''comma-separated list of values''' '''AND''' in a normalized way in the <OBJECT>_STD_MULTISELECT table (in {{IMSMANG}} the values for a multi select have to be looked up in the <OBJECT>_HAS_IMSMAENUM and IMSMAENUM tables)
 
| align="left" | Stored directly in the main object table as '''comma-separated list of values''' '''AND''' in a normalized way in the <OBJECT>_STD_MULTISELECT table (in {{IMSMANG}} the values for a multi select have to be looked up in the <OBJECT>_HAS_IMSMAENUM and IMSMAENUM tables)
−
| align="left" | some example
+
| align="left" | The Land object has a multi-select attribute called ''Marking'' (Marking Method). In the staging area, the HAZARD table has an attribute marking_method with a comma-separated list of values, for example 'Official Signs, Local Signs'. Additionally, the table HAZARD_STD_MULTISELECT can be joined with the HAZARD table in order to get to the same values as rows. Reusing the above example, there will be two rows, one with 'Official Signs' and one with 'Local Signs' as values (or the translated values if any translations are available).
 
|-
 
|-
  

Revision as of 12:53, 19 June 2014

What is the IMSMA Staging Area?

The IMSMA staging area is a flattened version of the IMSMA operational database. In terms of content, the data is an exact copy of the IMSMA database, but the structure is much less complex and therefore easier to query. While in the operational IMSMA database the core object data (e.g. the ID and name of a Land) is stored in one table (e.g. in the HAZARD table) and descriptive attributes (such as single- and multi-select standard and custom defined fields) are stored in separate tables, in the staging area all this information is available in one single table. Therefore, there is no need to write complex queries to get to all the attributes of an object. Ultimately, reporting and data analysis tools can easily be connected to the staging area.
A staging area can be created out of an IMSMANG v.6 database.

Structure of the IMSMA Staging Area

Database model

Flattening principles

Note.jpg This section requires basic knowledge of the IMSMANG structure
  • CDF text, numeric, and any single-select attribute values are stored directly in the main table.
    • Example:
  • Standard multi-select attribute values are directly stored in the main table as a comma-separated list and in a normalised way in the standard multi-select table associated to the object.
    • Example:
  • CDF multi-select attribute values are directly stored in the main table as a comma-separated list and in a normalised way in the CDF multi-select table associated to the object.
    • Example:
  • The guid, localid and name of the location an object is linked to are directly stored in the main object table.
    • Example:
  • The guid, localid and name of an organisation linked to an object are directly stored in the main object table.
    • Example:
  • The guid, localid and name of classifications (country structure (gazetteer), assistance classification, cause classification and needs assessment classification) associated to an object are directly stored in the main object table. Since a classification can have several levels, there is a placeholder for each level, up to the maximum number of levels. For example, the country structure can have up to seven levels. Therefore, in a main object table like HAZARD, there will the following columns: gazetteer_level1_localid, gazetteer_level1_name, gazetteer_level2_localid, gazetteer_level2_name, ..., gazetteer_level7_localid, gazetteer_level7_name. If in IMSMA only four levels are defined, then the columns for levels five to seven will always be empty.
    • Example:

Main object tables

Other tables

Views

Geographical data in the staging area

Where to find which data in the staging area
Type of attribute Where? Example
Standard text, numeric, date and time attributes Stored directly in the main object table, similar to IMSMANG The HAZARD (Land) attribute hazard_localid is stored in a column also called hazard_localid in the HAZARD table of the staging area.
Standard single selects Stored directly in the main object table (whereas in IMSMANG the main object table only contains a GUID reference to the IMSMAENUM table) The Land attribute status is stored in a column called status_enum in the HAZARD table of the staging area. It contains the translated value of the status base value, e.g. Open or Closed in English or the equivalent values in another language that has been specified at staging area generation.
Standard multi selects Stored directly in the main object table as comma-separated list of values AND in a normalized way in the <OBJECT>_STD_MULTISELECT table (in IMSMANG the values for a multi select have to be looked up in the <OBJECT>_HAS_IMSMAENUM and IMSMAENUM tables) The Land object has a multi-select attribute called Marking (Marking Method). In the staging area, the HAZARD table has an attribute marking_method with a comma-separated list of values, for example 'Official Signs, Local Signs'. Additionally, the table HAZARD_STD_MULTISELECT can be joined with the HAZARD table in order to get to the same values as rows. Reusing the above example, there will be two rows, one with 'Official Signs' and one with 'Local Signs' as values (or the translated values if any translations are available).
Staging Area Generator
IMSMA Staging Area
Other Reporting Tools
Indicators
Retrieved from "http://mwiki.gichd.org:8090/mediawiki/index.php?title=IMSMA_Staging_Area&oldid=15524"