Loading a Non-spatial table into the SDW v2.0

iShare Studio where Data Share database is your SDW

These steps rely on using an iShare Studio instance whose Data Share database is your Spatial Data Warehouse e.g.

• Studio ETL
• Studio on an internal SDW Server.

First you need to grant permission for SDW Admin staff to write to the public schema in your SDW. This can be done either via pgAdmin III or by creating a Stored Procedure Task in Studio using the 

astun_wkf_executecmd function (from v6.0.0 this is called wkf_executecmd) with the cmdtoexecute parameter similar to the following:

Now you can use the following steps:

• Open Studio
• Right click on the Data Share Connections node and select Add Datasource. Please follow the instructions for Adding a new Datasource in the on-line Help for the type of datasource you wish to connect to.
• Once you have created the Data Share Connection you can either right click on it and select Synchronise to populate the SDW or, if you want to schedule this, create a Workflow Job and Task. This copies the data to the public schema in your SDW.
• You now need to move this to a different schema within your SDW.
• Check that your have a Workflow Connection to your SDW and if not create one.

• Create a Stored Procedure Task to drop the table in the new schema using the astun_wkf_executecmd function with the cmdtoexecute parameter similar to the following:

  DROP TABLE IF EXISTS schema_name.table_name;

• Create another Stored Procedure Task to move the table from the public to the new schema. This is done using the astun.sdw_move_table function. From v6.0.0 select astun from the Function filter and pick sdw_move_table.

  This has the following parameters:

  • schemasource e.g. public

  • schemadestination e.g. myschema

  • tablename e.g. mytable

  • dropifpresent (from v6.0.0) Set this to true to drop any existing table or leave blank or false to not drop the table..

iShare Studio where Data Share database is not your SDW

The easiest way of migrating non-spatial data into the SDW is to use Studio to define a Data Share Connection. A Data Share Connection in Studio will create a connection to numerous different sources. Syncing a Data Share Connection will migrate the data to a public table of the same name in the public schema of the local iShareData database. PostgreSQL commands are then used to move the table to the SDW database.

1. Create data in iShare database if necessary
   • Open Studio
   • Right click on the Data Share Connections node and select Add Datasource. Please follow the instructions for Adding a new Datasource in the on-line Help for the type of datasource you wish to connect to.
   • Once you have created the Data Share Connection right click on it and select Synchronise.
      
2. Export the data for iShare database
   

   • From the Postgresql bin folder (C:\Program Files (x86)\Postgresql\9.5\bin) run pg_dump.exe -host <host> -port <port> -table <table> -U <user>  -f <path/file.sql>. <iShareData_database_name> This will dump the table to a file.

     Example

     pg_dump.exe --host localhost --port 5433 --username postgres --file "C:\export\schools.sql" --table education.school "iShareDataWorkshop"

3. Before importing the data into the SDW ensure a schema with the same name (in this case education) exists. To create a schema see Creating a Schema

   • From the same folder run psql.exe -U <user> -f <path/file.sql> <sdw_database_name> to load the file into the SDW database.

     Example

     psql.exe -U user_admin --port 5434 -f "c:\import\schools.sql" sdw

     Use an admin user to import the data.
     Ignore any errors like "schema "education" already exists or must be member of role "postgres"

   • If importing into the public schema you'll need to use the "astun" user as admin users do not have write permission to the public schema.
   • Once imported into the public schema you can move it to a corporate schema. See Moving a Table from the public Schema into a Corporate Schema.