Difference between revisions of "Post Processing SQL Scripts"
From IMSMA Wiki
| (9 intermediate revisions by the same user not shown) | |||
| Line 8: | Line 8: | ||
If you would like to use Ordnance data, link items and/or use multi-select fields then you need to create SQL views. | If you would like to use Ordnance data, link items and/or use multi-select fields then you need to create SQL views. | ||
| − | + | [[Image:SAG Ordnance.png|650px|center]] | |
| − | {{Note | 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.}} | + | <div align="center"> |
| − | [Image:SAG CS flattening1.png| | + | '' Example with Ordnance and different formats of date'' |
| + | </div> | ||
| + | [[Image:SAG concat.png|400px|center]] | ||
| + | <div align="center"> | ||
| + | '' Example of fields being concatenated'' | ||
| + | </div> | ||
| + | [[Image:SAG Link.png|200px|center]] | ||
| + | <div align="center"> | ||
| + | '' Example of retrieving information from a linked table'' | ||
| + | </div> | ||
| + | {{Note | It is only the Country Structure of Locations that is flattened, other Country structure references (both system and CDFs) have to be retrieved by SQL views.}} | ||
| + | [[Image:SAG CS flattening1.png|300px|center]] | ||
<div align="center"> | <div align="center"> | ||
'' Country structure reference to where the Victim is currently living is not automatically flattened into the the Victim by the Staging Area Generator'' | '' Country structure reference to where the Victim is currently living is not automatically flattened into the the Victim by the Staging Area Generator'' | ||
</div> | </div> | ||
| − | [Image:SAG CS flattening2.png| | + | [[Image:SAG CS flattening2.png|400px|center]] |
| + | <div align="center"> | ||
| + | '' The flattening with be done with SQL script as part of the post-processing'' | ||
| + | </div> | ||
| + | |||
| + | For more on column names in the Staging area, see [[Name Rules Staging Area]]. | ||
| + | |||
| + | === 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 Navicat): | ||
| + | <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> Start the [[Using Staging Area Generator | Staging Area Generator]] <br/> | ||
| + | [[Image:PostProcess sqlfile.png|375px|center]] | ||
| + | <li> Type in the full name of the SQL script | ||
| + | <li> Start the database generation. | ||
| + | <li> Your script will automatically be applied in the end of the database generation. | ||
| + | </ol> | ||
| + | |||
| + | {{Note| The Post Processing SQL scripts including SQL views needs to be re-applied '''every''' time a staging area database is updated i.e. re-created. <br/> | ||
| + | I you would like to test a SQL script without running the Staging Area Generator (of course only works if there is an '''existing''' staging database): | ||
| + | # Open a command window | ||
| + | # Go to ''C:/IMSMAETLTool'' | ||
| + | # Run the following in the command window: pgsql\bin\psql -U imsma -d staging -h localhost -L sql.log -f sqlscripts\filename.sql }} | ||
| + | |||
| + | [[Image:PostProc SAG error.png|600px|center]] | ||
<div align="center"> | <div align="center"> | ||
| − | '' '' | + | '' SAG cannot find the script file '' |
</div> | </div> | ||
| − | Contact your [[Management | + | # The post processing SQL file must be stored in C:\IMSMAETLTool\sqlscripts |
| + | # The full name of the file must be specified e.g. Albania_stat.sql | ||
| + | # The SAG must be started via a shortcut. You find which values to use for the shortcut '''[[Windows 8 and Windows 10#Shortcuts |here]]'''. | ||
| + | Contact your [[Information Management Team | GICHD IM advisor]] for help in developing SQL statements according to specific requirements. | ||
{{NavBox Business Intelligence}} | {{NavBox Business Intelligence}} | ||
| − | [[Category: | + | [[Category:NAA]] |
Latest revision as of 20:27, 20 February 2020
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.
Example with Ordnance and different formats of date
Example of fields being concatenated
Example of retrieving information from a linked table
 |
It is only the Country Structure of Locations that is flattened, other Country structure references (both system and CDFs) have to be retrieved by SQL views. |
Country structure reference to where the Victim is currently living is not automatically flattened into the the Victim by the Staging Area Generator
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):
- Save the script as a text file with file extension .sql e.g. MySQLscript.sql in the folder C:\IMSMAETLTool\sqlscripts
- Start the Staging Area Generator
- Type in the full name of the SQL script
- Start the database generation.
- Your script will automatically be applied in the end of the database generation.
| |
The Post Processing SQL scripts including SQL views needs to be re-applied every time a staging area database is updated i.e. re-created. I you would like to test a SQL script without running the Staging Area Generator (of course only works if there is an existing staging database):
|
SAG cannot find the script file
- The post processing SQL file must be stored in C:\IMSMAETLTool\sqlscripts
- The full name of the file must be specified e.g. Albania_stat.sql
- The SAG must be started via a shortcut. You find which values to use for the shortcut here.
Contact your GICHD IM advisor for help in developing SQL statements according to specific requirements.
| ||||||||||||||
