Difference between revisions of "Post Processing SQL Scripts"

From IMSMA Wiki
Jump to: navigation, search
Line 33: Line 33:
  
 
=== How to apply the Post Processing SQL script ===__NOEDITSECTION__  
 
=== How to apply the Post Processing SQL script ===__NOEDITSECTION__  
After you have developed and '''tested''' the script with a SQL tool (e.g. PGadminIII or Navicate):
+
After you have developed and '''tested''' the script with a SQL tool (e.g. PGadminIII or Navicat):
 
<ol start="1">
 
<ol start="1">
 
<li> Save the script as a text file with file extension .sql e.g. MySQLscript.sql in the folder ''C:\IMSMAETLTool\sqlscripts''
 
<li> Save the script as a text file with file extension .sql e.g. MySQLscript.sql in the folder ''C:\IMSMAETLTool\sqlscripts''

Revision as of 15:02, 31 March 2017

By using this optional step it is possible to change the Staging area after the generation. This can be valuable in several use cases, for example:

  • If the Staging area should only contain a subset of the IMSMANG database e.g. only Land with status Closed
  • If sensitive data (e.g. victim names and addresses) should not be included in the Staging area
  • If data has to be processed for reporting purposes e.g. complex calculations of square meters
  • If it would facilitate reporting to concatenate fields e.g. victim family and given names
  • If it would facilitate to add fields from other items e.g. Accident date to Victim table
  • If additional database SQL views are necessary, also for reporting and analysis purposes

If you would like to use Ordnance data, link items and/or use multi-select fields then you need to create SQL views.

SAG Ordnance.png

Example with Ordnance and different formats of date

SAG concat.png

Example of fields being concatenated

SAG Link.png

Example of retrieving information from a linked table

Note.jpg It is only the Country Structure of Locations that is flattened, other Country structure references (both system and CDFs) have to retrieved by SQL views.
SAG CS flattening1.png

Country structure reference to where the Victim is currently living is not automatically flattened into the the Victim by the Staging Area Generator

SAG CS flattening2.png

The flattening with be done with SQL script as part of the post-processing

For more on column names in the Staging area, see Name Rules Staging Area.

How to apply the Post Processing SQL script

After you have developed and tested the script with a SQL tool (e.g. PGadminIII or Navicat):

  1. Save the script as a text file with file extension .sql e.g. MySQLscript.sql in the folder C:\IMSMAETLTool\sqlscripts
  2. Start the Staging Area Generator
    PostProcess sqlfile.png
  3. Type in the full name of the SQL script
  4. Start the database generation.
  5. Your script will automatically be applied in the end of the database generation.

Contact your GICHD IM advisor for help in developing SQL statements according to specific requirements.