Post Processing SQL Scripts - Revision history

Alnaucler at 19:27, 20 February 2020

2020-02-20T19:27:25Z

← Older revision		Revision as of 21:27, 20 February 2020
Line 58:		Line 58:
	Contact your [[Information Management Team \| GICHD IM advisor]] for help in developing SQL statements according to specific requirements.		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:~~VIE~~]]		[[Category:NAA]]

Alnaucler at 10:40, 14 November 2017

2017-11-14T10:40:57Z

← Older revision		Revision as of 12:40, 14 November 2017
Line 49:		Line 49:
	# Run the following in the command window: pgsql\bin\psql -U imsma -d staging -h localhost -L sql.log -f sqlscripts\filename.sql }}		# 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\|~~500px~~\|center]]~~<br/>~~		[[Image:PostProc SAG error.png\|600px\|center]]
	<div align="center">		<div align="center">
	'' SAG cannot find the script file ''		'' SAG cannot find the script file ''

Alnaucler at 12:22, 13 November 2017

2017-11-13T12:22:54Z

← Older revision		Revision as of 14:22, 13 November 2017
Line 49:		Line 49:
	# Run the following in the command window: pgsql\bin\psql -U imsma -d staging -h localhost -L sql.log -f sqlscripts\filename.sql }}		# 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\|500px\|center]]<br/>
			<div align="center">
			'' SAG cannot find the script file ''
			</div>
			# 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.		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:VIE]]		[[Category:VIE]]

Alnaucler at 18:26, 10 November 2017

2017-11-10T18:26:06Z

← Older revision		Revision as of 20:26, 10 November 2017
Line 20:		Line 20:
	'' Example of retrieving information from a linked table''		'' Example of retrieving information from a linked table''
	</div>		</div>
	{{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.}}		{{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]]		[[Image:SAG CS flattening1.png\|300px\|center]]
	<div align="center">		<div align="center">
Line 43:		Line 43:
	</ol>		</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.}}		{{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 }}

	Contact your [[Information Management Team \| GICHD IM advisor]] for help in developing SQL statements according to specific requirements.		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:VIE]]		[[Category:VIE]]

Alnaucler at 14:30, 31 March 2017

2017-03-31T14:30:02Z

← Older revision		Revision as of 16:30, 31 March 2017
Line 42:		Line 42:
	<li> Your script will automatically be applied in the end of the database generation.		<li> Your script will automatically be applied in the end of the database generation.
	</ol>		</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.}}

	Contact your [[Information Management Team \| GICHD IM advisor]] for help in developing SQL statements according to specific requirements.		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:VIE]]		[[Category:VIE]]

Alnaucler at 14:02, 31 March 2017

2017-03-31T14:02:20Z

← Older revision		Revision as of 16:02, 31 March 2017
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''

Alnaucler at 12:08, 23 October 2015

2015-10-23T12:08:54Z

@@ Line 30: / Line 30: @@
 </div>
-For more on column names in the Staging area, see [[Name Rules Staging Area]]. Contact your [[Information Management Team | GICHD IM advisor]] for help in developing SQL statements according to specific requirements.
+For more on column names in the Staging area, see [[Name Rules Staging Area]].
 {{NavBox Business Intelligence}}
 [[Category:VIE]]

Alnaucler at 12:11, 6 October 2015

2015-10-06T12:11:29Z

← Older revision		Revision as of 14:11, 6 October 2015
Line 30:		Line 30:
	</div>		</div>

	For more on column names in the Staging area, see [[Name Rules Staging Area]]. Contact your [[Management ~~Consulting~~ Team \| GICHD IM advisor]] for help in developing SQL statements according to specific requirements.		For more on column names in the Staging area, see [[Name Rules Staging Area]]. 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:VIE]]		[[Category:VIE]]

Alnaucler at 08:04, 9 July 2015

2015-07-09T08:04:12Z

← Older revision		Revision as of 10:04, 9 July 2015
Line 30:		Line 30:
	</div>		</div>

			For more on column names in the Staging area, see [[Name Rules Staging Area]]. Contact your [[Management Consulting Team \| GICHD IM advisor]] for help in developing SQL statements according to specific requirements.
	Contact your [[Management Consulting Team \| GICHD IM advisor]] for help in developing SQL statements according to specific requirements.
	{{NavBox Business Intelligence}}		{{NavBox Business Intelligence}}
	[[Category:VIE]]		[[Category:VIE]]

Alnaucler at 20:33, 8 July 2015

2015-07-08T20:33:24Z

← Older revision		Revision as of 22:33, 8 July 2015
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]]
			<div align="center">
			'' 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 retrieved by SQL views.}}		{{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.}}
	[Image:SAG CS flattening1.png\|~~500px~~\|center]]		[[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\|~~500px~~\|center]]		[[Image:SAG CS flattening2.png\|400px\|center]]
	<div align="center">		<div align="center">
	'' ''		'' The flattening with be done with SQL script as part of the post-processing''
	</div>		</div>


	Contact your [[Management Consulting Team \| GICHD IM advisor]] for help in developing SQL statements according to specific requirements.		Contact your [[Management Consulting Team \| GICHD IM advisor]] for help in developing SQL statements according to specific requirements.
	{{NavBox Business Intelligence}}		{{NavBox Business Intelligence}}
	[[Category:VIE]]		[[Category:VIE]]