Difference between revisions of "IMSMA Staging Area"

From IMSMA Wiki
Jump to: navigation, search
(35 intermediate revisions by 4 users not shown)
Line 1: Line 1:
{{TOC right}}
+
The IMSMA staging area is a '''flattened''' and '''read-only''' version of the {{IMSMANG}} operational database. In terms of content, the data is an exact copy of the '''approved''' information in the {{IMSMANG}} database, but the structure is less complex and therefore easier to query.
  
==What's the IMSMA Staging Area?==
+
While in the operational {{IMSMANG}} database the item data (e.g. the ID, status and name of a Land) is stored in one table and other information about the Land (such as multi-select and custom defined fields) are stored in separate tables. Furthermore in the {{IMSMANG}} information are also stored with enumeration values and as links between objects. In the staging area all this information is flattened in one single table for each item. Therefore, there is lesser need to write complex queries to retrieve attributes of an object.  
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 the core information. Ultimately, reporting and data analysis tools can easily be connected to the staging area.<br />
 
A staging area can be created out of an {{IMSMANG}} v.6 database.
 
  
==Structure of the IMSMA Staging Area==
+
A staging area can be created out of an {{IMSMANG}} v 6.0 (and higher) database. It is typically used as a '''reporting database''', via [[MINT]] or any other [[Other Reporting Tools|reporting/analysis tool]] such as Excel or ArcGIS.
===Flattening principles===
 
{{Warning|This section requires basic knowledge of the {{IMSMANG}} structure}}
 
The generation of the staging area follows the following principles, for all the tables:
 
* Standard text, numeric, and any single-select attribute values (known as '''imsmaenum''') are stored directly in the main table
 
** Example:
 
* 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 organization 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===
 
 
 
===Geographical data===
 
 
 
===Database diagrams===
 
  
 
{{NavBox Business Intelligence}}
 
{{NavBox Business Intelligence}}
 +
[[Category:NAA]]

Revision as of 20:29, 20 February 2020

The IMSMA staging area is a flattened and read-only version of the IMSMANG operational database. In terms of content, the data is an exact copy of the approved information in the IMSMANG database, but the structure is less complex and therefore easier to query.

While in the operational IMSMANG database the item data (e.g. the ID, status and name of a Land) is stored in one table and other information about the Land (such as multi-select and custom defined fields) are stored in separate tables. Furthermore in the IMSMANG information are also stored with enumeration values and as links between objects. In the staging area all this information is flattened in one single table for each item. Therefore, there is lesser need to write complex queries to retrieve attributes of an object.

A staging area can be created out of an IMSMANG v 6.0 (and higher) database. It is typically used as a reporting database, via MINT or any other reporting/analysis tool such as Excel or ArcGIS.